JSON Parse

[MysticalAz]

Hi Community!

Its been almost a month of me trying to parse this file. I'm leaning towards you for some assistance. Any help would be GREATLY appreciated. 

I'm uploading this as a .csv file, no delimiters. The issue is that inputting gives no unique identifiers..

[mceleavey]

Hi @MysticalAz ,

there's not a lot of data in that file, but using the JSON Parse tool results in the following:

The workflow uses the JSON Parse tool as follows:

What else are you trying to pull from the data?

Once you have the data in this format, you can use the Text to Columns tool to separate the JSON_Name column by the "." delimiter. 

I've attached the workflow showing the use of the JSON Parse tool and how to configure it.

M.

[MysticalAz]

Hello! There's definitely much more data than that and that is what my initial input looked like as well until I changed the input settings (below). I'm trying to create an easily readable document that I can output to excel. This document contains a whole lot of gibberish and parsing out rows, columns and its values has been a nightmare.  

I'm thinking the input settings are incorrect which is why its become so difficult to parse. But believe me when I say there's more data! lol

[mceleavey · Accepted solution]

Hi @MysticalAz ,

Yes, I can see the text was truncated.

I've built this for you now.

The primary issue with this JSON file is it has been built with a varying number of delimiters, and the delimiters mean different things. This often happens with poorly constructed JSON files, and so you end up with one delimiter meaning a different section or column, and the other meaning a different type of the same thing within a column.

The perfect example of this is with the "Given_Name" field, which is denoted differently according to "Assignee" and "Submitted by", for example.

I've built it so it automatically determines where this field is "duplicated". If it is, it takes the last two populated columns after the text to columns splits the field. This denotes the last two sections, and concatenates these two fields together to create Assignee Given Name and Submitted By Given Name. 

This is relatively complex, but basically it takes those where a unique field name is pulled directly from the parsed JSON, split to columns. Those that are duplicated then append the section before. 

Here is an example of the output:

I hope this helps,

M

[MysticalAz]

This is great!!!!!!!!!

I attempted to this this on my larger data set which should output about 164 rows, but this workflow and the one I've been struggling with gives 20. Any idea why? Attached document. 

[mceleavey]

Hi @MysticalAz ,

because there are only 20 records. I see no reason why it would parse into any more. The ID delimiter only goes to 19, starting at zero.

M.

[MysticalAz]

The same file was downloaded as excel and its 164 rows. When inputting this into Alteryx with delimiters blank, it gives about 33K rows to parse (see below). I believe there are more than 20 rows so I'll keep playing around based on your workflow. 

Appreciate your help!!