Scientific Notation Issues: XLSX source data differs from what is shown in the Input tool

[Paul-Evans]

TL;DR - The numbers in my source file do not equal what is shown in Alteryx, and I don't like it. 

End goal:

I have field that contains 20 character alphanumeric (but predominantly numeric) ID values that I import from an XLSX file that I need to export to a CSV in standard notation (not scientific notation). 

Current Progress:

I have been able to convert the scientific notation to standard notation using ToNumber() in a Formula tool with a FixedDecimal data type. 

I bring the ID values with alpha characters back in by creating a new field that pulls the FixedDecimal for records that originally were in scientific notation, and pulling in the original values of the IDs that have alpha characters (and were truncated by using ToNumber).

Problem:

In the source file, the ID field is shown in full, with no scientific notation. However, it shows up as a V_String field with scientific notation in Alteryx with several digits at the end differing from the source file. This has been disconcerting, making me wonder what sort of other changes have been made that I'm not aware of. 

Constraint: 

While saving each tab of the XLSX file as a CSV to use in the input does work as a temporary workaround, it will not work as a long-term solution. In case it helps in isolating the issue, I did notice that the numbers had the same translation error when the correct ID numbers (V_String) from the CSV file were converted to a numeric type. 

Example:

Notice the difference in values:

Source file --> Input Preview --> Output 

The first value is originally 20160805162420000000, but ends up as 20160805162419998720 (and part of that change can be seen in the input preview with scientific notation).

Workflow (also attached):

[patrick_mcauliffe]

It looks like both Alteryx and Excel aren't understanding the M-notation (and I don't either).  Do you have a reference on how that is converted to a decimal?

[Paul-Evans]

@patrick_mcauliffe The field contains ID values that sometimes contain an alpha character. I am not trying to convert that to a number, but it impacts the automatic input data type and I needed to pull it back in when it was removed by ToNumber().

[agentzerow]

Might it help to bypass converting to a number?

[Paul-Evans]

@agentzerow Sorry, no that does not help. The number still does not match the source and the output is in scientific notation. 

[agentzerow]

Maybe I did not understand the problem correctly.The output in the workflow I attached matches the Source 'numbers' exactly. 

[Paul-Evans]

@agentzerow  I found where the disconnect was. Your workflow was in a more recent version, so I opened it with WinZip and redirected the input since the connection was lost. 

In your workflow you have the input file type as Microsoft Excel Legacy, but when I opened in with the regular XLSX format, it shows up differently and the resulting tokenization did not work. 

Thinking using a Legacy XLSX input file type could work on it's own, I tried it on my full data set, but when I switch that to Legacy XLSX, it converts the field to a numeric type with scientific notation and the related issues, which prevents using tokenization and prevents me from keeping it out of numeric form.

XLSX Legacy

Standard XLSX

[agentzerow · Accepted solution]

The imprecise number occurs because you have a string within the field. If you remove the last value it will convert your value to a more precise number in scientific format which can then be fixed.

[Paul-Evans]

Thank you for all your help, @agentzerow. That works to get make it equal the original data. 

On a related note, this and some additional tests I ran make it apparent that once a number is in scientific format, the later digits are not necessarily accurate.

Ultimately, I needed to get a clean version of the original file as the one I received had been opened and reformatted in Excel, which caused the ending digits to show as zeros in the first place. 

Lesson learned: if a number is showing in scientific notation, don't trust precision beyond what's shown.