excel source to import data gets some values as null

Question

Post reply

excel source to import data gets some values as null

M Shah

SSCommitted

Points: 1683
More actions
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

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

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

Lookup_BI SSCrazy Points: 2964 More actions · Answer 1

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]

Tim Mitchell SSCoach Points: 15685 More actions · Answer 2

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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 3

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.

Tim Mitchell SSCoach Points: 15685 More actions · Answer 4

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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

john_gordon_78 Valued Member Points: 61 More actions · Answer 5

john_gordon_78

Valued Member

Points: 61

November 26, 2007 at 6:35 am

#754004

Checkout

M Shah SSCommitted Points: 1683 More actions · Answer 6

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

Manisha