Issue: Decimals getting converted to exponential format.

[Anonymous]

Hello All,

We are loading data from an excel file. The column in question is used to store all kind of invalid values [might be a date, a string, a numeric value or even NULL]. The format for that column is specified as 'General' in the excel file.

During the load, the small decimal values [-0.000001] are getting converted to their exponential form [-1e-06]. This conversion is not desirable as we need to see the number the way it is.

Tried loading as Text, and also tried various number format but none seem to achieve the purpose. On using Num(ColName, '#,##0.0########') in the load script we are able to get the correct number but additional [non significant] zeroes are appended to the end of the number to account for the remaining '#' signs that we use [it loads -0.000001000 in place of -0.000001]

Any suggestions regarding how to load the contents the way they are in the excel file?

Thanks in advance for any pointers / suggestions

-

Thanks !

[Anonymous]

Thanks Marcus !

Currently the aim is to display the invalid values correctly. Also, we are not crossing the 15 digit limit. I shall explore more options

Thanks again for your inputs !

[Anonymous · Accepted solution]

Thanks Jaime !

I have used the Column like '*e*' part in the existing solution and it seems to fill the missing blocks. Earlier i had issues as all values were getting modified but now only the ones that have exponential form are being changed.

For now I have modified your logic as :

if(IsNum(Column) and Column like '*e*',Replace(RTRIM(Replace(Num(Column,'##0.#############'),'0',' ')),' ', '0'),Column) as Column,

and it seems to do the trick

-

Thanks !