Maintaining data types from workflow to CSV

I have a field that is made up of alphanumerics , and some have a preceding 0 (zero). Some examples are C99, THK, NOT, PC, 054, 958. I have a selection that has only the numeric looking data; 054 and 958. I output it to CSV file. It drops the preceding 0 (zero) and makes it a numeric data type. Is there anyway to over come this? I need to retain the preceding 0 (zero) and the text data type.

Output

Accepted answers

DavidxL

Refer to this link about how to force Excel to recognize it.

If you force the field to be encapsulated with "="" """, then Excel will automatically recognize it as a formula and leave it as text. However, the CSV won't look right to other programs like a normal text editor.

I also had to disable "Quote Output Fields" in the output tool so that it doesn't add an extra set of quotation marks. If you have other fields in the file that contain commas, then you will want to remove the outer quotation marks in the formula and re-enable this option.

CSVOutput.yxmd

All comments

MarqueeCrew

@robertosborn,

If you have a field that is alphanumeric, it is treated as a STRING data type in alteryx. It will maintain all spaces and zeros. If you have a numeric field and want to output it with a specific format of leading zeros, then you should convert the field to a string type (use a select) and then use a formula tool to pad it left with zeros.

The formula to create leading zeros is:

PadLeft([String],3,"0")

This will put 1 or many ZEROs (0) in front of of a value that is shorter in length than 3. It will leave alone any incoming data that has 3 or more characters. As an example, 10 will become 010 and 9 will become 009. It will also change AB to 0AB.

Please try this and see if it works for you.

Cheers,

Mark

robertosborn

I have attached how I implemented the suggestion. I am still getting the output of 5 when I need 005 to a .csv file. My issue is when the data is transferred to the .csv, it is losing its alphanumeric properties. I just wanted to ensure I was executing this properly

formula execution in workflow

.csv output

Browse tool results off of execution

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703