This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
TL;DR - The numbers in my source file do not equal what is shown in Alteryx, and I don't like it.
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).
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).
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.
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.
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).
@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 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.