How to convert excel to json for mongodb import
- Step 1Shape the sheet for one document per row — One row per document, one column per field, a single header row at the top. Header text becomes the MongoDB field names exactly (trimmed). Put the sheet you want first — the converter reads only the first sheet.
- Step 2Drop the .xlsx or .xls file in — SheetJS parses it locally. The picker accepts
.xlsxand.xls. For a.csvsource, convert with csv-to-json first, which is built for delimiter-separated input. - Step 3Keep 'Infer types' on so documents get real BSON types — Leave Infer types on so quantities and prices are numbers and flags are booleans. Turn it off only if you want every field stored as a string (with empties still as
null). - Step 4Decide on empty-cell handling — Empty cells become
null. Leave Skip empty rows on to drop fully blank rows. If you do not wantnullfields written into every document, run the output through json-null-stripper before importing. - Step 5Download the JSON array — Pick Minified indentation for the smallest file, then Download JSON. The result is one JSON array of documents.
- Step 6Import with mongoimport --jsonArray or Compass — Run
mongoimport --db mydb --collection products --jsonArray --file products.json, or open Compass → your collection → Add Data → Import JSON. Because the output is an array (not NDJSON), the--jsonArrayflag is required on the CLI.
Import path vs converter output
The converter emits a JSON array of objects. Match it to the right import command. There is no NDJSON output mode in this tool.
| Target | Command / action | Notes |
|---|---|---|
| mongoimport (array) | mongoimport --db d --collection c --jsonArray --file out.json | Required: --jsonArray, because output is [ {...}, {...} ], not line-delimited |
| MongoDB Compass | Collection → Add Data → Import JSON → select the file | Compass auto-detects an array of documents; no flag needed |
| Need NDJSON instead | Convert the array to NDJSON yourself: jq -c '.[]' out.json > out.ndjson | The tool does not emit NDJSON; this one-liner produces it for mongoimport without --jsonArray |
| Typed columns | mongoimport --columnsHaveTypes (CSV/TSV only) | For JSON input, types come from the JSON itself — inference already set numbers/booleans |
Cell type → BSON type after import (Infer types ON)
What each Excel cell becomes in JSON, and how MongoDB stores it on import.
| Excel cell | JSON value | BSON type on import |
|---|---|---|
120 (number) | 120 | Double / Int32 (Mongo number) |
TRUE / FALSE | true / false | Boolean |
| Real date cell | "2026-06-10T00:00:00.000Z" | String — use $dateFromString post-import for Date |
| Empty cell | null | Null (or omitted if you strip nulls first) |
SKU-007 (text) | "SKU-007" | String |
Free vs Pro limits for this tool
Excel to JSON is a Pro tool. Free tier is for trying it on small extracts; full collections need Pro.
| Limit | Free | Pro |
|---|---|---|
| Max file size | 2 MB | 50 MB |
| Max rows (documents) | 500 | 100,000 |
| Files per run | 1 | 5 |
Cookbook
Real spreadsheet → MongoDB-import conversions with the exact JSON the converter produces and the matching import command. Sample data is illustrative.
Product sheet → mongoimport --jsonArray
ExampleA products sheet becomes a JSON array. Numbers and booleans are inferred, so price range queries work immediately after import.
Sheet:
sku | name | price | inStock
SKU-001 | Cyber Mug | 12.5 | TRUE
SKU-002 | Sticker | 3 | FALSE
Output (Minified):
[{"sku":"SKU-001","name":"Cyber Mug","price":12.5,"inStock":true},{"sku":"SKU-002","name":"Sticker","price":3,"inStock":false}]
Import:
mongoimport --db shop --collection products --jsonArray --file products.jsonConvert the array to NDJSON for the non-array path
ExampleIf your pipeline expects line-delimited JSON, the tool does not emit it — convert the array yourself with jq, then import without --jsonArray.
Converter output (array):
[{"sku":"SKU-001"},{"sku":"SKU-002"}]
Make NDJSON:
jq -c '.[]' products.json > products.ndjson
→ {"sku":"SKU-001"}
{"sku":"SKU-002"}
Import (no --jsonArray):
mongoimport --db shop --collection products --file products.ndjsonTurn ISO date strings into real BSON Dates
ExampleDate cells convert to ISO-8601 strings. To store true BSON Date objects, run an aggregation update after import.
After import, ordered.createdAt is a string:
{ "createdAt": "2026-06-10T00:00:00.000Z" }
Convert in place:
db.orders.updateMany({}, [
{ $set: { createdAt: { $dateFromString: { dateString: "$createdAt" } } } }
])
→ createdAt is now an ISODateDrop null fields so documents stay sparse
ExampleEmpty cells become null. If you would rather omit absent fields entirely, strip nulls before importing.
Converter output:
[{"sku":"SKU-003","name":"Pin","discount":null}]
After /tool/json-null-stripper:
[{"sku":"SKU-003","name":"Pin"}]
Import → the discount field is simply absent on that documentPreserving existing _id values from a prior export
ExampleThe converter assigns no _id. For fresh data, mongoimport generates ObjectIds. To keep prior _ids, include an _id column whose cells hold the hex string, then wrap it post-import.
Sheet with an _id column:
_id | name
665f1a2b3c4d5e6f70819a2b | Cyber Mug
Output:
[{"_id":"665f1a2b3c4d5e6f70819a2b","name":"Cyber Mug"}]
This imports _id as a STRING. To restore ObjectId type:
db.products.updateMany({}, [{ $set: { _id: { $toObjectId: "$_id" } } }])
// (or re-key into a new collection — _id is immutable in place)Errors 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.
Output is a JSON array, not NDJSON
By designThe converter has no newline-delimited (NDJSON) output mode — it always emits [ {...}, {...} ]. Use mongoimport --jsonArray or Compass, both of which read arrays. If you specifically need NDJSON, run jq -c '.[]' out.json to split the array into lines, then import without the --jsonArray flag.
Date cells import as strings, not BSON Date
ExpectedInference emits ISO-8601 strings for date cells, and JSON has no native date type, so MongoDB stores them as strings. To get true ISODate values, run an updateMany with $dateFromString after import (see the cookbook), or include a typed-conversion step in your ingestion pipeline.
_id column imports as a string
ExpectedAn _id column with a 24-char hex value imports as a string, not an ObjectId, because JSON has no ObjectId literal. Convert with $toObjectId after import, or leave _id out entirely and let mongoimport generate fresh ObjectIds.
Date stored as a serial number
Excel coercionIf a date column is formatted as General/Number in Excel, the cell holds the date serial (e.g. 46184), so inference returns a number and you import an integer, not a date string. Re-format the column as Date in Excel before saving so SheetJS reads it correctly.
Leading zeros stripped from codes
Excel coercionA code like 00120 that Excel saved as a number becomes 120 before the converter sees it. Format the column as Text in Excel so the cell holds "00120", then convert — the string is preserved exactly for the import.
Only the first sheet is converted
By designThe tool reads the first sheet only; there is no sheet selector. For a workbook with one sheet per collection, move each sheet to the first position (or split into single-sheet files) and convert one at a time, importing into its matching collection.
Null fields written into every document
PreservedEmpty cells become explicit null, so a sparse sheet produces documents full of null fields. That bloats storage and complicates $exists queries. Run the output through json-null-stripper before importing if you want absent fields omitted instead.
Mixed-type column breaks index assumptions
PreservedInference is per cell, so a qty column with 5, N/A, 12 yields a mix of numbers and strings across documents. A numeric index on that field then behaves inconsistently. Normalize the column to one type in Excel, or turn Infer types off to store every value as a string.
Duplicate headers overwrite each other
OverwriteTwo columns with the same header text collapse to one field; the later column wins in each document. Ensure unique header names before converting — a MongoDB document cannot hold duplicate keys.
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 full collection export is blocked on free with an upgrade prompt. Split the sheet and import each chunk into the same collection, or upgrade to Pro for the whole file in one pass.
Frequently asked questions
Does this tool output NDJSON for mongoimport?
No — it outputs a single JSON array ([ {...}, {...} ]). Import it with mongoimport --jsonArray or via Compass, both of which read arrays directly. If your workflow needs NDJSON, convert the array with jq -c '.[]' out.json > out.ndjson and import that file without the --jsonArray flag.
Why are my date fields strings in MongoDB instead of dates?
JSON has no native date type, so date cells become ISO-8601 strings and MongoDB stores them as strings. Convert them after import with db.coll.updateMany({}, [{ $set: { d: { $dateFromString: { dateString: "$d" } } } }]), or use a typed ingestion step. The string format (2026-06-10T00:00:00.000Z) is exactly what $dateFromString expects.
My date column came out as 46184 — what went wrong?
That column was formatted as General or Number in Excel, so the cell stores the date serial rather than a date value. Re-format it as a Date type in Excel and save again; the converter then reads a true date cell and emits an ISO-8601 string.
How do I keep an existing _id from a previous export?
Include an _id column whose cells contain the 24-character hex string. It imports as a string (JSON has no ObjectId literal); convert it with $toObjectId after import, or re-key into a new collection. For brand-new data, omit _id and let mongoimport assign fresh ObjectIds.
Can I avoid writing null fields into every document?
Yes. Empty cells produce null by default. Run the converter output through json-null-stripper to remove null-valued keys before importing, keeping your documents sparse and $exists queries clean.
What file types are accepted?
.xlsx and .xls. If your source is .csv, convert it with csv-to-json first — it handles CSV delimiters and quoting that a spreadsheet parser does not apply to plain text files.
Is there a row limit for a big collection?
Yes. This is a Pro tool: free tier allows 2 MB / 500 rows; Pro allows 50 MB / 100,000 rows. For a large export, upgrade to Pro, or split the sheet and import each chunk into the same collection — the documents append, MongoDB does not require them in one file.
Can I convert several sheets for several collections at once?
Not in one pass — the tool reads only the first sheet. Move each sheet to the first tab (or save each as its own file) and convert separately, importing each resulting JSON array into its matching collection.
Does my production data get uploaded during conversion?
No. SheetJS parses the workbook in your browser. Customer records, financials, and PII in the spreadsheet are never transmitted to JAD Apps or any third party — the conversion is fully client-side.
How do I validate the JSON before importing?
Paste the output into json-validator to confirm it is well-formed before you run mongoimport. A malformed array is the most common cause of an import that errors out partway through.
Why did a code like 00120 lose its zeros?
Excel coerced it to the number 120 when saving, before the converter saw it. Format the column as Text in Excel so the cell holds "00120", then convert — the leading zeros are preserved as a string field in the document.
Can I merge several converted JSON files into one before importing?
Yes. If you split a large sheet and converted each part, combine the resulting arrays with json-object-merger into one array, then run a single mongoimport --jsonArray. Or just import each file in turn into the same collection — both approaches yield the same documents.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.