Convert JSON to CSV and Excel: Flattening, Headers & Node.js

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 20, 2026

Converting JSON to CSV requires three steps: flatten nested objects to dot-notation keys (user.name→ column header), extract unique headers from all rows (not just the first), and join each row's values with commas — handling values that contain commas or quotes by wrapping in double-quotes and escaping internal quotes. A 10,000-row JSON array with 20 fields converts to CSV in ~15 ms using a streaming approach; loading the full JSON array into memory first adds ~50 MB RAM overhead — use a streaming JSON parser (oboe.js, JSONStream) for files over 100 MB. This guide covers JSON-to-CSV flattening algorithms, handling nested objects and arrays, header normalization, the json2csv and Papa Parse libraries, CSV-to-JSON parsing, and converting JSON to Excel (.xlsx) with ExcelJS and SheetJS.

Flattening Nested JSON for CSV Conversion

Dot-notation flattening converts a nested JSON object into a single-level object whose keys represent the full path to each leaf value. A recursive flatten function is the foundation of any reliable JSON-to-CSV converter — without it, nested objects serialize as [object Object] in CSV cells.

// Recursive flatten function — dot-notation keys
function flattenObject(
  obj: Record<string, unknown>,
  prefix = '',
  result: Record<string, unknown> = {}
): Record<string, unknown> {
  for (const [key, value] of Object.entries(obj)) {
    const flatKey = prefix ? `${prefix}.${key}` : key

    if (value !== null && typeof value === 'object' && !Array.isArray(value)) {
      // Recurse into nested objects
      flattenObject(value as Record<string, unknown>, flatKey, result)
    } else if (Array.isArray(value)) {
      // Option A: flatten array elements to indexed keys
      //   items[0].name, items[1].name — produces wide sparse columns
      value.forEach((item, i) => {
        if (item !== null && typeof item === 'object') {
          flattenObject(item as Record<string, unknown>, `${flatKey}[${i}]`, result)
        } else {
          result[`${flatKey}[${i}]`] = item
        }
      })

      // Option B: JSON-stringify the array as a single cell (comment out Option A above)
      // result[flatKey] = JSON.stringify(value)
    } else {
      result[flatKey] = value
    }
  }
  return result
}

// Example input
const row = {
  id: 1,
  user: {
    name: 'Alice',
    address: { city: 'London', zip: 'EC1A' }
  },
  tags: ['admin', 'editor'],
  score: 98.5
}

// Output (Option A — indexed arrays)
// {
//   id: 1,
//   'user.name': 'Alice',
//   'user.address.city': 'London',
//   'user.address.zip': 'EC1A',
//   'tags[0]': 'admin',
//   'tags[1]': 'editor',
//   score: 98.5
// }

// Converting an array of JSON rows to flat rows
const jsonArray = [
  { id: 1, user: { name: 'Alice' }, score: 98 },
  { id: 2, user: { name: 'Bob', age: 30 }, score: 85 },  // has 'age', row 1 does not
]

const flatRows = jsonArray.map(row => flattenObject(row))
// flatRows[0] = { id: 1, 'user.name': 'Alice', score: 98 }
// flatRows[1] = { id: 2, 'user.name': 'Bob', 'user.age': 30, score: 85 }

Arrays of primitive values (strings, numbers) and arrays of objects require different handling decisions. Indexed key expansion (tags[0], tags[1]) produces clean columns for fixed-length arrays but creates unpredictable column counts for variable-length arrays. JSON-stringifying the array as a single cell value is safer for variable-length arrays but produces cells containing JSON strings that require further parsing. Choose based on whether downstream consumers need to filter on individual array elements.

Header Extraction and Column Ordering

Collecting headers from only the first row is a common bug — sparse rows (rows missing keys present in other rows) will produce empty data in later columns and missing columns entirely if those keys only appear in row 2 or later. The correct approach unions all keys across all rows.

// ── Step 1: Union all keys from all rows ──────────────────────
function extractHeaders(flatRows: Record<string, unknown>[]): string[] {
  const keySet = new Set<string>()
  for (const row of flatRows) {
    for (const key of Object.keys(row)) {
      keySet.add(key)
    }
  }
  return [...keySet]
}

// ── Step 2: Sort headers (choose one strategy) ─────────────────

// Strategy A: preserve insertion order (key appearance order across all rows)
const headers = extractHeaders(flatRows)

// Strategy B: sort alphabetically
const headersAlpha = extractHeaders(flatRows).sort()

// Strategy C: user-specified column order with fallback for extras
function orderedHeaders(
  flatRows: Record<string, unknown>[],
  preferredOrder: string[]
): string[] {
  const allKeys = new Set(extractHeaders(flatRows))
  const ordered = preferredOrder.filter(k => allKeys.has(k))
  // Append any keys not in preferredOrder at the end
  for (const key of allKeys) {
    if (!preferredOrder.includes(key)) ordered.push(key)
  }
  return ordered
}

// Usage:
const headers3 = orderedHeaders(flatRows, ['id', 'user.name', 'score'])
// → ['id', 'user.name', 'score', 'user.age']  (user.age appended at end)

// ── Step 3: Build CSV with sparse-row handling ─────────────────
function rowToCsv(row: Record<string, unknown>, headers: string[]): string {
  return headers
    .map(header => {
      const value = row[header] ?? ''  // missing key → empty cell
      return escapeCsvValue(String(value))
    })
    .join(',')
}

function escapeCsvValue(value: string): string {
  // Must quote if contains comma, double-quote, CR, or LF
  if (/[",
]/.test(value)) {
    return '"' + value.replace(/"/g, '""') + '"'
  }
  return value
}

// Assemble full CSV
function jsonToCsv(
  jsonArray: Record<string, unknown>[],
  preferredOrder?: string[]
): string {
  const flatRows = jsonArray.map(row => flattenObject(row))
  const headers = preferredOrder
    ? orderedHeaders(flatRows, preferredOrder)
    : extractHeaders(flatRows)

  const headerRow = headers.map(escapeCsvValue).join(',')
  const dataRows = flatRows.map(row => rowToCsv(row, headers))

  return [headerRow, ...dataRows].join('\r\n')
}

// Example
const csv = jsonToCsv([
  { id: 1, user: { name: 'Alice' } },
  { id: 2, user: { name: 'Bob', age: 30 } },
])
// id,user.name,user.age
// 1,Alice,
// 2,Bob,30

When consumers downstream require a fixed schema (e.g., uploading to a database table with defined columns), always pass an explicit preferredOrder array and validate that all required columns are present before generating the CSV. Extra columns from unexpected JSON keys are appended at the end by the orderedHeaders function, making the schema deviation visible without discarding data.

CSV Value Escaping and RFC 4180 Compliance

RFC 4180 compliance is essential for Excel compatibility. Three issues trip up most hand-rolled CSV generators: missing quotes around values with embedded commas, not doubling internal double-quotes, and using LF-only line endings that Excel misinterprets as embedded newlines within a cell.

// ── RFC 4180 escaping rules ────────────────────────────────────

// Rule 1: Fields with commas, double-quotes, CR, or LF → wrap in double-quotes
// Rule 2: Internal double-quotes → double them (" becomes "")
// Rule 3: Line ending → CRLF (\r\n) for Windows Excel compatibility

function escapeCsvValue(value: unknown): string {
  if (value === null || value === undefined) return ''
  const str = String(value)

  // Numbers and booleans: do not need quoting
  if (typeof value === 'number' || typeof value === 'boolean') return str

  // Check if quoting is required
  if (/[",
]/.test(str)) {
    return '"' + str.replace(/"/g, '""') + '"'
  }

  return str
}

// Examples of escaping edge cases:
// Input: He said "hello"      → "He said ""hello"""
// Input: London, UK           → "London, UK"
// Input: Line 1
Line 2       → "Line 1
Line 2"
// Input: 3.14                 → 3.14   (no quoting needed)
// Input: true                 → true   (no quoting needed)
// Input: He said "hi", bye   → "He said ""hi"", bye"

// ── UTF-8 BOM for Excel auto-detection ────────────────────────
// Without BOM: Excel on Windows opens UTF-8 CSV as Windows-1252 → garbled chars
// With BOM: Excel auto-detects UTF-8 encoding correctly

const BOM = '\uFEFF'

function jsonToCsvWithBom(jsonArray: Record<string, unknown>[]): string {
  const csv = jsonToCsv(jsonArray)
  return BOM + csv
}

// For file downloads in Node.js (HTTP response):
// res.setHeader('Content-Type', 'text/csv; charset=utf-8')
// res.setHeader('Content-Disposition', 'attachment; filename="data.csv"')
// res.send(BOM + csv)

// ── Numeric vs string type detection ──────────────────────────
// Problem: JSON numbers in CSV may be misinterpreted as text by Excel
// if the column is formatted as text before opening

// Force numeric CSV fields by NOT quoting pure numbers:
function escapeTyped(value: unknown): string {
  if (value === null || value === undefined) return ''
  if (typeof value === 'number' && !isNaN(value)) return String(value)
  if (typeof value === 'boolean') return String(value)

  const str = String(value)

  // If the string looks like a number, DON'T quote it (Excel will treat as number)
  // Exception: leading zeros (zip codes like "01234") — these MUST be quoted
  if (/^-?\d+(\.\d+)?$/.test(str) && !str.startsWith('0')) return str

  if (/[",\r\n]/.test(str)) {
    return '"' + str.replace(/"/g, '""') + '"'
  }

  return str
}

// ── TSV (tab-delimited) and pipe-delimited variants ───────────
// Replace comma with tab or pipe — no quoting needed for tab-delimited
// if values don't contain tabs (TSV rarely has embedded tabs)

function jsonToTsv(jsonArray: Record<string, unknown>[]): string {
  const flatRows = jsonArray.map(row => flattenObject(row))
  const headers = extractHeaders(flatRows)

  const rows = [
    headers.join('\t'),
    ...flatRows.map(row =>
      headers.map(h => String(row[h] ?? '')).join('\t')
    ),
  ]
  return rows.join('\n')
}

Leading-zero strings are a common gotcha: ZIP codes like 01234, product codes like 007, and phone numbers like 0044... must be quoted in CSV to prevent Excel from stripping the leading zero. Always quote strings that match /^0\d+/. For dates, use ISO 8601 format (2026-05-20) in CSV — Excel recognizes it and converts it to a date cell automatically.

json2csv Library: Node.js JSON to CSV

json2csv provides two classes — Parser (synchronous, in-memory) and AsyncParser (streaming) — with rich options for field selection, custom labels, value transforms, and delimiter configuration. Use AsyncParser for large arrays to avoid memory spikes.

import { Parser, AsyncParser } from '@json2csv/node'
import { flatten } from '@json2csv/transforms'
import { Readable } from 'stream'

// ── Basic usage — Parser (synchronous) ────────────────────────
const data = [
  { id: 1, user: { name: 'Alice', age: 28 }, score: 98 },
  { id: 2, user: { name: 'Bob',   age: 34 }, score: 85 },
]

const parser = new Parser({
  fields: [
    { label: 'ID',    value: 'id' },
    { label: 'Name',  value: 'user.name' },
    { label: 'Age',   value: 'user.age', default: 'N/A' },
    { label: 'Score', value: 'score' },
  ],
})

const csv = parser.parse(data)
// "ID","Name","Age","Score"
// 1,"Alice",28,98
// 2,"Bob",34,85

// ── Field value functions — custom formatting ──────────────────
const parserFormatted = new Parser({
  fields: [
    { label: 'ID',    value: 'id' },
    {
      label: 'Full Name',
      // value can be a function receiving the row
      value: (row: { user: { name: string } }) => row.user.name.toUpperCase(),
    },
    {
      label: 'Score %',
      value: (row: { score: number }) => `${row.score}%`,
    },
  ],
})

// ── AsyncParser — streaming for large JSON arrays ──────────────
async function convertLargeJsonToCsv(
  jsonArray: Record<string, unknown>[]
): Promise<string> {
  const opts = {
    fields: ['id', 'name', 'email', 'score'],
    transforms: [flatten({ separator: '.' })],  // flatten nested objects
  }

  const asyncParser = new AsyncParser(opts)

  // Feed data as a readable stream
  const input = Readable.from(JSON.stringify(jsonArray))
  const output = asyncParser.parse(input)

  const chunks: string[] = []
  for await (const chunk of output) {
    chunks.push(chunk)
  }
  return chunks.join('')
}

// ── Built-in flatten transform ────────────────────────────────
// Flattens nested objects up to 5 levels deep by default
const parserWithFlatten = new Parser({
  transforms: [
    flatten({
      separator: '.',   // dot-notation separator (default: '.')
      // objects: true, // flatten objects (default: true)
      // arrays: false, // do NOT flatten arrays (default: false)
    }),
  ],
})

// ── Custom delimiter — TSV and pipe-delimited ──────────────────
const tsvParser = new Parser({ delimiter: '\t', eol: '\n' })
const pipeParser = new Parser({ delimiter: '|', eol: '\n' })

// ── Streaming from a database cursor to HTTP response ─────────
import type { ServerResponse } from 'http'

async function streamJsonToCsvResponse(
  rows: AsyncIterable<Record<string, unknown>>,
  res: ServerResponse
): Promise<void> {
  res.setHeader('Content-Type', 'text/csv; charset=utf-8')
  res.setHeader('Content-Disposition', 'attachment; filename="export.csv"')
  res.write('\uFEFF')  // UTF-8 BOM

  const asyncParser = new AsyncParser({ fields: ['id', 'name', 'email'] })
  const input = Readable.from(rows)

  for await (const chunk of asyncParser.parse(input)) {
    res.write(chunk)
  }
  res.end()
}

The default option in field definitions is critical for sparse JSON — if a row is missing a field, json2csv uses the default value instead of leaving the cell empty. Always set default: '' explicitly (or a meaningful sentinel like 'N/A') rather than relying on undefined behavior. The flatten transform from @json2csv/transformshandles nested object flattening automatically — you do not need a separate pre-processing step when using json2csv.

Papa Parse: Browser and Node.js CSV Parsing

Papa Parse is the reference library for bidirectional CSV processing in JavaScript. Papa.unparse() converts JSON to CSV; Papa.parse() converts CSV back to JSON with robust handling of quoted fields, embedded newlines, and BOM detection. Web Worker mode keeps large conversions off the main thread.

import Papa from 'papaparse'

// ── JSON to CSV with Papa.unparse() ──────────────────────────
const jsonData = [
  { id: 1, name: 'Alice', score: 98 },
  { id: 2, name: 'Bob',   score: 85 },
]

const csv = Papa.unparse(jsonData)
// id,name,score
// 1,Alice,98
// 2,Bob,85

// With options: custom delimiter, no headers, quote all fields
const csvOptions = Papa.unparse(jsonData, {
  delimiter: '\t',    // TSV
  header: true,        // include header row (default: true)
  quotes: true,        // quote ALL fields (default: false — only quote when needed)
  newline: '\r\n',    // CRLF for Windows Excel
})

// ── CSV to JSON with Papa.parse() ────────────────────────────
const csvString = `id,name,score\n1,Alice,98\n2,Bob,85`

const result = Papa.parse(csvString, {
  header: true,         // use first row as object keys
  dynamicTyping: true,  // "98" → 98, "true" → true
  skipEmptyLines: true, // skip blank rows at end
})

// result.data = [{ id: 1, name: 'Alice', score: 98 }, ...]
// result.errors = []  (array of parse errors)
// result.meta.fields = ['id', 'name', 'score']

// ── Parsing without headers — access by array index ───────────
const resultNoHeader = Papa.parse(csvString, {
  header: false,
  dynamicTyping: true,
})
// result.data = [['id','name','score'], [1,'Alice',98], [2,'Bob',85]]

// ── Streaming large CSV — step callback (Node.js + browser) ───
Papa.parse(largeCsvString, {
  header: true,
  dynamicTyping: true,
  step: (row: { data: Record<string, unknown>; errors: unknown[] }) => {
    // Called once per parsed row — memory-efficient
    processRow(row.data)
  },
  complete: () => {
    console.log('CSV parsing complete')
  },
  error: (err: Error) => {
    console.error('Parse error:', err)
  },
})

// ── Web Worker mode — browser only (non-blocking) ─────────────
Papa.parse(largeCsvString, {
  header: true,
  worker: true,   // runs in a Web Worker — does not block the UI thread
  step: (row: { data: Record<string, unknown> }) => updateUI(row.data),
  complete: () => hideSpinner(),
})

// ── Parsing a File object in the browser ──────────────────────
function parseCsvFile(file: File): Promise<Record<string, unknown>[]> {
  return new Promise((resolve, reject) => {
    Papa.parse(file, {
      header: true,
      dynamicTyping: true,
      skipEmptyLines: true,
      complete: (results: { data: Record<string, unknown>[] }) => resolve(results.data),
      error: reject,
    })
  })
}

// ── Streaming from Node.js ReadStream ─────────────────────────
import { createReadStream } from 'fs'

Papa.parse(createReadStream('/data/large.csv'), {
  header: true,
  step: (row: { data: Record<string, unknown> }) => insertToDb(row.data),
  complete: () => console.log('Import done'),
})

Papa Parse handles edge cases that manual CSV parsers miss: quoted fields containing embedded newlines (a single CSV field that spans multiple lines), fields containing the delimiter character, UTF-8 BOM auto-stripping, and Windows/Unix/Mac line ending detection. When dynamicTyping is enabled, Papa Parse converts numeric strings but also converts "true" and "false"to booleans — disable it if you need all values as strings.

ExcelJS: JSON to Excel (.xlsx) with Formatting

ExcelJS creates .xlsx files with full formatting support: column widths, cell styles, number formats, header row styling, and formulas. It is the best choice when the Excel output requires visual formatting beyond raw data — such as colored headers, currency cells, or date formatting.

import ExcelJS from 'exceljs'

interface SalesRow {
  id: number
  name: string
  revenue: number
  date: Date
  margin: number
}

async function jsonToExcel(data: SalesRow[]): Promise<Buffer> {
  const workbook = new ExcelJS.Workbook()
  workbook.creator = 'Jsonic'
  workbook.created = new Date()

  const worksheet = workbook.addWorksheet('Sales Data')

  // ── Define columns (adds header row automatically) ────────────
  worksheet.columns = [
    { header: 'ID',      key: 'id',      width: 8 },
    { header: 'Name',    key: 'name',    width: 24 },
    { header: 'Revenue', key: 'revenue', width: 14 },
    { header: 'Date',    key: 'date',    width: 14 },
    { header: 'Margin',  key: 'margin',  width: 10 },
  ]

  // ── Style the header row ──────────────────────────────────────
  const headerRow = worksheet.getRow(1)
  headerRow.font = { bold: true, color: { argb: 'FFFFFFFF' } }
  headerRow.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FF4472C4' },   // blue background
  }
  headerRow.alignment = { vertical: 'middle', horizontal: 'center' }
  headerRow.height = 20
  headerRow.commit()

  // ── Freeze the header row ─────────────────────────────────────
  worksheet.views = [{ state: 'frozen', ySplit: 1, activeCell: 'A2' }]

  // ── Add data rows ─────────────────────────────────────────────
  worksheet.addRows(data)   // addRows is faster than addRow() in a loop

  // ── Apply number formats to data rows ─────────────────────────
  worksheet.getColumn('revenue').numFmt = '$ #,##0.00'
  worksheet.getColumn('date').numFmt    = 'DD/MM/YYYY'
  worksheet.getColumn('margin').numFmt  = '0.00%'

  // ── Auto-fit column widths based on content ───────────────────
  worksheet.columns.forEach(col => {
    let maxLength = (col.header as string)?.length ?? 10
    col.eachCell?.({ includeEmpty: false }, cell => {
      const cellLength = cell.value ? String(cell.value).length : 0
      if (cellLength > maxLength) maxLength = cellLength
    })
    col.width = Math.min(maxLength + 2, 60)  // cap at 60 chars
  })

  // ── Alternating row colors ─────────────────────────────────────
  worksheet.eachRow((row, rowNumber) => {
    if (rowNumber === 1) return  // skip header
    if (rowNumber % 2 === 0) {
      row.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFF2F2F2' },  // light grey
      }
    }
    row.commit()
  })

  // ── Add a SUM formula in the totals row ───────────────────────
  const lastDataRow = data.length + 1
  const totalRow = worksheet.addRow({
    id: '', name: 'TOTAL', revenue: { formula: `SUM(C2:C${lastDataRow})` }
  })
  totalRow.font = { bold: true }
  totalRow.commit()

  // ── Write to Buffer (for HTTP response) ──────────────────────
  return workbook.xlsx.writeBuffer() as Promise<Buffer>
}

// ── HTTP download handler (Next.js Route Handler) ─────────────
// app/api/export/route.ts
export async function GET() {
  const data = await fetchSalesData()
  const buffer = await jsonToExcel(data)

  return new Response(buffer, {
    headers: {
      'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'Content-Disposition': 'attachment; filename="sales-export.xlsx"',
    },
  })
}

ExcelJS's addRows() is significantly faster than calling addRow() in a loop for large datasets — use it when adding more than a few hundred rows. Number formats applied to columns (not individual cells) are inherited by all cells in that column, including rows added after the format is set. For very large exports (100,000+ rows), consider using the streaming writer: workbook.xlsx.createWriteStream(filePath) which writes directly to disk without loading the entire workbook into memory.

SheetJS (xlsx): Round-Trip JSON ↔ Spreadsheet

SheetJS (the xlsxnpm package) supports reading and writing 20+ spreadsheet formats including .xlsx, .xls (legacy Excel), .ods (LibreOffice), and .csv — making it the best choice for round-trip JSON ↔ spreadsheet pipelines and for reading legacy .xls files that ExcelJS cannot handle.

import * as XLSX from 'xlsx'

// ── JSON array to worksheet ────────────────────────────────────
const jsonData = [
  { id: 1, name: 'Alice', score: 98, date: '2026-05-20' },
  { id: 2, name: 'Bob',   score: 85, date: '2026-05-19' },
]

// json_to_sheet — keys become headers, values become cells
const worksheet = XLSX.utils.json_to_sheet(jsonData)
// Override header order by passing a header array:
const worksheetOrdered = XLSX.utils.json_to_sheet(jsonData, {
  header: ['id', 'name', 'score', 'date'],
  // skipHeader: true  — omit header row (use for aoa_to_sheet instead)
})

// ── Array-of-arrays (AOA) format ───────────────────────────────
const aoa = [
  ['ID', 'Name', 'Score'],   // header row
  [1, 'Alice', 98],
  [2, 'Bob', 85],
]
const worksheetAoa = XLSX.utils.aoa_to_sheet(aoa)

// ── Assemble workbook with multiple sheets ─────────────────────
const workbook = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(workbook, worksheet,    'Sales')
XLSX.utils.book_append_sheet(workbook, worksheetAoa, 'Summary')

// ── Write to Buffer ────────────────────────────────────────────
const xlsxBuffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' })
const xlsBuffer  = XLSX.write(workbook, { type: 'buffer', bookType: 'xls' })  // legacy
const csvBuffer  = XLSX.write(workbook, { type: 'string', bookType: 'csv' })  // CSV from JSON

// ── Read .xlsx or .xls back to JSON (sheet_to_json) ───────────
function excelToJson(buffer: Buffer): Record<string, unknown>[] {
  const wb = XLSX.read(buffer, { type: 'buffer', cellDates: true })
  const firstSheet = wb.SheetNames[0]
  const ws = wb.Sheets[firstSheet]

  return XLSX.utils.sheet_to_json(ws, {
    header: 1,        // use: 1 = AOA, omit = use first row as keys
    defval: '',       // default value for empty cells (avoid undefined)
    raw: false,       // false = format dates as strings (requires cellDates: true)
  }) as Record<string, unknown>[]
}

// ── sheet_to_json with header array ───────────────────────────
// When header: 1 is used, output is array-of-arrays
const aoa2 = XLSX.utils.sheet_to_json(worksheet, { header: 1 })
// [['id','name','score'], [1,'Alice',98], ...]

// When header is omitted, first row becomes object keys
const objects = XLSX.utils.sheet_to_json(worksheet)
// [{ id: 1, name: 'Alice', score: 98 }, ...]

// ── Generate CSV from JSON without Excel dependency ───────────
// SheetJS can write CSV directly — lighter than loading ExcelJS for CSV-only
function jsonToCsvViaSheetJS(data: Record<string, unknown>[]): string {
  const ws = XLSX.utils.json_to_sheet(data)
  return XLSX.utils.sheet_to_csv(ws, { FS: ',', RS: '\r\n' })
}

// ── Reading a legacy .xls file (ExcelJS cannot read .xls) ─────
async function readXlsFile(filePath: string): Promise<Record<string, unknown>[][]> {
  const workbook = XLSX.readFile(filePath, { cellDates: true })
  return workbook.SheetNames.map(name =>
    XLSX.utils.sheet_to_json(workbook.Sheets[name], { defval: '' })
  ) as Record<string, unknown>[][]
}

SheetJS's cellDates: true option is essential for reading Excel files with date columns — without it, Excel serial date numbers (e.g., 46201 for 2026-05-20) appear as raw numbers instead of JavaScript Date objects. When writing JSON to CSV via SheetJS (sheet_to_csv), it handles RFC 4180 escaping automatically and is a lightweight alternative to importing Papa Parse when CSV generation is the only requirement. For generating CSV in the browser where bundle size matters, xlsx/dist/xlsx.mini.min.js is a stripped-down build that omits legacy format support.

Key Terms

flattening

The process of converting a nested JSON object into a single-level object by concatenating parent and child key names with a separator (typically a dot). For example, { user: { name: "Alice" } } flattens to { "user.name": "Alice" }. Flattening is a prerequisite for CSV conversion because CSV is a two-dimensional format (rows and columns) that cannot represent hierarchy natively. Arrays within JSON objects require an additional decision: flatten to indexed keys (tags[0]) or JSON-stringify as a single cell.

RFC 4180

The IETF informational standard (not a full standard) that defines the CSV format. Key rules: each record is on a separate line terminated by CRLF; the last record may or may not have an end-of-line; an optional header line appears as the first line; fields containing special characters (commas, double-quotes, or newlines) must be enclosed in double-quotes; double-quote characters within quoted fields must be escaped by preceding them with another double-quote. RFC 4180 does not define encoding — UTF-8 with a BOM is the de facto standard for Excel compatibility.

UTF-8 BOM

A byte order mark () prepended to a UTF-8 encoded text file. The BOM is not required by the UTF-8 specification and has no meaning in a UTF-8 context (UTF-8 has no byte order), but Microsoft Excel on Windows uses its presence to auto-detect UTF-8 encoding when opening CSV files directly. Without the BOM, Excel on Windows opens UTF-8 CSV files as Windows-1252 (Western European encoding), corrupting non-ASCII characters like accented letters, currency symbols, and CJK characters. Add the BOM by prepending  to the CSV string before writing to disk or sending as an HTTP response.

TSV

Tab-Separated Values — a variant of CSV that uses the tab character (\t) as the field delimiter instead of a comma. TSV avoids the need to quote fields containing commas, making it simpler to generate and parse when field values frequently contain commas (e.g., addresses, descriptions). However, fields containing tab characters must still be escaped (typically by replacing tabs with spaces or encoding them). TSV files use the .tsv extension and the MIME type text/tab-separated-values. Excel opens TSV files directly. json2csv supports TSV via the delimiter: '\t' option; Papa Parse supports it via the same option.

workbook

The top-level container object in Excel file formats (.xlsx, .xls) that holds one or more worksheets (tabs). In ExcelJS, a workbook is created with new ExcelJS.Workbook() and worksheets are added with workbook.addWorksheet(name). In SheetJS, a workbook is created with XLSX.utils.book_new() and sheets are attached with XLSX.utils.book_append_sheet(). A workbook can contain multiple worksheets with different schemas — useful for exporting related JSON datasets (e.g., orders and line items) into a single multi-tab Excel file.

sparse row

A JSON object in an array that is missing keys present in other objects. For example, in an array where most objects have an age field but some do not, the objects without age are sparse rows. In CSV conversion, sparse rows produce empty cells for the missing columns. The header extraction step must collect all unique keys across ALL rows (not just the first row) to ensure sparse row columns are included. If headers are extracted from only the first row and it happens to be a sparse row, data from those missing columns in other rows will be silently discarded.

FAQ