Import JSON

[sdemichelis]

Hi all,

I am trying to import a large file in JSON format. I have attached here a sample. I have tried to use the Json Parse tool but I get mostly blank fields. Is there a way to import this into a table format?

Thank you all for your help and suggestions

[grazitti_sapna]

@sdemichelis, is this what you are trying to achieve?

Try reading the file in json format it will work.

Thanks!

[sdemichelis]

Dear Sapna,

it should be converted in table format. See attached

[grazitti_sapna]

@sdemichelis, you will have to transform the data within alteryx workflow to achieve the result.

I hope this helps!

Thanks!

[sdemichelis]

Thank you. The layout is great and the rationale of your import is easy to follow. However, the test sample has more than one record. Why is alteryx limiting it?

[apathetichell]

There's a variety of strategies to properly aggregate each JSON record into it's appropriate category. Usually there is a JSON record number which is included but in your case it isn't... I'd use a Multi-Row Formula to find if the category was a "company_number" (my assumption is this is your primary key) and if non exists - i'd take the previous row's company_number - this allows for a key column for your crosstab...

see attached.

[grazitti_sapna · Accepted solution]

@sdemichelis, the format of your JSON file is incorrect when I try to run it as ".json" file format in Alteryx. Please refer to the screenshot below.

Then I checked this on JSON checker online and it showed me the exact error.

The issue is a [ is missing in the beginning and ] at the end and after each record, it should be followed by a comma separator, in your scenario is it not able to distinguish between the records that is why it just read the first record. Attaching the correct JSON file.

Now if you want to keep the same JSON format as per the original file then you can use the below workflow.

Workflow 1

Use the following configuration

Output

Workflow 2(with correct JSON format file)

Use the JSON file attached along

Please let us know if it works for you!

Thanks!

[sdemichelis]

Thank you

[grazitti_sapna]

@sdemichelis, if this approach works for you could you please mark my post as a solution so that it could help others for future references.

Thanks!

[sdemichelis]

Hi Sapna,

currently importing the full dataset. Will revert as soon as possible. Meanwhile, thank you for taking the time to assist

Stefano