Search code examples
c#sql-serverexcelssisscript-task

Failing to read String value from an excel column

SSIS script task reading only numeric values of excel but fails to read the alphanumeric values present in the same column

I've tried using IMEX=0, IMEX=1 and IMEX=2. But the Alphanumeric values are being generated as NULL in my SQL server table.

//Excel Connection String
string ConStr;
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + 
";IMEX=0\"";

If the 1st 8 column 'A' values are integer (0,1,2,3,4,5,6,7) and then comes alphanumeric (A,B,ABQX345) values in below rows, then it should read all the values as it is and insert into SQL Server table as

0,1,2,3,4,5,6,7,A,B,ABQX345
Solution

  • This issue is related to the OLEDB provider used to read excel files: Since excel is not a database where each column has a specific data type, OLEDB provider tries to identify the dominant data types found in each column and replace all other data types that cannot be parsed with NULLs.

    There are many articles found online discussing this issue and giving several workarounds (links listed below).

    But after using SSIS for years, I can say that best practice is to convert excel files to csv files and read them using Flat File components.

    Or, if you don't have the choice to convert excel to flat files then you can force excel connection manager to ignore headers from the first row bu adding HDR=NO to the connection string and adding IMEX=1 to tell the OLEDB provider to specify data types from the first row (which is the header - all string most of the time), in this case all columns are imported as string and no values are replaced with NULLs but you will lose the headers and a additional row (header row is imported).

    If you cannot ignore the header row, just add a dummy row that contains dummy string values (example: aaa) after the header row and add IMEX=1 to the connection string.

    SchemaMapper Excel Import class

    In addition, it is good to check the following class which is a part of SchemaMapper project, I implemented the logic mentioned above in order to fix this problem:

    Helpful links