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.
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
Go to Solution.
Go to Solution.
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.
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.
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.
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!
Attached is the workflow. You can then Parse out the first column as necessary. I hope this helps.
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
Always happy to learn from an ACE! Thank you @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?
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.