SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


excel source to import data gets some values as null


excel source to import data gets some values as null

Author
Message
M Shah
M Shah
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 51
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
Lookup_BI
Lookup_BI
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 642
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?

“I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison
Tim Mitchell
Tim Mitchell
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1771 Visits: 2988
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


Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14343 Visits: 8978
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/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
Tim Mitchell
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1771 Visits: 2988
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


john_gordon_78
john_gordon_78
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 51
Checkout [url=http://www.sqlservercentral.com/Forums/Topic284613-148-1.aspx][/url]
M Shah
M Shah
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 51
Thanks the IMEX=1 tag at the end of the connection string worked like a charm.

Manisha
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search