Excel to JSON: Differences, Edge Cases & Pitfalls
Turn Excel rows into a JSON array with header mapping and explicit typing for numbers, blanks and dates.
A worksheet becomes an array of objects, with row one as keys and each row as one object. The traps live in type inference: 001 turns into 1, IDs past 15 digits get float-damaged, and empty cells look identical to missing fields. Pin the column formats first.
Format Differences
| Aspect | Excel (grid) | JSON (object array) |
|---|---|---|
| Structure | Two-dimensional grid (rows x columns) | Array of objects, nesting is native |
| Keys | First row supplies column names | Column names become keys; names with spaces need cleaning |
| Types | Cell type plus display format | Inferred number/boolean/string, no notion of formatting |
| Empty values | Empty cell | null or "" — different semantics, agree on one |
| Nesting | Requires extra sheets or repeated columns | Nested objects and arrays directly |
| Arrays | Multiple rows or a delimiter convention | Native arrays with explicit meaning |
| Readability | Good for humans to view and enter | Good for programs and APIs |
| Size | Includes styles and structure, larger | Data only, compact |
Edge Cases
- A column mixing text and numbers ("12" and 12) yields inconsistent types and breaks downstream validation — normalise the column first.
- Column names with spaces or special characters are not valid identifiers; transliterate, underscore them or quote them.
- Multi-row headers (merged cells) mean row one is not the real header — flatten the header first.
- Blank rows are usually dropped; if they carried separation meaning it is lost entirely in JSON.
Common Pitfalls
- Leading-zero identifiers become numbers, so 001 arrives as 1 and the write-back to the database is wrong.
- Only the active worksheet is exported; the rest disappears silently.
- Duplicate column names (two columns called "note") mean the later one wins — JSON objects cannot hold duplicate keys.
- Numbers above 2^53 lose precision when parsed on the JS side; send big IDs as strings.
Related Tools
- Excel to JSONConvert Excel files (xlsx, xls, csv) to JSON format. Supports nested headers, sheet selection and key mapping. Choose the sheet and header row and keep numbers.
- Data Format ConverterConvert between JSON, YAML, TOML, XML and CSV in one place with a single paste and instant preview for seamless multi-format config and data conversion.
- Excel to CSVConvert Excel files to CSV with custom delimiter options and batch sheet conversion. Export data for databases or scripts via SheetJS, in the browser.
- Excel to HTMLConvert Excel to clean, styled HTML tables with sheet selection and responsive output. Pick a sheet keep merged cells Choose which sheet to export Pick.