How do I parse large json from an API?

Question

I am trying to create a data table from an API response but the JSON that is output won't process and I think the file is too big.  It's about 20 MB and the final data table is ~7000 rows and ~115 columns.  Is this just too big to parse?  I also have the option of getting a csv file from the API or output as a text string.  I work with the developers that wrote the API so we can make it output whatever we want.
I have verified that the JSL script to hit the API is working and I have been able to parse much smaller JSON files from the same API.  I just need to figure out how to handle the larger ones.

Craige_Hales · Answer

What sort of error? Anything in the log window? Which pandas frame orientation? "Orient" is a pandas keyword. JMP should show you a choice for that keyword on the wizard's guess menu.

MQ_Smith · Answer

We are using pandas to_json() to make the dataframe into json.  When I try to open the json file using the wizard I get a really long error.

Craige_Hales · Answer

there are two competing JSON parsers in JMP. You probably want to use the newer JSON Wizard.

Download a representative xxx.json from the API. From JMP's file->open dialog, pick the JSON file type and pick the wizard.

From the wizard, try the guesses. If you can get the dev team to write a pandas-like format, that might make it even easier.

If none of the guesses work, the wizard can import using information you supply about the structure of the JSON, but that requires a bit more study.

The script saved in the table can be used for other similar files, without opening the wizard again.

The file size you describe is not too big; there should be a progress dialog and I'd guess about a minute.