How to convert Excel to JSON: Python, Node.js and online methods
TL;DR: The quickest way to convert Excel to JSON is Python: pandas.read_excel('file.xlsx').to_json('file.json', orient='records', indent=2, force_ascii=False). In Node.js use the xlsx package with sheet_to_json. Online converters are fine for non-sensitive data, but do not upload private spreadsheets to unknown sites. Watch out for date formats, empty cells, header names and multiple sheets, and validate the JSON before using it.
Python with pandas
import pandas as pd
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")
df.to_json("data.json", orient="records", indent=2, force_ascii=False)
orient="records" gives a list of objects, one per row. Install with pip install pandas openpyxl. To convert all sheets, pass sheet_name=None and loop over the dictionary.
Node.js with SheetJS
const XLSX = require("xlsx");
const wb = XLSX.readFile("data.xlsx");
const sheet = wb.Sheets[wb.SheetNames[0]];
const rows = XLSX.utils.sheet_to_json(sheet, { defval: null });
require("fs").writeFileSync("data.json", JSON.stringify(rows, null, 2));
defval: null keeps empty cells as null.
From CSV with jq
Save the sheet as CSV, then use a CSV-to-JSON tool such as Miller or csvkit:
csvjson data.csv > data.json
Online converters
Online tools are quick for small public data. Avoid uploading customer lists, invoices or anything sensitive. Check the tool's privacy statement, or use a local method instead.
Things that go wrong
- Dates may become numbers or timestamps. Format them as ISO strings first.
- Empty cells become null or empty strings. Decide which you want.
- Duplicate or blank headers produce unusable keys.
- Numbers stored as text keep leading zeros only if the column is text.
- Special characters. Use UTF-8 and
force_ascii=False. - Large files. Process in chunks.
Validate the result
Open the file in a JSON validator or run python -m json.tool data.json. If you plan to import the JSON into WordPress or a database, test with a few rows first. For related automation, see Zapier vs Make.
FAQs
How do I convert Excel to JSON?
Use pandas in Python, SheetJS in Node.js or an online converter.
Can I convert Excel to JSON without code?
Yes, with an online converter or by exporting to CSV and using a free tool.
Is it safe to use online converters?
Only for non-sensitive data.
How do I convert multiple sheets?
Loop through the sheets in code and write one file each.
Why are my dates wrong?
Excel stores dates as numbers. Format them as text or ISO strings.
What JSON structure do I get?
With records orientation, a list of objects keyed by header names.
Can Excel export JSON directly?
Not directly, but Power Query and scripts can.
What if my file is large?
Use pandas chunks or a streaming parser.
Conclusion
Pick a local method such as pandas or SheetJS, clean headers and dates and validate the output. Use online converters only for data you are happy to share.
HostCloud helps Indian websites with reliable hosting, domains and support. See the options at https://hostcloud.in.
Sources: