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.
Is that a bug or a feature of the CSV Output tool?
I managed to generate a properly formatted JSON file, that I am exporting to CSV to be used outside of Alteryx, and for some reason I cannto comrehend, the OUtput process is adding quotes to my already quoted fileds?
Alteryx is showing in the display window data as it should be:
Yet, opening the actual CSV output, every field has been double double quoted!!
And same thing happens if I remove option 3 in the output: Delimiters set to blank...
I don't understand why Alteryx displays it correctly and outputs it wrong? Workflow v11.4 is attached...
It depends on what you want ;) Your initial post is a CSV file with 2 fields - the Record ID and the JSON string. The JSON string is quoted because there's spaces within the string and the quotes within the string are quoted again because they are quotes.
With my workflow, I've created a field for each field in the JSON file (so more fields), but every value is in it's proper field.
Both ways sound like they are not the right solution (which is cool), but the answer is really that you have to do something between JSON input and CSV output to get it into the format that you want.
Are you after a single field containing the JSON, or the JSON split between many fields ?
I don't understand why Alteryx displays it correctly and outputs it wrong?
The output is not wrong, you're looking at it wrong ;) ..... Alteryx shows the value of the field. But then you write to CSV and Alteryx says (very correctly) I'm writing to a CSV file that has commas in the field, so I have to quote it. The field also contains quotes, so I have to double quote that. Excel knows how to interpret the quoted fields and double quoted quotes properly. Notepad shows you the raw output - both are actually correct but you need to know what happens downstream from here and what you have to do to fix it.
The "easy" answer is to change all your doubles quotes to single quotes, but I think proper JSON needs double quotes.
Can you change the design so that you're not outputting to raw JSON to a CSV file ?
OR, what is ingesting the CSV file and trying to read JSON ?
The problem here is that CSV and JSON are incompatible formats. Both formats use double quotes and commas and treat them differently so you can't have one file that is legal in both formats. You can store your JSON output in flat text file with a .csv extension, but then csv parsers will choke trying to read it in.
If you format it so that CSV parsers can open them correctly, the JSON becomes "corrupted", i.e. gets double doubles.
The format above is what you're looking for to preserve the structure of the JSON, but its not a true csv and there is no way(Someone prove me wrong) that Alteryx can automatically output this by combining the 2 fields from your input within the Output Tool. You either get the double doubles or or the comma is missing between RecordId and JSON. If this format is what you need you'll have to build this string explicitly as one field and output it to a csv with the no delimters and auto-quoting turned off.