Maintaining data types from workflow to CSV

[robertosborn]

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.

[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

[DavidxL]

Is it possible that this is an Excel issue? Excel will default to auto-formatting data. Try opening the CSV in a text editor first to verify that the data is being generated by Alteryx correctly. If it is, you should use the data import in Excel (treat as text) to ensure that Excel does not remove the padded zeroes.

[robertosborn]

I was leaning towards that as being the issue.  I am trying to automate something and importing the data in to Excel manually is not acceptable.  The column in the .csv is a "general" data type (would expect numeric if leading 0s get shaved off) so not sure if it's Excel doing it or Alteryx.

[DavidxL · Accepted solution]

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.

[robertosborn]

Thank you DavidxL!

This gets me to where I need to be.