Converting JSON to and from CSV and Tabular Data

Bottom line: flatten with dotted keys and JSON-stringified arrays when people will read the export, split into a parent table plus related tables when loading a database, and expand arrays into extra rows only for pivots; when restoring, convert by explicit column definitions instead of global inference.

Flattening a tree into a table always loses hierarchy; restoring a table into a tree always guesses types. Both directions need their rules stated up front.

1. What CSV actually specifies

RFC 4180 runs only a few pages and specifies less than many people assume:

1. Each record occupies one line, with fields separated by commas;
2. The line terminator is CRLF, though in practice \n is also accepted;
3. A field containing a comma, a double quote, or a newline must be wrapped in double quotes;
4. Double quotes inside a field are written as two double quotes "";
5. The first line may be a header;
6. No character encoding is specified, and neither are types.

复制id,name,remark
1,张三,"喜欢 JSON，讨厌尾逗号"
2,李四,"他说：""这行有引号"""
3,王五,"多行
备注"

Pay attention to rule 3: fields containing newlines are legal, so a naive parser that splits on \n and then on , is guaranteed to be wrong.

2. JSON → CSV: decide the structure first

There are only three strategies for flattening a tree structure into a two-dimensional table:

Strategy 1: flatten fields (the most common)

复制{ "id": 1, "user": { "name": "张三", "city": "杭州" }, "tags": ["a", "b"] }

复制id,user.name,user.city,tags
1,张三,杭州,"[""a"",""b""]"

• Join nested object keys with .;
• Arrays cannot be expressed as columns, so JSON.stringify them straight into the cell (which is still a legal CSV field);
• Downside: spreadsheet tools cannot filter the array, so a second parse is required.

Strategy 2: expand the array into multiple rows

复制id,user.name,tag
1,张三,a
1,张三,b

This suits one-to-many relationships where downstream consumers aggregate per element. The downside is duplicated parent fields and a ballooning row count.

Strategy 3: split into multiple tables

A main table plus related tables joined by an id — the cleanest approach, but it goes beyond "a single export" and looks more like data modeling.

Which one? Use strategy 1 for exports meant for humans, strategy 3 for loading into a database, and strategy 2 for pivoting data.

3. CSV → JSON: how types get guessed

CSV has no types, so every step of restoration is a guess:

Suggested default: treat everything as a string, and let a schema or an explicit column definition decide which columns become numbers or booleans. "Smart inference" is delightful when exporting and a source of incidents when importing.

复制type ColumnType = 'string' | 'number' | 'boolean' | 'json';

/**
 * Converts CSV rows into objects according to the column definitions.
 * @param rows The raw row array; the first row is the header
 * @param columnTypes Mapping from column name to type
 * @returns The converted array of objects
 */
function rowsToRecords(
  rows: string[][],
  columnTypes: Record<string, ColumnType>,
): Array<Record<string, unknown>> {
  const [headerRow, ...dataRows] = rows;

  return dataRows.map((dataRow) => {
    const record: Record<string, unknown> = {};

    headerRow.forEach((columnName, columnIndex) => {
      const rawValue = dataRow[columnIndex] ?? '';
      const columnType = columnTypes[columnName] ?? 'string';

      if (columnType === 'number') {
        const numericValue = Number(rawValue);
        record[columnName] = Number.isFinite(numericValue) ? numericValue : null;
        return;
      }
      if (columnType === 'boolean') {
        record[columnName] = rawValue === 'true' || rawValue === '1';
        return;
      }
      if (columnType === 'json') {
        try {
          record[columnName] = JSON.parse(rawValue);
        } catch {
          record[columnName] = rawValue;
        }
        return;
      }
      record[columnName] = rawValue;
    });

    return record;
  });
}

4. Three pitfalls around Excel

4.1 Encoding

When Excel opens a UTF-8 CSV without a BOM, Chinese text turns into mojibake. Adding a BOM (\uFEFF) on export is the cheapest compatibility fix:

复制const bom = '\uFEFF';
const csvContent = `${bom}${headerLine}\n${bodyLines.join('\n')}`;

4.2 Long numbers turning into scientific notation

An 18-digit national ID or a 19-digit order number shows up in Excel as 1.23457E+17, and the value itself has already been truncated. Prefixing the field with a tab or wrapping it as a formula (="1234...") preserves text formatting, but turns the cell into a formula. The safer approach is to tell users to "use the import feature and specify the column as text", or to offer a .xlsx alongside the export.

4.3 Delimiters affected by locale

Some European locales default to the semicolon ; as the separator. When distributing across regions, either pin CSV to commas and say so, or offer a TSV (tab-separated) version — TSV is more locale-tolerant.

5. Handling large files

Do not load CSVs with more than a hundred thousand rows into memory in one go:

复制import { createReadStream } from 'node:fs';
import { parse } from 'csv-parse';

const parser = createReadStream('orders.csv').pipe(
  parse({ columns: true, skipEmptyLines: true, bom: true }),
);

for await (const record of parser) {
  // Process one record at a time; memory usage is independent of file size
}

The export direction works the same way: generate and write to a stream as you go, rather than concatenating one giant string.

6. Round-trip consistency self-check

A CSV round trip (JSON → CSV → JSON) is almost never exactly identical, but you can define which losses are acceptable:

Putting this table into tests is far more effective than a verbal agreement.

7. Checklist

• [ ] The "one row is one record" semantics are stated explicitly and reflected in the export filename;
• [ ] Every column that may lose precision is exported as a string;
• [ ] Chinese CSVs include a BOM, or the encoding is stated explicitly;
• [ ] Fields containing commas, quotes, or newlines are escaped correctly (use a library, do not concatenate strings yourself);
• [ ] Large files go through streaming reads and writes;
• [ ] Imports convert types per column definition, never through global smart inference.

FAQ

How do I choose between flattening, expanding rows, and splitting tables?

Flatten for exports meant for humans: join nested keys with . and JSON.stringify arrays into the cell, at the cost of not being able to filter those arrays in a spreadsheet. Split into a parent table plus related tables when the target is a database. Expand arrays into extra rows when downstream consumers aggregate per element, accepting duplicated parent fields.

Why shouldn't a CSV import infer types automatically?

RFC 4180 specifies delimiters and escaping but no types, so every restore step is a guess: 00123 loses its leading zeros, 1.10 becomes 1.1, and 1e5 becomes 100000. Treat everything as a string by default and let a schema or explicit column definition decide which columns become numbers or booleans.

Should I expect a CSV round trip to reproduce the original JSON exactly?

Almost never. Define the acceptable losses instead: identical field count, identical string values including leading zeros, numeric types decided by the column definition, nested objects recoverable through dotted paths, arrays visible after serialization, and empty values normalized to null or the empty string — pick exactly one.