2

I have some excel data which contains scientific numbers like 5e+00. When the see the value in excel by clicking edit button I can see the full value. But when I import the data into table I am getting the data loaded as Null. I need to import the data without doing any changes in excel. Please suggest how to do it in SSIS.

I tried imported by changing the format in excel side. I want it to be done in ssis level without doing any changes in excel

Data in my Column as

Amounts
15880
5e+19
57892

I expect the output should be like as follows

1588007
500000000019
57892

But I am getting Null value for second item

Please suggest.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Bhushan
  • 114
  • 12

1 Answers1

2

In the question above, there are 2 problems:

  1. Numbers are shown in scientific format
  2. Data is replaced by Null values while importing

Scientific Format issue

You mentioned that:

I tried imported by changing the format in excel side. I want it to be done in SSIS level without doing any changes in excel

Unfortunately, this cannot be done without changing the Excel file, since the only way to solve this issue is to change the Number Format property of the cells. You can automate this step by adding a Script Task that uses Microsoft.Office.Interop.Excel.dll assembly to automate this process instead of doing it manually from Excel.

You can refer to the following post as an example:

But make sure to use:

m_XlWrkSheet.Columns(1).NumberFormat = "0"

To force a Numeric format.

Null Values issue

This issue is caused by the OLE DB provider used to read from Excel files, This error occurs when the Excel column contains mixed data types, the OLE DB provider read the values with dominant data types and replace all other values with Nulls.

You can refer to the following links for more information/workarounds:

Hadi
  • 36,233
  • 13
  • 65
  • 124