Maintaining data types from workflow to CSV
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you DavidxL!
This gets me to where I need to be.
