Post reply

Writing to Excel After Skipping Rows

  • June 20, 2015 at 7:12 pm

    #301780

    Hi all -

    Is there a way to write data to an excel spreadsheet after skipping x number of rows...excel is my destination and a sql query would be my source?

    My scenario is one where i have a lot of header rows that i need to skip before data insertion. I would like to do this in an SSIS package. I am using SQL 2008 and Excel 2010.

    Thanks

  • June 21, 2015 at 10:14 am

    #1806904

    Editing EXCEL spreadsheets via SSIS or any other method is so wrong for so many reasons. One of the worst reasons is that if someone has the spreadsheet open, your code/job will fail, plain and simple. Another reason is that spreadsheets change. Sure, you can do some tricks with named ranges or inserting into a different sheet and using formulas on different sheets to put the data in the right spots... until the user makes a change somewhere that blows your code out of the water. All spreadsheets are simply out of your control and trying to edit spreadsheets that are out of your control could become a full time job.

    To wit, if it must be an "editable" spreadsheet, I recommend that you create a stored procedure that will be called as "external data" from the spreadsheet and let the users deal with it. A better way, of course, is to maintain all of the data for the spreadsheet in the database where it actually gets backed up, etc, and let the users create reports/spreadsheets from that or, even better, create the reports for them.

    If none of that is possible, then I recommend reading the entire spreadsheet (including the header stuff) into staging table(s), update the data there, and then generate an entirely new spreadsheet for the users. Of course, that's still going to fail on the read if the user has the spreadsheet open.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 22, 2015 at 11:10 am

    #1807113

    While I agree with Jeff that working with Excel using SSIS has a lot of drawbacks, I have done what you are attempting.

    You can start writing the rows to the xls connection in SSIS and modify the rows to skip to start where you know the header rows end.

    Also bear in mind you need to set the job to run in 32 bit mode.

Viewing 3 posts - 1 through 2 (of 2 total)

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