Post reply

Excel Connection manager

  • March 2, 2010 at 9:59 am

    #139108

    I have an excel file with 3 blank rows. The data starts from the 4th row only. Is there a way I can specify the excel connection manager to start accessing the data from 4th row only?

  • March 2, 2010 at 10:05 am

    #1127240

    Not there.. You could potentially add a Conditional split after the Excel source on the Data-Flow and discard those rows.

    CEWII

  • March 3, 2010 at 2:09 am

    #1127588

    Thanks Elliot

  • March 3, 2010 at 9:55 am

    #1127904

    You are welcome.

    CEWII

  • March 4, 2010 at 1:53 am

    #1128328

    Yes you can - I spent ages with this problem in both 2000 dts and 2005 ssis.

    In your Excel data source set the data access mode as SQL Command and then the SQL Command text as

    select * from [SheetName$A4:IV65535]

    where sheetname is the name of the worksheet and the range is your data

  • March 4, 2010 at 8:15 am

    #1128530

    Interesting. I think I might have learned something today..

    CEWII

  • March 4, 2010 at 10:44 am

    #1128633

    Or alternatively what you could do is have a execute sql task after the insert into your table with a "DELETE FROM TABLENAME WHERE ROWSNOTWANTED=''

  • March 4, 2010 at 11:02 am

    #1128649

    avinash jobanputra (3/4/2010)

    Or alternatively what you could do is have a execute sql task after the insert into your table with a "DELETE FROM TABLENAME WHERE ROWSNOTWANTED=''

    I have to say that in almost no case would that be ok, in many (most?) cases this method would cause issues in the data-flow pipeline. Therefore they would never have been inserted in the first place..

    CEWII

  • March 4, 2010 at 11:20 am

    #1128656

    P Jones (3/4/2010)

    Yes you can - I spent ages with this problem in both 2000 dts and 2005 ssis.

    In your Excel data source set the data access mode as SQL Command and then the SQL Command text as

    select * from [SheetName$A4:IV65535]

    where sheetname is the name of the worksheet and the range is your data

    I had this problem previously and used the conditional split approach.. This is something new.. never saw it anywhere before..

    what does that actually refer to ..i knw that 65535 is the max limit what does IV refer to... is it cell number in roman format??

    If you want from 6th cell would it be [SheetName$A6:VI65535] ??? and i guess it works only with excel 2000 bcz the later versions can store way no of rows.. Does it wrk with later versions... if we just change the no of rows value from 65535 to the number that later versions can store..

  • March 4, 2010 at 11:28 am

    #1128665

    I meant at the end of the package flow So

    step 1 Data flow task

    a) it retrieves the excel file

    b) it writes to the destination (sql table)

    step 2 Execute SQL task

    a) sql statement like

    delete from Sheet1$

    where id is null

    I have tested this and it works.

    Irrespective, this can be done a better way but its one way which potentially could work for you ns.sharath

  • March 4, 2010 at 11:37 am

    #1128677

    I'm not saying it wouldn't work. But most of my data-flows have a lot between the source and the destination. Having NULLs or blanks will OFTEN cause them to fail, I'd have to add extra logic to handle that condition where it makes more sense to filter them off BEFORE I have to deal with them. What I'm saying is that while it might work, its not very functional.

    CEWII

  • March 4, 2010 at 12:48 pm

    #1128722

    Ok fair point

Viewing 12 posts - 1 through 11 (of 11 total)

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