February 26, 2012 at 11:25 pm
I have an excel file to process in SSIS. One of the column in excel is having different types of values like
2012,
2011,
2000,
1998,
1/1/2011-12/12/2012
2012+
Post 1/12012
2/2/2012
...
...
So mixed of values. But excel is considering it as a float column instead of varchar / nvarchar. So when i try to process the data, the string values are getting truncated to blank.
If i open the excel sheet and do the "format cell" of that column to "text" then it is coming as string data type in SSIS but this file is provided by client on a daily basis and we cannot change it manually before running the package.
Is there any way where i can change the data type of the column for further processing.
February 27, 2012 at 12:52 am
Add IMEX=1 to the end of the Excel connectionstring.
Connection strings for Excel 2007
If different data types are found, the JET or ACE OLE DB provider will automatically take string as datatype.
Also set the TypeGuessRows registry setting to 0. This will make the provider scan the entire column to determine the datatype instead of the first 8 rows.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 27, 2012 at 10:28 am
This really worked perfectly as i was looking for.
Now the data type is always coming as nvarchar as i wanted.
Thanks for the solution.
February 27, 2012 at 11:42 am
agg_rakesh (2/27/2012)
This really worked perfectly as i was looking for.Now the data type is always coming as nvarchar as i wanted.
Thanks for the solution.
No problem, glad to help.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 9, 2012 at 11:31 pm
I am facing another strange issue on the same excel.
One of the column in the sheet is having the data like
2003
2004
2006
2012
1/4/2012
4/10/2010
As of now, this column contains only these kind of values. So the data type for this column is becoming Integer in SSIS. It is returning integer values for dates as well (No of days since 1/1/1900). I want this to be String as well. So for development, i have entered some string records in this column (like "23232sddfd") and this column has become String and behaving as expected.
but when running this package with the actual sheet, it is again Integer. Please suggest how to go about this. I always wanted every column to be string so that i can do Data Type validations before processing further.
Thanks in advance.
March 11, 2012 at 2:52 pm
You still have IMEX=1 added in your connectionstring?
Did you modify the TypeGuessRows registry setting?
Is the type of the column in Excel set to Text, instead of General?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 5:59 am
Yes, both the settings are there (IMEX, TypeGuessRows) as suggested.
As of now the the data type of the column is mixed. Some are integer and some are dates.
If I change the data type of the complete column to Integer then it converts the dates to Integer and shows the no of days since 1/1/1900.
If i change it to datetime,then it converts integers values to dates.
March 13, 2012 at 6:02 am
And if you set it to Text?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 6:40 pm
After converting it to Text, it converts the datetime column values to Integer with the no of days since 1/1/1900.
March 14, 2012 at 12:34 am
agg_rakesh (3/13/2012)
As of now the the data type of the column is mixed. Some are integer and some are dates.
If you leave it to General, you have 2 different datatypes.
The JET provider should pick up that it is mixed and import it as string (that's what IMEX is for).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply