Converting Scientific Notation to Number

[Vermaipshita19]

Hi All,

I am having difficulties in converting large numbers into number format. 

My input is a text file, by default it takes all the feeds as V_WString and the number comes as a scientific number. Tried using multiple options-

1. Using fixed decimal- gives a null output

2. Using Double- converts keeps first few digits of the number and adds 0s in the end

3. ToNumber- converts keeps first few digits of the number and adds 0s in the end

Would anyone be able to help here?

Thanks,

IV

[apathetichell]

Can you post a sample line? I want to get the spaces accurate... Basically I'd convert using REGEX replace... something along the lines of (\d+).*X.*10\^([-\d]+) and then something where you use tonumber($1)*pow(10,($2)) - but the exact format is specific to your data.

[Vermaipshita19]

Hi, please find attached sample file.

[apathetichell]

Those are in standard notation.  Are those being read in as scientific notation? I was having some issues reading in the .txt file but just cut and pasted it and then ran it through to number() had no problems like that.

[apathetichell]

Does this work for you?

[Vermaipshita19]

Thanks Aurora. This is still not working for me. 

Probably, if you can try this example.

[atcodedog05]

Hi @Vermaipshita19 

Here is how you can do it. This looks like a tsv file where columns are separated by tab.

1. Read the file in the below configuration

2. Add a select tool after and convert amount datatype to double. It will automatically turn to number.

Hope this helps 🙂

[apathetichell]

Thanks - that's what I needed to see:

basically the formula I used to convert was:

tonumber(REGEX_Replace([Amount],"(-*).*","$1")+REGEX_Replace([amount],"-*(\d+\.\d+).*","$1"))* pow(10,tonumber(regex_replace([amount],".*\+(\d+)$","$1")))

with a fixed.with 35/6 - note. it worked for me as a straight double and but I wasn't sure how large some of your other values were. You can adjusted your fixed with for your max size as needed.