Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

NUMBER IN STRING CELL CONVERTS TO SCIENTIFIC NOTATION WHILE EXPORTING CSV

Shell
6 - Meteoroid

I haven't found any solution for this strange behavior and it's hard to believe it is not doable. 

I am loading an excel spreadsheet, and matching it with a SQL table on a COMMON NUMBER-LOOKING STRING COLUMN to find delta.

I don't seem to have any issue with the processing of workflow itself, however when I export the result into a CSV file, the very same column changes to 1E+12 format. 1.png

As it can be seen in highlighted, the equipment column looks OK up to the last "input connector" of the output tool. however, the below snippet shows the same column turned into scientific notation. 

2.png

 

I have tried to add an apostrophe before the string to force it to perceive it as string but that added an extra character at the beginning of the number-looking-string. Excel perhaps would understand that but CSV is not that smart. Regardless, I can't add any leading space to it because there is another procedure takes this file and loads into a live table. I also tried adding following formula but that didn't help either.

ToString([EQUIPMENT],0)

 

What I want is for this string column to be exported as it is in CSV.

 

Any idea where I am dropping the ball?

 

Thanks in advance.

 

5 REPLIES 5
DavidP
17 - Castor
17 - Castor

I think your data is actually correctly written to the CSV file - try opening the file in a text editor like notepad.

 

It's most likely Excel that is not displaying it correctly when you open the csv file in EXcel

AmandaH
8 - Asteroid

Have you tried placing quotes around the data? Doing the below in a formula tool should tell Excel when you open the file that this field is a string value.

 

'"' + ToString([EQUIPMENT],0) + '"'

 

Shell
6 - Meteoroid

David, you're right. 

 

Opening in Excel does it. 

 

image.png

Shell
6 - Meteoroid

Amanda, 

 

I did try adding leading apostrophe but didn't see the expected result - it simply added an extra character. When you mentioned wrapping, sounded like a good idea to tell excel. but unfortunately it made the matter worse. 

 

image.png

Whooppee
6 - Meteoroid

Hi, 

 

I have the same issue as Shell.  However, my csv file is being uploaded into another software and will not work with scientific figures.  My issue is the customer account #'s are going to scientific vs numeric or text.  

 

I have tried changing the type to string, FixedDecimal, and  Int16.  I have tried a formula to put ' in front of the numbers but I am getting errors for that.  

 

Nothing seems to fix it.  

 

 

Labels