Excel source output - E-2 values

Question

Excel source output - E-2 values

pwalter83

SSChampion

Points: 14575
More actions
October 22, 2018 at 5:33 am

#381603

Hi,
I am using an Excel source to upload data to Netezza. However, due to some weird reason the excel source output for many columns adds E-2, E-4, E-05 to the end of values and it shows up something like 2.54258742543E-5.
The datatype for Excel Source Output (External Columns) for the affected columns is double-precision float [DT_R8] by default. These are actually % values in the Excel sheet with values like 1.42545038678714% going upto 15 decimal places.
I have tried various datatypes in the Output Columns like DT_WSTR(Unicode String) and DT_STR(String) and also tried to keep the default datatype which is double-precision float [DT_R8] but the values still show up as 1.425450386787E-2 (example).
Has anybody seen this scenario or found this issue in SSIS ? Any suggestions / help would be appreciated. Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

pwalter83 - Monday, October 22, 2018 5:33 AM
Hi,
I am using an Excel source to upload data to Netezza. However, due to some weird reason the excel source output for many columns adds E-2, E-4, E-05 to the end of values and it shows up something like 2.54258742543E-5.
The datatype for Excel Source Output (External Columns) for the affected columns is double-precision float [DT_R8] by default. These are actually % values in the Excel sheet with values like 1.42545038678714% going upto 15 decimal places.
I have tried various datatypes in the Output Columns like DT_WSTR(Unicode String) and DT_STR(String) and also tried to keep the default datatype which is double-precision float [DT_R8] but the values still show up as 1.425450386787E-2 (example).
Has anybody seen this scenario or found this issue in SSIS ? Any suggestions / help would be appreciated. Thanks.

That's probably just Excel being its usual helpful self.

1.42545038678714% would be stored in Excel as 0.0142545038678714, which is 1.42545038678714 * 10^(-2), or 1.42545038678714 E-2.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246979 More actions · Answer 2

This is the same number, formatted as Scientific (left) and General (right).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pwalter83 SSChampion Points: 14575 More actions · Answer 3

Phil Parkin - Monday, October 22, 2018 5:54 AM
This is the same number, formatted as Scientific (left) and General (right).

Thanks Phil.

Do you know if this issue can be resolved at the staging layer itself.

The requirement of the business is to get clean data from Excel sheet as is in staging area without any E-2 etc values.

Do you know if it is achievable ? Thanks.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 4

pwalter83 - Monday, October 22, 2018 6:48 AM
Phil Parkin - Monday, October 22, 2018 5:54 AM
This is the same number, formatted as Scientific (left) and General (right).
Thanks Phil.
Do you know if this issue can be resolved at the staging layer itself.
The requirement of the business is to get clean data from Excel sheet as is in staging area without any E-2 etc values.
Do you know if it is achievable ? Thanks.

Not that I know of. Excel will present the data to SSIS using whatever display format is used when you open up the file in Excel.

The 'requirement of the business' should be to modify whatever is creating that Excel file to generate those percentages in General format. Or simply to use a CSV file.

Excel is a horrible tool for any sort of data transfer.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pwalter83 SSChampion Points: 14575 More actions · Answer 5

Phil Parkin - Monday, October 22, 2018 7:04 AM
pwalter83 - Monday, October 22, 2018 6:48 AM
Phil Parkin - Monday, October 22, 2018 5:54 AM
This is the same number, formatted as Scientific (left) and General (right).
Thanks Phil.
Do you know if this issue can be resolved at the staging layer itself.
The requirement of the business is to get clean data from Excel sheet as is in staging area without any E-2 etc values.
Do you know if it is achievable ? Thanks.
Not that I know of. Excel will present the data to SSIS using whatever display format is used when you open up the file in Excel.
The 'requirement of the business' should be to modify whatever is creating that Excel file to generate those percentages in General format. Or simply to use a CSV file.
Excel is a horrible tool for any sort of data transfer.

Thanks again Phil. Excel is really horrible for data transfer I have always known that but I just hope this issue is regarded as an Excel-SSIS limitation as opposed to lack of knowledge on my part 🙂

Phil Parkin SSC Guru Points: 246979 More actions · Answer 6

pwalter83 - Monday, October 22, 2018 7:47 AM
Thanks again Phil. Excel is really horrible for data transfer I have always known that but I just hope this issue is regarded as an Excel-SSIS limitation as opposed to lack of knowledge on my part 🙂

IMO, it's purely an Excel issue, specifically with the ironically named 'ACE' drivers, which provide data as per its display format (rather than how the data is stored).
Another great thing these drivers do is where you have financial data formatted using the 'Accounting' format. Any zeros in this data are passed to SSIS as '-', at which point SSIS terminates, with extreme prejudice.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel