Scientific Notation Issue while loading data from Excel (xlsx) file to SQL Tables via SSIS

0
votes

I'm loading data from excel file (.xlsx) to SQL table using SSIS package. For one column it's adding scientific notations in the data, it's already there in the excel file. But it's actual value is not loading to SQL table. I tried multiple option of derived columns, expressions etc. But I couldn't get the proper value.

This column has data of numeric and nvarchar values. Below is the example of the column.

ApplicationNumber  
1.43E+15  
923576663  
25388447  
TXY020732087  
18794588  
TXAP0000140343  

**Actual Values -**  
ApplicationNumber  
1425600000000000  
923576663  
25388447  
TXY020732087  
18794588  
TXAP0000140343

There is no issue with data coming from Business to Excel. But how we can handle this scenario in SSIS ? I also tried (DT_I8)ApplicationNumber==(DT_I8)ApplicationNumber, But it giving values for the above 1.43E+15 -> 1.430000000000000 and not the 1425600000000000

2
sql-serverexcelssisbusiness-intelligence
Excel has all kinds of issues as a source. What happens if you save the file to a CSV? If that works, then I would recommend importing that instead. – KeithL
I tried it, but in CSV also it adds scientific notation. But we have to apply generic solution here. – Ajit Medhekar
By But it's actual value is not loading to SQL table do you mean the row loads but the column is NULL? You are really going to have no end of issues with excel as a data interchange format. The first thing to do is to try the IMEX trick stackoverflow.com/questions/3595131/… social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Nick.McDermaid
Is it possibile for you to make this before import the file? superuser.com/a/1301584 – Nicolaesse
@Nicolaesse : No, this file is coming from business user, so we can't update/modify it manually. Even for settings changes. – Ajit Medhekar

2 Answers

1
votes

One thing you can do is set the output in advanced editor of the excel source as decimal with a large scale, 20 digits for example:

enter image description here

UPDATE

to consider also strings in the same column you may need to redirect the error output as these will throw a conversion error:

enter image description here

enter image description here

in advanced editor:

enter image description here

Default output:

enter image description here

Error output:

enter image description here

Then you can update your database from both the default and the error output.

0
votes

I faced this problem recently using SSIS too.

1- Change the column type in Excel to "Number"

2- Remove the decimal positions.

3- Upload the file using SSIS