Converting Complicated JSON to csv

[MAAbdullahAlMubarah]

I'm looking to parse the attached JSON file into Excel (csv) can any one help me to do that?

Note the source file I take it from here: https://pastebin.com/Shggpn04

[dantvli]

I'm not sure if Alteryx is able to handle JSON files. I think your best work around is the look for another solution to convert it to a regular CSV -- i.e. python.

But then again, advanced members of the Alteryx community are welcomed to correct me.

[smoskowitz]

It is easy to handle JSON with the JSON Parse tool. Look for it in the Developer tab and has some examples included om how to use. If you have any issues using it -- let me know.

Read it in as json file with the input tool and then use the json parse. 

Seth

[smoskowitz]

Alteryx can indeed handle JSON with the JSON parse too.

---

@dantvli wrote:

I'm not sure if Alteryx is able to handle JSON files. I think your best work around is the look for another solution to convert it to a regular CSV -- i.e. python.

But then again, advanced members of the Alteryx community are welcomed to correct me.

---

[dantvli]

Thanks for correcting me. I am interesting in this topic. Would you have a dumbed down version of an example to show/ share?

Feel free to obfuscate values, and sample only a small set of nested json rows. Thanks!

[smoskowitz · Accepted solution]

Attached is the workflow. You can then Parse out the first column as necessary. I hope this helps.

Seth

[jdunkerley79 · Accepted solution]

I attached a sample building a table of the most top level data.

Basically the same as @smoskowitz but picking a set of fields and converting to a table.

What you can do is select any additional fields and join these back together.

Hopefully enough for you to get started with

[smoskowitz]

Always happy to learn from an ACE! Thank you @jdunkerley79!

[MAAbdullahAlMubarah]

Dear @jdunkerley79

I downloaded the JSON train dataset from kaggle competition: https://www.kaggle.com/c/two-sigma-connect-rental-listing-inquiries/data

I tried to implement it on your solution but I failed, Kindly can you tell me how to implement your solution on the file in the mentioned kaggle competition? 

Thank you 

[jdunkerley79]

A slightly different process to parse this file.

I downloaded the train.json.zip file for the example you gave. The process I would use looks like:

Alteryx will parse the JSON in the zip file as part of the Input tool.

The file is organised by field and then an array inside each.

I used a REGEX tool to break the JSON_Name into a Field, RecordID and Extra parts

I then split of those rows with something in the Extra field. This turns out to be photos and features. I chose to concatenate features back into a single field but you could do what you wanted.

Hopefully enough to get you going.