SQL Server Performance

excel data type import problem

Discussion in 'SQL Server 2005 Integration Services' started by beav1013, Oct 7, 2008.

  1. beav1013 New Member

    Hello. I'm trying to import an excel file that is sent from an outside source, several times a month, and loaded into sql '05. All of the columns are text in Excel, but some of the values start with characters and some with numbers. The values that start with numbers are not getting loaded into the table, they're going in as NULL. If i go in and double click on the field with all numbers in Excel, I get the little green arrow in the corner telling me it's a number in a text format... Then the number gets imported to the table.
    I have an Excel Source object pulling the worksheet in Excel, and looking at the Preview of the file also shows NULL for those values.
    Any thoughts on how to get around this problem without having to go into each cell and "activating" it?
  2. beav1013 New Member

    I found it online. In case anyone else has the same problem, add IMEX=1 to the end of the connection string in the Properties of the Connection Manager for the excel file so that it looks like this:
    ..."EXCEL 8.0;HDR=YES;IMEX=1";
    Adding IMEX=1 forces the values being imported to text.

Share This Page