Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting Complicated JSON to csv

MAAbdullahAlMubarah
8 - Asteroid

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

9 REPLIES 9
dantvli
6 - Meteoroid

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
12 - Quasar

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
12 - Quasar

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
6 - Meteoroid

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
12 - Quasar

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

 

Seth

jdunkerley79
ACE Emeritus
ACE Emeritus

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
12 - Quasar

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

MAAbdullahAlMubarah
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

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:

2019-01-02_20-56-31.jpg

 

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.

Labels