Excel to JSON for developers, shapes and nested headers

Converting a spreadsheet to JSON is a design decision disguised as a file conversion. The same sheet of 2,000 rows can become several very different documents, and the shape you choose decides how much code you write afterwards. This guide compares the common shapes, explains how to turn grouped or nested headers into nested objects, and covers the type questions that break parsers. You can try each option in the Excel to JSON converter.

The four common JSON shapes

Take a small product sheet with three columns, sku, name and price. Here is how it looks in each shape.

Array of objects

[
  {"sku": "A-100", "name": "Desk lamp", "price": 39.5},
  {"sku": "A-101", "name": "Floor lamp", "price": 89}
]

Each row becomes an object, each header becomes a key. This is the default for a reason. It maps directly onto database rows, ORM models, API payloads and front end tables. It is self describing, so a consumer does not need to know the column order. The cost is size, because every key is repeated on every row.

Keyed by a column

{
  "A-100": {"name": "Desk lamp", "price": 39.5},
  "A-101": {"name": "Floor lamp", "price": 89}
}

One column becomes the key of a top level object. This is ideal for lookups, such as translation files, configuration tables or a price list you query by SKU. It requires the key column to be unique. If two rows share a key, a converter has to either fail or let the later row overwrite the earlier one, and silent overwrites are a classic source of missing data. XlsConverter reports duplicate keys instead of dropping them.

Array of arrays

[
  ["sku", "name", "price"],
  ["A-100", "Desk lamp", 39.5],
  ["A-101", "Floor lamp", 89]
]

Rows as plain arrays, usually with the header as the first row. It is compact and preserves column order exactly, which matters for charting libraries and for re-creating the sheet later. The consumer has to know which index means what.

Columnar

{
  "sku": ["A-100", "A-101"],
  "name": ["Desk lamp", "Floor lamp"],
  "price": [39.5, 89]
}

One array per column. Analytics tools and dataframe libraries load this efficiently, and it compresses well. It is awkward for record by record processing.

Shape	Best for	Watch out for
Array of objects	APIs, databases, general use	Repeated keys increase size
Keyed by column	Lookups, config, translations	Duplicate keys
Array of arrays	Charts, round trips to a sheet	Meaning depends on position
Columnar	Analytics, dataframes	Hard to process row by row

Turning headers into keys

Spreadsheet headers are written for people. They contain spaces, punctuation, line breaks and the occasional trailing space nobody can see. As JSON keys they cause friction, because data["Unit Price (USD) "] is easy to get wrong. A good conversion normalizes headers into predictable keys.

• Trim whitespace and collapse internal line breaks.
• Choose one case convention, such as snake_case (unit_price_usd) or camelCase (unitPriceUsd), and apply it everywhere.
• Make duplicates unique, since two columns called Notes would otherwise collide. Appending a suffix (notes, notes_2) keeps both.
• Give empty headers a name, such as column_7, rather than an empty string key.

When your application already defines the target field names, a mapping template is better than any automatic rule. You map Unit Price (USD) to price once, and every later file follows it. XlsConverter can also suggest the mapping by matching headers against a target schema, which helps when each supplier names the same column differently.

Nested headers and nested objects

Many sheets use two header rows. The top row groups columns, such as Billing and Shipping, and the second row holds city, zip and country under each group. Flattening that into one row of keys produces billing_city and shipping_city, which works but loses the structure.

The alternative is to treat the grouping as nesting. A common convention uses dotted headers, where billing.city and billing.zip become fields of a billing object.

{
  "order_id": "SO-2291",
  "billing": {"city": "Denver", "zip": "80202"},
  "shipping": {"city": "Austin", "zip": "78701"}
}

Two practical notes. Merged cells in the group row store the value only in the first cell, so a converter must spread the group name across the merged range before building keys. And a numeric segment, such as items.0.sku, can be read as an array index, which is how one row can carry a short list. Repeating groups of unknown length are better handled as a second sheet joined by an ID than as ever wider rows.

Types, dates and empty cells

Numbers and strings

JSON distinguishes numbers from strings, so the converter has to choose. Numeric cells become JSON numbers, and text cells stay strings, which protects ZIP codes such as 02134 and SKUs with leading zeros. Be careful with very large integers. JavaScript represents numbers as 64 bit floats, so integers above 9,007,199,254,740,991 lose precision when parsed. Long IDs are safer as strings.

Dates

JSON has no date type. Excel stores dates as serial numbers, so a naive export produces 45931 where you expected a date. Write ISO 8601 strings instead, such as "2025-10-01" or "2025-10-01T14:30:00", which every language parses. The conversion must respect the workbook date system (1900 or 1904) and the 1900 leap year quirk, where Excel counts a February 29, 1900 that never existed.

Booleans and formulas

TRUE and FALSE cells map to JSON true and false. Formula cells should export their calculated value, not the formula text, unless you explicitly want the formula for documentation.

Empty cells

There are three reasonable choices for an empty cell. Output null, which keeps every object the same shape and suits typed consumers. Omit the key, which keeps the payload small and suits document stores. Or output an empty string, which is rarely what you want, because it hides the difference between unknown and blank. Pick one rule per pipeline and keep it. Trailing empty rows and columns, often left behind by formatting, should be dropped so you do not ship objects full of nulls.

Choosing a shape by consumer

If you are unsure, start from the code that reads the file. A REST endpoint that creates records expects an array of objects and will validate each one. A front end that renders a searchable table also wants an array of objects, because it can sort and filter by key. A service that answers lookups by product code is simpler with a keyed object, since it becomes a direct property access instead of a search. A notebook or dataframe library reads columnar data fastest. When two consumers need different shapes, convert twice from the same template rather than reshaping in code, so both outputs share the same header rules, type rules and null handling.

Validating the output

Before you wire the conversion into production, check a few things on a real file. Confirm that the number of objects equals the number of data rows in the sheet. Spot check a date, a long identifier and a value with an accented character. Parse the result with the same JSON library your service uses, because a document that one parser accepts can still fail in another when it contains unusual escapes. If you work with a JSON Schema, validate against it, which catches a renamed column immediately.

Several sheets in one document

A workbook with Products and Prices sheets can become one JSON document keyed by sheet name, with each value in the shape you chose. This keeps related tables together for a single API call. When the sheets are large, separate files per sheet are easier to stream.

Automating the conversion

If spreadsheets arrive from customers or partners, converting them by hand does not scale. The XlsConverter REST API accepts the workbook, applies your shape and mapping template, and delivers the JSON to a signed webhook, so your service receives ready data instead of an attachment. The reverse direction is covered by JSON to Excel, which flattens nested objects back into dotted columns.