Remove first two column in excel using Script Task

Question

Remove first two column in excel using Script Task

sqlusers

SSCrazy

Points: 2463
More actions
December 1, 2010 at 4:48 am

#231761

Hi,
I am converting an Excel File to Flat File [txt file] format. In my excel source file i am getting some unwanted values in first and second rows. Which means that from third row itself i am getting real datas in my excel.
I need to remove those unwanted rows from excel before converting it into an txt file [Flat File format]. How can i do this in Script Task?
---

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

stricknyn SSCertifiable Points: 6560 More actions · Answer 1

If the "good" data doesn't start until the 3rd column, then you actually don't need a script task to correct this. You can just use an excel source, your transformations in between, and a flat file destination. In your flat file destination you would just ignore the first two columns in the mapping.

Strick

sqlusers SSCrazy Points: 2463 More actions · Answer 2

stricknyn (12/1/2010)
If the "good" data doesn't start until the 3rd column, then you actually don't need a script task to correct this. You can just use an excel source, your transformations in between, and a flat file destination. In your flat file destination you would just ignore the first two columns in the mapping.
Strick

But i am getting error while clicking mapping column tab after imporing this into Excel Source part.

Phil Parkin SSC Guru Points: 246949 More actions · Answer 3

The title of this thread refers to columns, yet your posts refer to rows. That's an important difference - which do you mean?

For future reference, when you get an error and you want to ask the community for help resolving it, please post the text of the error message.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sqlusers SSCrazy Points: 2463 More actions · Answer 4

Phil Parkin (12/2/2010)
The title of this thread refers to columns, yet your posts refer to rows. That's an important difference - which do you mean?
For future reference, when you get an error and you want to ask the community for help resolving it, please post the text of the error message.

Ops, 🙂 Sorry Phil. I d'nt noticed that. I need rows not columns. But i got the solution for this.

In my data Flow task, i need to set the "OpenRowset" Custom Property in my Excel Source (right-click your Excel connection > Properties; in the Properties window, look for OpenRowset under Custom Properties). To ignore the first 2 rows in Sheet1, and import columns from A-M, i would enter the following value for OpenRowset: Sheet1$A3:M (notice, I did not specify a row number for column M. You can enter a row number if you like. Using my OLEDB Destination i got all the records to my table.

Thank You guyz for your valuable info. 🙂

Phil Parkin SSC Guru Points: 246949 More actions · Answer 5

Nice solution - I'm sure others will use it.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin