API response → table → Excel
The task sounds simple: “we exported data from the system and need it in Excel”. In practice there are several places between JSON and a table where data gets lost or distorted.
Step one: find the data
An API response is almost never an array in its entirety. The data sits under a key — data, items, result, rows — next to metadata: a count, a cursor, a status.
Sometimes there are several arrays: the records themselves and a lookup list for them. A tool should show a list of candidates with the number of elements, not silently take the first one it meets. In Tabulens that list is in File parsing, and you pick the array you want.
Step two: pagination
An API usually returns data in pages of 100–1000 records. You end up with a set of files page1.json, page2.json, each with its own wrapper.
Two approaches. The simple one: open the pages as separate tabs and export each, if merging is not needed. The proper one: when exporting, ask the API for JSON Lines if it can do that — then pages are joined into a single file by concatenation and you get one table.
There is a separate article on this format.
Step three: types
This is where data is lost most often. JSON records types explicitly: a number is a number, a string is a string. On the way to a table they must be preserved — neither everything coerced to text nor text turned into numbers.
Identifiers deserve special attention. A field "id": 78123456789012345 is valid in JSON, but converted to a double-precision number its last digits are lost. Such fields have to stay text. Any column where values are longer than 15 digits always stays text. The same rule protects account numbers and codes with leading zeros in CSV; see the CSV section.
Dates in JSON arrive as ISO strings: 2025-01-15T10:30:00Z. They are worth recognizing as dates — then in XLSX they become real dates that Excel can calculate with. Booleans such as paid show up as checkboxes.
Step four: export
When exporting to XLSX it matters that numbers stay numbers, dates stay dates and codes stay text. Then the file opens ready for work: pivot tables can be built, filters work, nothing has to be “fixed” by hand.
If the data is meant for another system rather than a person, export to CSV or to SQL with a ready CREATE TABLE saves one more step.
When you do need code
A viewer solves “look once and pass it on”. If the export repeats every week, it is wiser to write a script: it will not forget a step or choose the wrong array.
The line runs along regularity: a one-off task is for a tool, a repeating one for automation.
FAQ
What if the response contains nested arrays?
Choose the level you need: either the records with a count of nested elements, or the nested elements themselves as rows.
Will long identifiers survive?
Yes, if they are recognized as text. A column where values are longer than 15 digits always stays text.
Can I merge several page files?
Not inside the viewer — they open as tabs. The easiest way to join pages is the JSON Lines format.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.