How to convert excel to json for crm & erp data migration
- Step 1Audit the sheet before converting — Remove subtotal rows, formula-only columns you don't need, and merged header cells. Leave one clean header row and raw data rows. Move the entity you're migrating (contacts, accounts, deals) to the first sheet — only the first sheet is read.
- Step 2Drop the .xlsx or .xls file in — SheetJS parses it locally. The picker accepts
.xlsxand.xls. For a.csvlegacy export, convert with csv-to-json first. - Step 3Keep 'Infer types' on for correct target types — Leave Infer types on so ids/amounts are numbers and status columns are booleans. Mixed-type legacy columns will produce mixed JSON types — clean them in Excel or accept strings (see the edge cases).
- Step 4Convert one entity sheet at a time — For a multi-tab workbook (Contacts, Accounts, Deals), convert each tab separately — move it to first position, drop, convert, download, then repeat. Name each output for its entity.
- Step 5Validate before loading — Paste the output into json-validator to catch truncation, encoding, or type issues before submitting to the target's import API. For partial updates, strip nulls first with json-null-stripper.
- Step 6Load into the target system — Use the target's import path: HubSpot import API or NetSuite/REST loaders accept JSON arrays directly; Salesforce's Data Import Wizard is CSV, so convert this JSON with json-to-csv; a warehouse accepts the JSON in a staged file for COPY/Copy Activity.
Target system vs how it ingests the output
The converter emits a flat JSON array. Match it to the target's accepted format. Information current as of June 2026.
| Target | Accepts JSON array? | Step from this tool |
|---|---|---|
| Salesforce Data Import Wizard | No — CSV-based | Convert the output with json-to-csv, then upload the CSV |
| HubSpot import / CRM API | API accepts JSON; the UI import is CSV | POST the array to the API, or convert to CSV for the UI importer |
| NetSuite SuiteTalk / REST | Yes (REST record payloads) | Map keys to record fields, then POST |
| ETL platform (Fivetran/Airbyte/ADF) | Yes | Stage the JSON file as a source |
| Warehouse (Snowflake/BigQuery) | Yes (VARIANT / JSON load) | Stage and COPY INTO / bq load |
Cell → JSON for migration (Infer types ON)
What legacy cell values become, and the migration consequence.
| Excel cell | JSON value | Migration note |
|---|---|---|
100245 (account id) | 100245 | Numeric id — fine unless the target needs a string key |
| Real date cell | "2026-06-10T00:00:00.000Z" | ISO-8601 — accepted by most CRM/ERP date fields |
TRUE / FALSE | true / false | Boolean — maps to checkbox/flag fields |
| Empty cell | null | On a partial update, null can overwrite an existing value — strip first |
00245 (legacy code) | 245 if stored as number; "00245" if Text | Format as Text in Excel to keep the code intact |
Free vs Pro limits for this tool
Excel to JSON is a Pro tool. A real migration table almost always needs Pro.
| Limit | Free | Pro |
|---|---|---|
| Max file size | 2 MB | 50 MB |
| Max rows (records) | 500 | 100,000 |
| Files per run | 1 | 5 |
Cookbook
Real legacy-workbook → migration conversions with the exact JSON output and the target-load step. Sample data is anonymised.
Contacts tab → HubSpot import API
ExampleA contacts sheet whose headers match HubSpot property names. Inference types the fields; the array POSTs to the import API.
Sheet (Contacts tab, moved to first position):
email | firstname | lifecyclestage
ada@x.dev | Ada | lead
grace@x.dev | Grace | customer
Output:
[
{ "email": "ada@x.dev", "firstname": "Ada", "lifecyclestage": "lead" },
{ "email": "grace@x.dev", "firstname": "Grace", "lifecyclestage": "customer" }
]
→ POST as the records payload to HubSpot's import APISalesforce needs CSV — convert the output
ExampleSalesforce's Data Import Wizard is CSV-based. Convert the sheet here, then turn the JSON into CSV for the wizard.
Converter output:
[{ "Account Name": "Acme", "AnnualRevenue": 1250000 }]
Via /tool/json-to-csv:
Account Name,AnnualRevenue
Acme,1250000
→ Upload the CSV in Salesforce Data Import WizardStrip nulls for a partial-record update
ExampleOn an upsert that should only touch populated fields, null cells would blank out existing CRM values. Strip nulls so absent fields are simply omitted.
Converter output:
[{ "email": "ada@x.dev", "phone": null, "company": "Acme" }]
After /tool/json-null-stripper:
[{ "email": "ada@x.dev", "company": "Acme" }]
→ the existing phone value in the CRM is left untouchedConvert each entity tab separately
ExampleA migration workbook has Contacts, Accounts, and Deals tabs. The tool reads one sheet, so convert each in turn, naming each output for its entity.
Pass 1: move Contacts to first → convert → contacts.json Pass 2: move Accounts to first → convert → accounts.json Pass 3: move Deals to first → convert → deals.json Load order: Accounts → Contacts → Deals (so lookups/relationships resolve in the target)
Keep legacy zero-padded codes intact
ExampleLegacy account codes like 00245 lose leading zeros if Excel saved them as numbers. Format the column as Text so the code survives the migration.
Column formatted as Text in Excel:
account_code
00245
00310
Output (Infer types ON, but cell is text):
[{ "account_code": "00245" }, { "account_code": "00310" }]
→ zero-padding preserved as a stringErrors and edge cases
Real errors and silent failures sourced from each platform's own documentation. Match the wording to the row, fix what the row says to fix.
Salesforce import is CSV, not JSON
Format mismatchThe Salesforce Data Import Wizard (and Data Loader) ingest CSV, not JSON. Don't try to upload the JSON array. Convert this output with json-to-csv, then upload the CSV. Many other targets (HubSpot API, NetSuite REST, ETL stages) do accept the JSON array directly.
Null cells overwrite existing values on update
Data loss riskEmpty cells become null. On an upsert/partial update, a null field can blank out a populated value in the target CRM/ERP. Before an update load, strip null keys with json-null-stripper so absent fields are omitted and existing values are preserved.
Mixed-type legacy column
PreservedLegacy sheets often mix types in one column (100, N/A, 250). Per-cell inference yields a mix of numbers and strings, which a strictly-typed target field may reject. Clean the column to one type in Excel, or turn Infer types off so every value is a string and cast in your ETL layer.
Leading zeros stripped from legacy codes
Excel coercionAn account/SKU code like 00245 that Excel saved as a number becomes 245 before the converter sees it. Format the column as Text in Excel so the cell holds "00245", then convert — critical when the code is the join key in the target system.
Only the first sheet is converted
By designMulti-entity workbooks must be converted one sheet at a time — the tool reads only the first sheet and has no selector. Move each entity tab to the first position, convert, then repeat. Load entities in dependency order so relationships resolve.
Merged header / subtotal rows produce junk records
Cleanup neededMerged header cells leave blanks (→ null), and subtotal rows become bogus records. The converter has no merge-fill or subtotal detection. Unmerge and delete subtotal rows in Excel before converting so only real entity rows reach the array.
Date stored as a serial number
Excel coercionA date column formatted as General/Number holds the serial (e.g. 46184), so it converts to a number and the target's date field rejects it. Re-format the column as Date in Excel before saving so the converter emits an ISO-8601 string.
Duplicate header names collide
OverwriteTwo columns sharing a header (common after a legacy export added a column without removing the old one) map to one key; the later column overwrites the earlier in each record. Give every column a unique header before converting.
Numeric id needed as a string key
PreservedA numeric legacy id (100245) infers to a number, but some targets require the external-id field as a string. Either format that column as Text in Excel, or convert with Infer types off (all strings). The converter has no per-column type override.
Free tier size / row cap exceeded
Upgrade requiredFree tier limits this Pro tool to 2 MB and 500 rows; Pro raises it to 50 MB and 100,000 rows. A real migration table is blocked on free with an upgrade prompt. Split a very large entity sheet and recombine the JSON arrays with json-object-merger, or upgrade to Pro for the full file.
Frequently asked questions
Can I upload the JSON straight into Salesforce?
No — the Salesforce Data Import Wizard and Data Loader are CSV-based. Convert this tool's JSON output with json-to-csv, then upload the resulting CSV. Targets like HubSpot's API, NetSuite REST, and most ETL stages do accept the JSON array directly, so the JSON output is the right intermediate for those.
How do I stop blank cells from wiping out existing CRM data?
Empty cells become null, and on an update/upsert a null overwrites the existing value. Run the converter output through json-null-stripper before the load so absent fields are omitted entirely — the target then leaves those populated values untouched.
How do I handle Excel columns with mixed data types?
Per-cell inference emits whatever each cell is, so a mixed column yields a mix of numbers and strings. Clean the source column to one type in Excel, or turn Infer types off to get all strings and cast the field in your ETL transformation layer. There is no per-column type setting in the tool.
My migration sheet has 80,000 rows — will it convert?
On Pro, yes — the Pro cap is 50 MB and 100,000 rows. Free tier is limited to 2 MB and 500 rows (this is a Pro tool), so an 80,000-row sheet needs Pro. If a single entity exceeds the Pro row cap, split it and recombine the JSON with json-object-merger, or load each chunk in turn.
How do I convert several entity tabs from one workbook?
Convert one at a time — the tool reads only the first sheet and has no selector. Move each tab (Contacts, Accounts, Deals) to the first position, convert, and download with an entity-specific name. Load them in dependency order so lookups and relationships resolve in the target.
Why did a legacy account code like 00245 lose its zeros?
Excel coerced it to the number 245 when saving, before the converter saw it. Format that column as Text in Excel so the cell holds "00245", then convert. This matters most when the code is a join key in the target system.
My date fields came out as numbers — how do I fix that?
Those columns are formatted as General/Number in Excel, so the cells store the date serial rather than a date. Re-format them as a Date type and save again; the converter then emits ISO-8601 strings that most CRM/ERP date fields accept.
What file types does it accept?
.xlsx and .xls. For a .csv legacy export, convert with csv-to-json first — it handles CSV delimiters and quoting that a spreadsheet parser doesn't apply to plain text.
Should I validate the JSON before loading?
Yes. Paste the output into json-validator to catch truncated strings, encoding issues, or type mismatches before you submit to the target's import API. Catching a malformed array up front avoids a load that errors out partway through and leaves the target in a half-migrated state.
How do I keep a numeric id as a string external key?
Either format that column as Text in Excel so the cell holds the string, or convert with Infer types off (which stringifies everything). The tool has no per-column type override, so the cell's Excel format decides whether you get a number or a string.
Is my migration data — customer records, financials — kept private?
Yes. The entire conversion runs locally in your browser via SheetJS. Customer records, financial figures, and employee data in the workbook are never transmitted to or stored by JAD Apps servers — there is no upload step.
How do I clean subtotal rows and merged headers first?
Do it in Excel before converting — delete subtotal rows and unmerge any merged header cells, leaving one clean header row and only real entity rows. The converter reads the raw cell grid and has no subtotal detection or merge-fill, so any leftover junk rows become junk records in the array.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.