0

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

Improve this question
5
  • 1
    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. Commented May 21, 2021 at 15:55
  • I tried it, but in CSV also it adds scientific notation. But we have to apply generic solution here. Commented May 22, 2021 at 19:33
  • 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/… Commented May 23, 2021 at 0:22
  • Is it possibile for you to make this before import the file? superuser.com/a/1301584 Commented May 24, 2021 at 7:15
  • @Nicolaesse : No, this file is coming from business user, so we can't update/modify it manually. Even for settings changes. Commented May 24, 2021 at 11:02

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

In your scenario, how it will handle nvarchar values of that column. It will throw an error. As I mentioned, below are the values and we have to apply generic solution for same. ApplicationNumber 1425600000000000 923576663 25388447 TXY020732087 18794588 TXAP0000140343
@AjitMedhekar yes, they will produce an error, so you may need to redirect those rows. I've updated the answer with examples
This looks better solution. But only the issue over here is, in my Source excel file, Application Number column's data type is taking decimal/float as there first 8 rows of the file are of decimal kind. I also tried IMEX=1, but with this I can't update registry value as it's a PROD server.
0

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

Improve this answer

2 Comments

No, this is not possible. As mentioned in my question, there are nvarchar values also there in the column. And we don't want to manually update any column of file provided by business user.
I see, then you need an additional step: Convert all the values in the column to string, then import to the database. Here a reference link how to do the conversion in Excel. ablebits.com/office-addins-blog/2014/10/10/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.