Certain numeric fields not read from the Excel file when using a Excel file source.

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

Try doing a data conversion of this column in the transformation phase using the Derived Column Transformation or the Data Conversion Transformation. Hope that works for you.|||

It has to do with a registry setting. I got a response from some other forum and I tried it and it worked.

|||I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.|||

Al C. wrote:

I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.

Can you set up the Excel connector to import all fields as text fields, and then later convert them to numerics using a derived column transformation?|||

Phil,

TFYR. I verified in the Excel Source (Advanced Editor) that it is using DT_WSTR in both the External columns and Output columns. When I click the Preview button it shows NULL in the first two rows. If I add a DataViewer it also shows nulls. Evidently the first two rows are considered text even though the values are numeric. I can see the difference when I use the formula auditing tool and the first two rows align to the left (indicating text) and the others align to the right (numeric). So what I have gathered is that Excel samples the first nn rows to determine the datatype and since most values are numeric it deems the datatype of the column to be numeric and so it replaces the first two values with nulls. If I modify the Excel connection string and add 'IMEX=1' (import mode=1) to the extended properties, then it works OK.