Post reply

excel source to import data gets some values as null

  • November 19, 2007 at 11:23 am

    #119215

    I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.

    Thanks,

    Manisha

  • November 20, 2007 at 6:55 am

    #752666

    1.Is the original for this specific fields are empty in the source?

    2.Why do you want it to NOT be Nulls?(most of us prefer Nulls in empty fields)

    3.What do you want to replace it with if you don't want Nulls in them?

    [font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

  • November 20, 2007 at 7:21 am

    #752682

    This is a common problem with Excel. One thing I've found is that if you change the row sample size that Excel uses (by way of the Access Jet driver) to determine the data type in your Excel document, you can get more accurate results. See the posting in the following link for this registry hack:

    http://msmvps.com/blogs/nickwienholt/archive/2006/03/15/86379.aspx

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • November 20, 2007 at 7:29 am

    #752688

    When SSIS looks at Excel for the import, it checks the values in the first 5 or 10 rows (I don't remember which). This is the sampling that Tim is talking about.

    The only way I found to resolve the problem is to move your rows around in the Excel sheet so that the first 5 / 10 rows have an even (or close to even) number of different values for that column. So, the first 2 rows would be decimal, the next two would be character. This way, SSIS defaults to the character as the best way of picking up all values.

    I have tried (in SSIS) to change all the datatypes around, but I still only ever got nulls until I did the above. Excel is horrible to insert from. If this is going to be a constant, automated process, I'd get the people providing the Excel sheet to instead provide a delimited CSV file. At least with a CSV file, you can change the datatypes in SSIS and still get the values you're looking for.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • November 20, 2007 at 7:37 am

    #752694

    Agree completely with Brandi about Excel and it's quirks (or are they features??). For some of my more difficult ETL, I've resorted to saving the Excel as a CSV before the ETL process to make sure the data will behave like I need.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • November 26, 2007 at 6:35 am

    #754004

    Checkout

  • November 26, 2007 at 5:32 pm

    #754338

    Thanks the IMEX=1 tag at the end of the connection string worked like a charm.

    Manisha

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

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