Output JSON to CSV adds unwanted quotes

[fpinchon]

That one really baffles me...

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...

Thanks in advance !

Frederic

[estherb47]

Hi @fpinchon 

So I tried this out, and when I open the csv with Excel, it renders perfectly, with just one set of quote marks.



But, when I open it with Notepad, I find the same double double quotes as you do. If I read that outputted file back into Alteryx, the extra quote marks disappear.

It seems to be a quirk of CSV's (although this article relates to Excel, it explains things https://superuser.com/questions/349882/how-to-avoid-double-quotes-when-saving-excel-file-as-unicode)

Are you able to save into a different file type? 

Cheers!

Esther

[fpinchon]

@estherb47 

That's my experience as well, Excel format does not bug that way, and the CSV looks correct in Alteryx, but notepad shows the double double.

When I try to inject that CSV through API, the receiving endpoint sees the double double as well, alas...

To your point of using another format, well I need a format which will accept cells over 256 characters to accommodate JSON Strings, which is not the case of Excel... Any recommendation?

[cmcclellan]

You need to do a bit more to properly parse the JSON to CSV though.  Because you haven't really created fields for the CSV file (yet)

Try this (attached)

[fpinchon]

I like how you are thinking, but I am trying to fix the last step of a workflow that created that JSON file, from a table, so I don't want to parse it into a table again...

The larger workflow is meant to prepare a table to be injected to an API, and thus needs to be JSON formatted into a target wide cell > 256 characters...

And APIs don't like double doubles :(

[cmcclellan]

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 ?

[fpinchon]

I am after a single field containing the JSON, as it is a whole array...

[cmcclellan]

And I guess you need double quotes in the JSON string .... you don't have much choice.

---

@fpinchon wrote:

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 ?

[danilang]

Hi @fpinchon 

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. 

RecordID,JSON
6,{"id":"10002234","Productline__c":"Education Edge;...
7,{"id":"10002260","Clarify_Site_ID__c":"11729"}
8,{"id":"10002279","Productline__c":"GIFTS; Target A...
9,{"id":"10002775","Productline__c":"Everyday Hero; ...
10,{"id":"10003049","firstName":"obsdonot","Productl...
11,{"id":"10003092","company":"Really big one","webs...
12,{"id":"10003523","Productline__c":"","Clarify_Sit...
13,{"id":"10003577","company":"humans","Productline_...
14,{"id":"10003589","firstName":"George","title":"As...

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.  

Dan

[fpinchon]

So when you try to output into a csv with no delimiters and auto-quoting turned off, you get this error message:

So I tried to export to a file with a .CSV extension, using the .flat extension type, and then it works, but I do lose the comma separation between the 2 fields:

Just can't win that one...