Back to all posts

How to Convert Excel to JSON Without Writing a Script

Shivank Prabhudessai Founder & CEO ·4 min read

To convert Excel to JSON without writing a script, open the .xlsx in a spreadsheet application that can export structured data, select the range including its header row, and export or copy as JSON. Each row becomes an object, and the header row supplies the keys. NativeOffice does this from the Data tab with Copy as JSON. The alternative is a few lines of Python with pandas, which is worth it when the conversion needs to be repeatable.

What shape should the JSON be?

This is the decision that matters most, and the one converters usually make for you without asking.

A table of people can reasonably become an array of objects:

[
  { "name": "Ada", "role": "Engineer", "team": "Core" },
  { "name": "Grace", "role": "Engineer", "team": "Tools" }
]

Or a columnar structure, which is more compact for large data and is what many charting libraries expect:

{
  "name": ["Ada", "Grace"],
  "role": ["Engineer", "Engineer"],
  "team": ["Core", "Tools"]
}

Array of objects is the right default. It is self describing, it survives reordering, and every API and language handles it without ceremony. Choose columnar only when something downstream specifically asks for it.

How are numbers and dates handled?

Badly, unless you check. This is where converted data usually breaks.

  • Numbers should come through as JSON numbers, not strings. "1200" and 1200 are different things, and the first will sort as text, putting "1200" before "900".
  • Dates are stored in Excel as serial numbers counting from 1900. A naive converter emits 45292 instead of a date. A good one emits an ISO string such as 2024-01-01.
  • Leading zeros in postcodes, phone numbers and IDs are destroyed if the column was ever treated as numeric. 01234 becomes 1234, and no converter can recover it afterwards because the information is already gone from the file.
  • Empty cells may become null, "", or be omitted entirely. All three are defensible, but they are not interchangeable to whatever consumes the file.

Format ID-like columns as Text in the spreadsheet before you convert, not after. This is the one failure that cannot be fixed downstream.

How do I convert nested data?

A spreadsheet is flat and JSON is not, so nesting has to come from somewhere.

The usual convention is dotted column headers. A column named address.city becomes a nested address object with a city key. It works, and it is readable, but it is a convention rather than a standard, so confirm the tool on the other end agrees with yours.

For anything genuinely hierarchical, such as orders each containing a variable number of line items, a flat sheet is the wrong source format. Two related sheets joined on an ID will produce far cleaner JSON than one wide sheet with item1_name, item2_name and so on.

When should I use Python instead?

Use a script when the conversion is not a one-off. If the same file arrives every week, a script is faster from the second run onward and it cannot be done inconsistently.

import pandas as pd

df = pd.read_excel("report.xlsx", sheet_name="Sheet1")
df.to_json("report.json", orient="records", indent=2, date_format="iso")

orient="records" gives the array of objects described above, and date_format="iso" avoids the serial number problem. If you are moving between a spreadsheet and pandas often, going straight from a selection to a DataFrame skips the intermediate file entirely.

For a one-off, a script is overhead. Exporting from the application is faster and there is nothing to maintain.

Should I use an online Excel to JSON converter?

Only for data you would be comfortable posting publicly.

Converting means uploading the entire spreadsheet. Business data is exactly the category people paste into these tools without thinking: customer lists, salary bands, pricing, unreleased figures. Converting locally removes the question, and for a task this mechanical there is no upside to sending the file anywhere.

The practical annoyances are real too. Free tiers cap file size, and a large export is exactly when you hit the ceiling.

What about YAML instead?

Same data, different serialisation. YAML is easier to read and to hand-edit, which makes it better for configuration; JSON is better for machine interchange and is parsed natively by every language and browser.

If a human is going to edit the result, YAML is usually kinder. If it is going into an API or a frontend, use JSON. NativeOffice's converter produces both from the same selection, and reads them back into a table, which is useful when someone sends you a JSON blob and you want to look at it as a grid.

NativeOffice converts a selection to JSON or YAML from the Data tab, free on Windows, macOS and Linux, entirely offline. Download it here.