Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

excel source to import data gets some values as null Expand / Collapse
Author
Message
Posted Monday, November 19, 2007 11:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 18, 2010 3:56 PM
Points: 73, 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
Post #423760
Posted Tuesday, November 20, 2007 6:55 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 31, 2009 7:53 AM
Points: 150, 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
Post #424071
Posted Tuesday, November 20, 2007 7:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:19 AM
Points: 1,046, Visits: 2,739
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #424090
Posted Tuesday, November 20, 2007 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 5,672, Visits: 6,482
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #424099
Posted Tuesday, November 20, 2007 7:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:19 AM
Points: 1,046, Visits: 2,739
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #424105
Posted Monday, November 26, 2007 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 10, 2013 3:20 AM
Points: 1, Visits: 51
Checkout [url=http://www.sqlservercentral.com/Forums/Topic284613-148-1.aspx][/url]
Post #425727
Posted Monday, November 26, 2007 5:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 18, 2010 3:56 PM
Points: 73, Visits: 51
Thanks the IMEX=1 tag at the end of the connection string worked like a charm.

Manisha
Post #426114
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse