Excel to JSON: Dates Become Numbers / Chinese Garbled — Fix

Symptom: after converting, dates become a number

You export Excel to JSON and the date that should be 2023-01-01 becomes 44927; Chinese is occasionally garbled; empty cells just disappear. The tool isn't broken — it's how Excel stores data.

Pit 1: dates become numbers (Excel serial)

Excel internally doesn't store a "date" — only a number: days counted from the base date 1899-12-30. So 44927 means 44927 days after that base, i.e. around 2023-01-01.

If conversion doesn't special-case it, you get the day count, not a date string. Restore it properly:

// Excel serial -> JS Date (use UTC to avoid timezone shift)
function excelDateToISO(serial) {
  const utc = Math.round((serial - 25569) * 86400 * 1000);
  return new Date(utc).toISOString().slice(0, 10);
}
console.log(excelDateToISO(44927)); // 2023-01-01

Pit 2: Chinese garbled (encoding)

If any link in the export chain used a non-UTF-8 encoding (e.g. reading GBK CSV as UTF-8), Chinese garbles. Standardizing on UTF-8 for both export and read fixes it.

Pit 3: empty cells / headers

• Empty cells: many tools just omit the field, making each row's keys inconsistent; if you need to keep them, explicitly output null for empties;
• Headers: the first row is usually column names and should become object keys; if the file has no header, specify one or number columns sequentially.

Pit 4: silent precision loss

Excel numbers carry only 15 significant digits. Pure-numeric strings longer than 15 digits — ID card numbers, bank card numbers, long order references — have every digit from the 16th onward turned into 0 the moment the file opens in Excel. This is not a converter problem: the file itself is already corrupted. Prevention: format such columns as text in Excel before entry. Already-corrupted values are unrecoverable; re-export from the source.

How to convert correctly with a tool

Open the Excel to JSON tool on ToolVault:

1. Upload or drop an .xlsx;
2. The tool restores date serials to readable dates and handles Chinese as UTF-8 without garbling;
3. Choose output shape (array of objects / by column); empty-value policy is configurable;
4. Copy JSON in one click; pair with CSV to JSON for plain-text tables.

The cell-level data corruption Excel causes silently

Excel is a spreadsheet that guesses, and its guesses corrupt data on open (before any export): leading-zero IDs become numbers (00123 → 123), long numbers become scientific notation (1234567890123 → 1.23457E+12 — irreversible precision loss on phone numbers and card numbers), dates become serial numbers or shift across locales (03/04 read as March 4 or April 3), and gene names literally become dates (SEPT2 → September-2, a famous bioinformatics casualty). The export-to-JSON step inherits all of it. Prevention at the source: format ID/phone columns as Text before typing, import CSVs via Data → From Text (which asks about types) rather than double-clicking the file. If the damage is done and the file was saved, the precision is gone — recover from the original source, not the xlsx.

Structuring the output: rows vs records

Flat row-arrays ([["Name","Age"],["Ann",30]]) preserve column order but lose semantics; object-records ([{"Name":"Ann","Age":30}]) are what APIs want but assume a clean header row. Real sheets complicate both: merged header cells (a two-row header with "Q1" spanning four month columns — flatten to Q1-Jan keys), repeated header blocks on one sheet (three tables stacked vertically), and metadata rows above the actual header (title, generated-date). The converter question that matters: can it skip N rows, take row K as headers, and handle merged cells — or are you pre-cleaning every sheet by hand? Our Excel converter handles the common shapes; the pathological ones deserve a scripted pipeline, not interactive conversion.

Multi-sheet and cross-references

Workbook-to-JSON strategies: one JSON per sheet (file-per-sheet, preserves isolation — the default right answer), nested under sheet names (single file, convenient, ambiguous sheet-name collisions), or relational (resolve cross-sheet VLOOKUP-style references into nested objects — powerful, fragile, only for controlled workbooks). The honest boundary: spreadsheets with formulas referencing other sheets are programs, not data; exporting their computed values freezes one run's results. If the workbook is a living system, the migration path is extracting the data model into a real database, with the JSON export as the one-time bridge.

Header hygiene before conversion

Spend two minutes in the sheet so JSON keys stay stable:

1. One header row, no merged title above it (or tell the converter which row is the header)
2. Unique column names — duplicate “Name” columns become colliding object keys
3. No blank header cells mixed into the middle of the table
4. Trim trailing empty columns that Excel leaves after deletes

Then convert with the Excel to JSON tool and open the result in the JSON formatter to spot null-heavy columns early.

FAQ

Why is my 44927 off by one day?

Excel has the famous "1900 leap year bug" — it treats 1900 as a leap year, over-counting one day. The 25569 offset above already corrects this; using UTC also avoids the local timezone pushing the date back a day.

I only want some columns?

Filter in Excel first, or pick needed keys in code after conversion.

Will large files lag?

SheetJS parses in the browser; files of a few thousand rows convert in seconds. Tens of thousands of rows work but benefit from closing other heavy tabs first.

The converted JSON contains invisible characters — now what?

Data copied from Excel frequently carries non-breaking spaces (\u00A0) and zero-width characters — invisible to the eye, fatal to matching and dedup. Run the output through Text Deduplication once, or globally clean with a \s regex pass before storing.

Excel parsing runs locally in the browser; the larger the file, the more memory. For huge sheets, split first or keep only the necessary sheet.

Triage notes

The opening reassurance — "the tool isn't broken, Excel's storage model is" — sets up four pitfalls that all trace back to that mechanism: dates are serial numbers counted from 1899-12-30, and within one column a cell showing 2023-01-01 and one showing 44927 can be the same underlying value, so the rule is never to trust the display format and to parse by serial number. Pitfall four is flagged separately as "the file is already corrupted": Excel keeps only 15 significant digits, so the 16th digit onward of an ID number turns to 0 the moment the file opens — prevention means entering such columns as text, and corruption can only be undone by re-exporting from the source. The FAQ's "off by one day" answer is the 1900 leap-year bug, already corrected by the 25569 offset in the conversion.

Provided by ToolVault. Related tools: Excel to JSON, CSV to JSON, JSON Formatter. Visit the home page for more developer tools.