iLoveDevTool
Back to Updates
DevTool Team

Three Ways to Turn Excel into JSON: Online Tool, Formulas, and Python

The API wants JSON, the warehouse has Excel. Here is how headers become keys and rows become objects, compared across a browser-based converter, Excel formulas for small snippets, and a repeatable pandas script.

Why "spreadsheet into JSON" is a thing at all

The API wants JSON; the warehouse has Excel. That mismatch is the most common shape of a data integration task. A spreadsheet is a two-dimensional structure for humans: one header row, one record per row below it. JSON is a tree for programs: one object per record, keys naming the fields.

The conversion boils down to a single mapping rule: headers become keys, each row becomes one object, and all rows form an array. Once that is clear, the three methods below differ only in who executes the rule: a browser-based tool, Excel itself, or a Python script.

Method 1: an online tool, for one-off conversions

The Excel to JSON converter takes this path:

  1. Upload an .xlsx file, or paste a range copied straight from Excel or WPS.
  2. The first row becomes the keys automatically. For multi-sheet files, switch to a specific sheet or export everything as a {sheetName: data} structure.
  3. Check the columns you need and export only those fields.
  4. Copy the result or download it.

A few details that make or break the output are handled for you: numbers and booleans keep their native types instead of becoming strings; dates can be output as ISO 8601, Excel serial numbers, or raw text; and integers beyond 16 digits are kept as strings, so order IDs and snowflake IDs do not lose their tails. Parsing happens locally in your browser and the file is never uploaded, which makes it usable on internal data too.

Method 2: Excel formulas, for tiny snippets

When you are writing test data for an API or building one example record, a tool is overkill. Concatenate in a cell with TEXTJOIN and &:

="{"&""""&$A$1&""""&":"&""""&A2&""""&","&""""&$B$1&""""&":"&B2&"}"

A1 and B1 hold the headers; A2 and B2 hold the first row. Fill the formula down and each row yields one object literal. Wrap the column in brackets, join with commas, and you have a JSON array.

The limits are equally clear: a dozen rows of text-only fields is the sweet spot. Number precision, escaped quotes, and date formats are all on you — past one screen of data, switch methods.

Method 3: Python, for conversions that run again next week

When the sheet refreshes weekly and the conversion belongs in a pipeline, write it down:

import pandas as pd
import json

df = pd.read_excel("orders.xlsx", dtype={"order_id": str})
records = df.to_dict(orient="records")

with open("orders.json", "w", encoding="utf-8") as f:
    json.dump(records, f, ensure_ascii=False, indent=2)

dtype={"order_id": str} reads the ID column as text and avoids the precision problem at the source. to_dict(orient="records") produces exactly the one-object-per-row list. ensure_ascii=False keeps Chinese text readable instead of escaping it to \uXXXX.

Dates need one extra step: pd.read_excel returns them as Timestamp objects, so serialize before dumping — df["created_at"] = df["created_at"].dt.strftime("%Y-%m-%d").

Three things to check before converting anything

Is the sheet actually machine-readable? Merged cells, two-row headers, subtotal rows, and blank spacer rows are layout that pleases humans and breaks converters. Normalize first: one header row, every row filled, nothing merged.

Which "numbers" are really identifiers? Amounts and quantities are fine as numbers. Order IDs, phone numbers, and national ID numbers should always be strings in JSON. The test: would you ever add or multiply this value? If not, make it a string.

Should multiple sheets stay separate? One sheet maps to one array by default. For multi-sheet files, decide up front whether you want one array per sheet or a single object keyed by sheet name.

Frequently Asked Questions

What do the Excel headers become in the JSON?

The convention is that the first row maps to the keys of a JSON object, and every data row becomes one object in an array. Empty header cells get a fallback name like column_N, and duplicate headers get a suffix so the keys stay unique.

Why do long order IDs end in zeros after the conversion?

It is a number-precision issue, not data loss in the transfer: integers beyond 15–16 significant digits cannot be represented exactly as double-precision floats. Treat such columns as text and output them as JSON strings; a reliable converter preserves them as strings automatically.

Can a sheet with merged cells be converted directly?

Not as-is. Merged cells mean some rows have no value of their own, and a mechanical conversion produces nulls or empty strings. Normalize the sheet first — one header row, every row filled, no merges — then convert.

What happens to Excel dates in the JSON output?

It depends on the converter: common outputs are ISO 8601 strings, the Excel date serial number, or the displayed text. Confirm which one the receiving API expects before converting, especially when time zones matter.

Does converting Excel in a browser upload my file?

It depends on the service. The converter on this site parses the .xlsx locally in your browser and never uploads the file. Check a service's privacy statement before using it, especially for sheets with sensitive data.