Mixed type column import from Excel to SQL Server(IMEX cure)


When you import data from excel to SQL Server, the mixed column will skip one type of data because of the use of TypeGuessRows  in the background. By seeting the property IMEX to 1 in the connection string, this issue can be corrected. Here is the information about IMEX I grabbed from connectionstrings.com.:
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative. Source: http://www.connectionstrings.com/?carrier=excel
 
The connection string I used for my SSIS package to loop through a folder to upload multiple excel files is:
 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::myFileName] + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES\";"
myFileName is a pakage variable in my SSIS pakage for looping(I tested this in a test SSIS pakage under "bioData1" at my office computer.
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s