Last Wednesday afternoon a colleague from operations dropped an API response in front of me and asked me to turn it into a spreadsheet. It was the order list endpoint, with an outer wrapper of {"code":0,"msg":"ok","data":{"total":28413,"list":[...]}}, 500 orders inside list, each with order_no, pay_amount, create_time, buyer and items. My first instinct was select all, copy, paste into Excel: everything landed in column A, the amounts became 1.00002E+15, the date column showed a row of #, and over an hour was gone. In hindsight the problem was not that Excel is hard to use; I did not understand how JSON maps onto a table structurally, and I did not control the field types.
1. JSON and a table are two drawings of the same thing
The mapping is narrow: each item in an array of objects is a row, the keys in the object are the column names, and the values fall into cells. The reverse holds too: a table with the header row removed is an array of objects.
[
{"order_no":"20240915001","pay_amount":128.5,"city":"Hangzhou"},
{"order_no":"20240915002","pay_amount":219,"city":"Beijing"}
]
order_no | pay_amount | city
20240915001 | 128.5 | Hangzhou
20240915002 | 219 | BeijingSo the first step before touching anything is deciding which array is the data itself. Converting the whole response yields one row with columns code, msg and data, which looks plausible and is useless; take data.list on its own. The second kind of trouble is uneven fields: 39 of those 500 records had no coupon_amount (no coupon used), so the header has to be the union of all object keys with blanks where a key is missing; define columns from the first record's keys instead and you silently lose two columns with no warning at all.
2. What really takes time is nesting and one-to-many
buyer is an object, and flattening it into buyer.nickname and buyer.phone loses nothing while reading more clearly. items is an array, and one order held up to 12 SKUs; cramming it into one cell only produces a JSON string and makes amount totals impossible. For this I split into two tables: orders with one row per order and order_items with one row per item, joined on order_no, and the business side matches them with a pivot table or VLOOKUP in Excel.
Align the key names before splitting. Some APIs write create_time in one place and createTime in another, so the header gains a duplicate column out of nowhere, and nobody in the report ever asks why half of it is empty.
3. The order I followed that day
- Validate the structure before doing anything. Push the whole response through JSON Formatter first; when the syntax is invalid it gives you the line and column of the error, which is faster than counting brackets. A common case is an API embedding one JSON document as a string inside another, so the double escaping needs one layer peeled off.
- Copy
data.listout on its own and paste it into JSON to Excel to see the rendered table. The preview is the key step: which columns were actually extracted and whether to keep the header row are all visible at a glance, so you do not redo the work after exporting. - Copy the table straight from the preview into Excel, or download the CSV and open it. For the business side I save a separate xlsx and leave the original CSV untouched so it can be recalculated later.
- The reverse case came when the business side edited the details in Excel and sent the file back, and it had to become a JSON array for developers to load. What you copy from Excel is tab-separated text, Excel to JSON accepts that paste, and numbers and booleans are output by type, saving the manual quote fixing.
The sheet held phone numbers and shipping addresses, so I made a point of confirming that this kind of conversion happens locally in the browser and the data is not sent to a server. Even so, the middle four digits of phone should be replaced before anything leaves the building; that CSV was forwarded three times in a group chat afterwards.
4. Once the table exists, guard against these five traps in Excel
| Symptom | Cause | What to do |
|---|---|---|
| Order numbers show as 1.00002E+15 | Excel keeps only 15 significant digits, so the tail of an 18-digit number is zeroed out | Import that column as text; if it already turned into scientific notation you must re-import, because the erased digits are gone |
| A Chinese column turns into garbage like "ä¸æ–‡" | The CSV has no BOM, so Excel decodes UTF-8 bytes with the local ANSI code page | Export the copy with a UTF-8 BOM, or set 65001 by hand in the From Text import dialog |
| The date column shows ###### | The column is too narrow, or the value is still a string underneath | Double-click the column border to autofit; for string dates, run Text to Columns once with YYYY-MM-DD |
| One comma in a note splits a row into two columns | The field contains the delimiter but is not wrapped in double quotes | Quote any field containing a comma or line break, and double the quotes inside it |
| An amount of 1,280.50 turns into text or a date | The thousands separator made Excel guess the wrong type | Put only the bare number in the CSV and let Excel cell formatting add the separators |
Only the first of these is irreversible. The two hundred-odd order numbers I ruined that day only came back after I re-imported everything.
5. Common questions
Q: I pasted it in and only got a few hundred rows, but the API says more than twenty thousand?
That is almost always pagination. total is 28413 while the length of list is 500, which means you are holding the first page only. Page through everything before converting, and do not hand in one page as the full set.
Q: Should I keep CSV or xlsx?
CSV is plain text, a single table, no styling, readable by any language, small, and good for feeding back into a program; xlsx supports multiple sheets, formulas and formatting, and suits delivery to the business side. My habit is to archive the CSV and save an extra xlsx for the people who read it.
Q: The exported column order is not what I expected?
Column order follows the first appearance of each field in the data, not the order you want. To fix the order, select the fields before converting and keep only the keys you need.
Q: Can I still do this with a very large dataset?
Pages like this read the text into the browser and render it as a table, so a few hundred thousand rows are enough to make rendering slow. My dividing line is a few thousand rows here, and tens of thousands or more go through the backend writing a file directly, or are fetched page by page and merged.
Q: How do I check that the conversion lost nothing?
Check two numbers: the length of the source array against the table row count, and the sum of pay_amount against the total field the API provides separately. Only when both match is the conversion done; spot-checking a few rows will not reveal a missing column.
Looking back at that afternoon, what actually embarrassed me was not failing to convert a table but finishing a monthly report on 500 records and being told the next day by finance that the total was off by 300,000. The conversion itself is ten minutes of work; the valuable part is the order: confirm which array is the data, confirm you have the full set, and only then fix columns and types, especially for a column like the order number that never comes back once it has been treated as a number.