Remove first two column in excel using Script Task

  • 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?

    ---

  • 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

  • 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.

  • 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

  • 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. 🙂

  • 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

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

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