JSON Output

[iwilliams]

I pulling a lot of data from an API.  When I parse out the response I get something like:

col1     col2

name1 val1

name2 val2 

.....

I would like to output the response as a json file structured like this: 

[

{record}, 

{record},

{record}, 

....

]

and each record is a dictionary formatted like this:

{'name1':val1,'name2':val2, 'name3': val3,.....}

where val could be a string, list, number, null, or dictionary

Any help with this would be appreciated

[Drussek]

Not sure how you recognize a record.In my solution I've added additional column with record id.

[iwilliams]

Thank you for helping.  So, recognizing a record is no issue because of how the response is, I can just group them in the same way you did (ex: it'll have something like data.# in the beginning and I can just parse out and group by the #).

Right now I am testing out your solution with a subset of the data.  One concern I have is the utilization of the summary tool.  I may have too much data for it all to fit into one cell.  Is there a way I can achieve the same functionality using the output tool?

Just to explain a little further...

I have done up to the point where each record is in its own row in a column called JSON, like this:

JSON

{record1}

{record2}

{record3}

....

Is there a way I can output it as a JSON organized like I mentioned before? (without putting them in one cell)

[Drussek]

@iwilliamslook at this:

If you would add coma to every JSON and "[" as first row and "]" as the last one you can write it as csv and it should have desired format without Summary tool:

[Drussek · Accepted solution]

And just a small patch to my solution 🙂

Use rather Multi row formula just to add coma to every row but except the last one.

[iwilliams · Accepted solution]

Thanks to everyone who replied.  I ended up finding an alternative solution that was close enough to what I needed.  Once I had the data in this from:

JSON

{record1}

{record2}

{record3}

....

I could output the data as a JSON with page code as UTF-8.  

[Drussek]

@iwilliamshehe replied only me. I've given you ready solution but you marked your .... sth.

Don't wish you best

[iwilliams]

@Drussek I ran into some issues last week with this workflow, so I didn't have time to try out your solution and ended up finding my own.  I have some deadlines to meet with this workflow and I knew I probably would not get a chance to try your solution until next week, so I decided to at least explain what I did in case someone else was attempting to do the same thing.  I still intend to try out your solution and will accept as a solution if it works for what I was trying to do.  I just need some time in order to do that.  Sorry if I offended you, your help was appreciated

[iwilliams]

some small changes I made to your patched solution:

checked "Set a Specific Output Order" in the Union tools

changed the output file extension from .txt to .json

made the delimiter of the output \0