community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Converting Complicated JSON to csv

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

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.

Bolide

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

Bolide

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.


 

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!

Highlighted
Bolide

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

 

Seth

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

Bolide

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

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 

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