JSON to Excel and CSV
Converting JSON into a table almost always involves losses. The question is whether they are deliberate or accidental.
What gets lost and why
- Structure. A flat table cannot express nesting. Objects are expanded into columns with dot notation, and that is only partly reversible.
- Arrays of objects. A list inside a record is a second table. In a single column it survives as text, but you cannot work with it.
- Types. JSON records types explicitly. CSV has none at all, and the reading side has to guess them again.
- Long identifiers. A number like
78123456789012345will lose its last digits under careless handling.
The order of work
Find the array with the data. In an API response it rarely sits at the root — usually under data, items or result. If there are several arrays, choose deliberately: sometimes you want the small lookup list, not the large list of records.
Decide what to do with nesting. Expand into columns or leave as is — it depends on whether you need those fields in the table.
Check the types. Pay special attention to identifier columns: they must stay text.
Export. To XLSX if the file is for a person; to CSV if another program is waiting for it; to SQL if the data is headed for a database. In the CSV export you choose the delimiter, the encoding with or without a BOM, and the date format to suit the program that will open the file.
Paginated exports
An API hands out data in pages, and you end up with a set of files with the same wrapper. Merging them into one table by hand is tedious.
If the API can return JSON Lines, take it: pages are joined by plain concatenation and you get one file that reads as a table. There is a separate article about the format.
If it cannot, process the pages one at a time and export each; merge them in the final tool.
Checking the result
Two checks catch almost all conversion errors:
- Row count. It must match the length of the source array. A difference means the wrong array was taken or some records were filtered out.
- Spot-check the identifier columns. Open the resulting CSV and make sure long numbers did not turn into exponents and codes with leading zeros kept their zeros.
FAQ
Can I export only some of the fields?
Yes: hide the columns you do not need, and only the visible ones go into the export.
How do I export a nested array as a separate table?
At the moment this takes the source file and choosing another array in File parsing.
Will the order of records be kept?
Yes, unless you applied a sort: rows follow the order of the source array.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.