Spreadsheet Export Formats Compared
Exporting the same sheet to different formats changes more than the extension: some preserve data types, some degrade everything to strings, some open straight in Excel, some suit machines better. This page compares four common export formats and lists the CSV escaping rules that most often go wrong.
Four Export Formats
| Format | Structure | Types | Best For | Watch Out |
|---|---|---|---|---|
| JSON | Array of objects keyed by header | Preserves numbers, booleans and null | API wiring, front-end rendering, config import | Duplicate headers overwrite; merged cells lose structure |
| CSV | Plain text table, comma separated | Everything degrades to strings | Database import, opening in Excel, cross-platform exchange | No types and no encoding declaration; CJK often mojibakes |
| HTML | table / tr / td markup | Preserves text and merged structure | Embedding in pages, email bodies, rich-text paste | Large output; numbers remain text |
| 纯文本 | Tab or pipe delimited | Everything is a string | Logs, terminal paste, Markdown tables | Fields containing the delimiter must be escaped or columns shift |
CSV Escaping Rules
Whenever a field contains a comma, newline or double quote it must be wrapped in double quotes, and inner quotes are written as two consecutive quotes.
| Case | Input | Written in CSV | Note |
|---|---|---|---|
| Plain field | apple | apple | Emitted as is |
| Contains a comma | a,b | "a,b" | Must be wrapped in double quotes |
| Contains a newline | line1 line2 | "line1 line2" | Newlines are allowed inside quotes |
| Contains a double quote | say "hi" | "say ""hi""" | Inner quotes are doubled |
| Leading/trailing spaces | pad | " pad " | Quote it to preserve the spaces |
Frequently Asked Questions
Should CSV use commas or tabs as the delimiter?
It depends on the data. Commas are the RFC 4180 standard with the best compatibility, but if the data itself contains commas — common in addresses, amounts and person names — fields must be wrapped in double quotes, and getting that wrong shifts every column. Tab-separated TSV is far less likely to appear inside the text, which suits comma-heavy data, at the cost of weaker default support. A safe rule: prefer commas with strict quote escaping; if you control the downstream parser, TSV removes a great deal of escaping pain.
Why does Chinese text mojibake when opening a CSV in Excel?
CSV declares no encoding, so Excel guesses using the system default. Chinese Windows defaults to GBK, while most exporters write UTF-8, and the mismatch produces mojibake. Three fixes: write a UTF-8 BOM, which tells Excel to parse as UTF-8 and is the least effort; have the recipient use Data → From Text and pick UTF-8 explicitly; or save as GBK, which sacrifices compatibility elsewhere.
Why did my dates turn into numbers after export?
This is how Excel stores dates internally: a date is just a serial number counted from 1900-01-01, and the displayed form is entirely a matter of cell formatting. Exporting to CSV drops the formatting, leaving only the serial. Format the date column as text before exporting, or convert it with the TEXT function into a string like yyyy-mm-dd. When exporting JSON, the tool should recognise date formats and emit ISO 8601 strings rather than the raw internal number.
Exporting a large sheet is slow or fails. What should I do?
The bottleneck is usually browser memory rather than the computation. A 100,000-row sheet parsed into an array of objects can occupy several times the original file size, and string concatenation pushes it over the edge. Practical steps: trim columns and rows to what the business actually needs instead of exporting everything; move sheets beyond a few tens of thousands of rows to a server-side script; and if the consumer is another program, choose CSV over JSON or HTML, since it is an order of magnitude lighter in both memory and bytes.