The basic rule for Excel to JSON is simple: headers become field names, and each row becomes a JSON object.
[
{
"name": "Alice",
"email": "alice@example.com",
"active": true
}
]Clean the Sheet Before Conversion
- Use the first row as field names and avoid empty or duplicate headers.
- Keep each column to one kind of data.
- Decide whether date columns should remain text or become date strings.
- Remove merged cells, notes, and summary rows before conversion.
- If the file has multiple sheets, confirm which sheet the tool reads.
How to Represent Nested Fields
Many APIs expect nested objects, while Excel is a flat table. A common approach is to use path-like headers such as user.name, user.email, and order.id, then rebuild those paths into nested JSON.
[
{
"user": {
"name": "Alice",
"email": "alice@example.com"
},
"order": {
"id": "A-1001",
"total": 59.9
}
}
]| Excel header | JSON result | Best for |
|---|---|---|
| user.name | user: { name: ... } | Profiles and contacts |
| order.total | order: { total: ... } | Orders, quotes, and billing data |
| tags | tags: ... | Simple tag columns that can later become arrays |
| address.city | address: { city: ... } | Addresses, regions, and shipping data |
Handle Empty Values, Dates, and Numbers Carefully
A cell that looks like text in Excel may actually be a number, a date serial value, or a formatted cell. IDs, phone numbers, and postal codes should usually stay as text so leading zeros are not lost. Amount fields need decimal checks.
- Save IDs, phone numbers, and postal codes as text when needed.
- Check decimal places for amounts, quantities, and ratios.
- Normalize dates to yyyy-mm-dd or ISO strings before sending them to an API.
- Decide whether blank cells should become null, empty strings, or omitted fields.
Common Issues
| Issue | Cause | Suggestion |
|---|---|---|
| Strange field names | Headers contain spaces, line breaks, or duplicates | Normalize headers first |
| Numbers become text | Cell formats are inconsistent | Check key fields after conversion |
| Empty values change | Different blank-cell rules | Normalize according to API needs |
| Dates look wrong | Excel dates may be serial values | Convert dates to text first |
Checklist Before Importing to an API
- Convert a small sample first and confirm field names and nested structure.
- Use a JSON formatter to verify that the output is valid JSON.
- Spot-check the first row, a middle row, and the last row to catch empty or summary rows.
- Compare required fields, field types, and date formats with the API documentation.
- Keep the original Excel file before a production import.