SSIS import mixed datatypes from Excel to SQLServer

While importing data from Excel to SQLServer, if a Excel column in has both Text and Numeric values then either Text or Numeric values will be NULL in SQLServer.

See example..

If first data cell (in Excel) in that particular column is of type String/Text then all Numeric values in that column will be NULL in SQL table or viceversa.

This NULL can be avoided in 3 different ways

1. Manual Change

Manually add single quotes (') before each Numeric cell and convert it to String / Text type. This way all the values will be exported to SQLServer without NULLS.

2. Excel to Access to SQLServer

If number of rows to modify is more then …

First export the excel data to MS-Access database and then export Access DB to SQL Server. This way all the values will be exporeted to SQLServer without NULLS.

3. Change EXCEL Connection String

This is simple method but it has its own disadvantage.

Append IMEX=1 to ExcelFileConnectionString. Adding IMEX=1 changes ISAM driver to Import Mode.

(click on the image for full view)

You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.

The possible settings of IMEX are:

0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)

More Info :

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: