Post reply

Parsing excel with SSIS

  • June 23, 2008 at 8:45 am

    #123432

    I'm new to SSIS and help is much appreciated. I'm trying yo parse an Excel spreadsheet using SSIS but when I preview the data I don't get the same result.

    Below is the raw data before parsing:

    BEGIN_DATEEND_DATEPercent1 Percent2

    1/1/2006 12/31/9999-

    4/1/2007 12/31/9999100% Percent100% Percent

    1/1/2006 12/31/99991

    7/2/2006 12/31/99990.65 17%

    Data previewed on the Excel Source editor:

    1/1/2006 12/31/9999null null

    4/1/2007 12/31/9999null null

    1/1/2006 12/31/99991 null

    7/2/2006 12/31/99990.65 17

    Instead of giving me 100 or 100 percent on the 100% percent it gives me NULL. Any idea why this is happening? TIA for all the geniuses out there.

  • June 23, 2008 at 9:35 am

    #832811

    Try to see if there is any formatting on this column (in excel spreadsheet) ... if there is any that will not be carried. Only actual data with its datatype will be carried

  • June 23, 2008 at 9:41 am

    #832820

    Well there's evidently something really screwy in the driver because I not only recreated your problem, I was able to introduce new problems.

    However, I was able to do it with not problems at all by saving the .xls to a .csv and importing it as a plain txt file.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • June 23, 2008 at 9:48 am

    #832827

    Any ideas as to when MS will release a better xls driver, as the current one certainly has a couple of pitfalls in it?

  • June 23, 2008 at 9:52 am

    #832839

    I don't really have any insight into the office team. I'm on office'07 though and it appears to be buggy there too. However, I'm not sure if it's office or SSIS. I tried a handful of things and just couldn't get it to go either. I got really strange results when I switched the 100% to 'a' to try to force it to import as a string. And that's your problem mainly. You're formatting some of the columns as percent and others as decimal. So SSIS is making its col float and forcing the data into that mold... I was trying to force it to do string, but couldn't get any love.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • June 23, 2008 at 10:24 am

    #832874

    Wow ..didn't know I could get help so fast. The format on the excel columns are text. I tried to format it as General or money but still get the same results. I wanted to make this a automated process so I don't have to open the Excel and save it as .CSV or .txt. I'm i being too complicated? Sorry

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

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