Post reply

SSIS 2005 Excel File Import skipping/Deleting rows

  • January 8, 2010 at 10:42 am

    #136354

    Dear All,

    I am in the process of creating an SSIS 2005 package that when completed will import multiple excel files into a single SQL table via a Foreach loop container.

    The format of the 1st few rows are giving me big problems. The data that I need to import starts at row 10. But two columns, rows A1:B10 contain headers & date fields that I need to skip. I built this query in the Excel Connection Manager:-

    SELECT F1, F2, F3, F4, F6, F5, F7, F8, F9, F10, F11, F12, F13, F14, F15

    FROM ['ExcelSheet1$']

    WHERE (F4 IS NOT NULL)

    When I run the preview it only displays string text ok, but all number fields as NULL?

    I need to either skip or delete the first 10 rows. Has anybody out there experienced anything simillar.

    I created a copy of this file & deleted the first 10 rows. I was able to import the data without any problems?

    Any suggestions would be much appreciated.

    Regards

    Nigel

  • January 8, 2010 at 11:41 am

    #1101109

    You should read up on the issues that are common with the Excel Provider in Douglas Laudenschlager's blog - posts tagged with Excel.

    The problem you're having is that Excel is testing the first eight lines of your sheet, has decided they're text, and when it goes to really read it, won't read in the numbers.

    Using the IMEX=1 flag on the connection solves most of those issues, although if you have substantial control on the format of the spreadsheet itself, I'd suggest using Named Ranges. Then you can do a SELECT ... FROM [Named Range].

    Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
    Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
    Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

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

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