February 2, 2012 at 10:55 am
I have an Excel 2010 spreadsheet that I need to import. That spreadsheet has a column. In some cells in that column, the values are strictly letters. In some cells, the values are a combination of letters and numbers. In some cells, the values are strictly numbers. Of the cells with strictly numbers, some cells are formatted as text and some cells are formatted as numbers. When I import the file using SSIS 2008 R2, SSIS seems to assume that the column consists only of numbers. The destination for this data is a SQL Server table where the column is an nvarchar(255).
How can I get SSIS to assume that the source column in the .xlsx is the same datatype (text) as the destination instead of assuming that the data is numerical?
February 2, 2012 at 12:06 pm
imani_technology (2/2/2012)
I have an Excel 2010 spreadsheet that I need to import. That spreadsheet has a column. In some cells in that column, the values are strictly letters. In some cells, the values are a combination of letters and numbers. In some cells, the values are strictly numbers. Of the cells with strictly numbers, some cells are formatted as text and some cells are formatted as numbers. When I import the file using SSIS 2008 R2, SSIS seems to assume that the column consists only of numbers. The destination for this data is a SQL Server table where the column is an nvarchar(255).How can I get SSIS to assume that the source column in the .xlsx is the same datatype (text) as the destination instead of assuming that the data is numerical?
You can add IMEX=1 into your Excel source connection string. This property will tell the Jet driver to always read intermixed values as text.
If I recall correctly, there is also a registry setting you can change to force the Jet driver to consider all rows in the Excel file before deciding on the data type. I think by default it only looks at the first 8 rows of data.
Not 100% sure what the registry key's name is, but you should be able to find it with a little research.
February 3, 2012 at 12:38 am
Martin Schoombee (2/2/2012)
imani_technology (2/2/2012)
I have an Excel 2010 spreadsheet that I need to import. That spreadsheet has a column. In some cells in that column, the values are strictly letters. In some cells, the values are a combination of letters and numbers. In some cells, the values are strictly numbers. Of the cells with strictly numbers, some cells are formatted as text and some cells are formatted as numbers. When I import the file using SSIS 2008 R2, SSIS seems to assume that the column consists only of numbers. The destination for this data is a SQL Server table where the column is an nvarchar(255).How can I get SSIS to assume that the source column in the .xlsx is the same datatype (text) as the destination instead of assuming that the data is numerical?
You can add IMEX=1 into your Excel source connection string. This property will tell the Jet driver to always read intermixed values as text.
If I recall correctly, there is also a registry setting you can change to force the Jet driver to consider all rows in the Excel file before deciding on the data type. I think by default it only looks at the first 8 rows of data.
Not 100% sure what the registry key's name is, but you should be able to find it with a little research.
edit: this links shows the registry path for Excel 2003 which uses the JET provider. Excel 2007 uses the ACE provider, so you need to search for TypeGuessRows in that directory.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 3, 2012 at 1:48 pm
Thank you, this solved the problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply