March 8, 2012 at 11:56 am
I have an excel sheet that I want to import into SQL server table using SSIS.
There is a column called RangeCd, basically is like grade level.
The data should like 01, 02, 03, ...09, 10, 11, 12, K, PS, K-9.
Now in the excel it is formated as text column, but the leading 0 is lost. How can I add leading 0 for number 1-9, but still keep the other charaters like K, PS, K-9, k-1 there. Also I would like the column still formated as text.
I know I can use custom 00, it looks right, but when I import using SSIS, it complains it, it cannot recognize the data type of those with chararters.
I also tried to copy and paste special to another column, but it still has custom format there, I would like it should change to text.
I think basically I want those numbers saved as text. like 09, 08.
March 8, 2012 at 1:38 pm
The problem is that the JET OLE DB provider - used to read Excel files - tries to "guess" the datatype.
It scans the first 8 lines of the column and inspects the values. If it sees numerical data, it thinks the entire column is numeric. In a numeric datatype, 09 is converted to 9. String are imported as NULL.
To change this behaviour, you need to add IMEX=1 to your connection string (http://www.connectionstrings.com/excel) and set the TypeGuessRows registry setting to 0. The first one will tell the JET provider that if multiple datatypes were found, the resulting datatype should always be text. The second one tells the provider to scan the entire column instead of the first 8 lines.
http://support.microsoft.com/kb/281517
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply