How to convert excel to json for node.js database seeding
- Step 1Lay out a flat table that mirrors the table columns — One row per record, one column per field, one header row. Name the headers to match your model field names where you can — the converter keeps header text exactly (trimmed). Put the target sheet first; 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.csvreference file, convert with csv-to-json first. - Step 3Keep 'Infer types' on so rows pass validation — Leave Infer types on so integer IDs are numbers and boolean columns are
true/false. Confirm in the preview that ID columns aren't strings — a stray text cell forces the whole column behaviour per cell. - Step 4Remap headers to your model fields if needed — If a header is
First Namebut your Prisma field isfirstName, the tool will not convert it for you. Run the output through json-key-renamer to map keys, or rename the headers in Excel before converting. - Step 5Download into your seed folder — Click Download JSON and save it as
prisma/seed-data.json(orsrc/db/seeds/products.json). Use a name that matches the table you are seeding. Pick 2 spaces if you'll commit it for review. - Step 6Import in the seed script and call createMany —
import seedData from "./seed-data.json"(orrequire(...)in CommonJS), thenawait prisma.product.createMany({ data: seedData, skipDuplicates: true }). The same array works for Knex.insert()or SequelizebulkCreate().
The four real options (Node seed context)
Every control the converter exposes. There is no camelCase option, no sheet selector, and no key wrapper — output is a flat array from the first sheet.
| Option (UI label) | Default | Effect on the seed array |
|---|---|---|
| First row is header | On | Row 1 supplies the object keys (trimmed). Off → keys are col0, col1, … (rename with json-key-renamer) |
| Infer types | On | IDs/quantities → numbers, active → boolean, dates → ISO strings, empty → null. Off → everything stringified (empty still null) |
| Skip empty rows | On | Drops fully blank rows so you never insert all-null records |
| Indentation | 2 spaces | Minified, 2 spaces, or 4 spaces — cosmetic only; the array and values are identical |
ORM consumption of the array
The same flat JSON array feeds every common Node ORM/query builder.
| ORM / driver | Call | Note |
|---|---|---|
| Prisma | prisma.model.createMany({ data: seedData, skipDuplicates: true }) | Keys must match model field names — remap first if they don't |
| Sequelize | Model.bulkCreate(seedData) | Add { validate: true } to enforce model validations |
| Knex | knex('table').insert(seedData) | Chunk large arrays with batchInsert |
| Raw pg / mysql2 | Build a multi-row INSERT from the array, or use json-to-sql | json-to-sql generates the INSERT statements directly |
Free vs Pro limits for this tool
Excel to JSON is a Pro tool. Free tier is for small reference tables; large seeds need Pro.
| Limit | Free | Pro |
|---|---|---|
| Max file size | 2 MB | 50 MB |
| Max rows (records) | 500 | 100,000 |
| Files per run | 1 | 5 |
Cookbook
Real reference-table → seed-data conversions with the exact JSON output and the matching seed call. Sample data is illustrative.
Category reference table → Prisma createMany
ExampleA simple lookup sheet whose headers already match the Prisma model. Numbers and booleans are inferred, so the array seeds without casting.
Sheet (headers match the model):
id | name | active
1 | Electronics | TRUE
2 | Apparel | FALSE
Output (2 spaces) saved as prisma/categories.json:
[
{ "id": 1, "name": "Electronics", "active": true },
{ "id": 2, "name": "Apparel", "active": false }
]
Seed:
await prisma.category.createMany({ data: categories, skipDuplicates: true });Remap 'First Name' → firstName before seeding
ExampleThe converter keeps header text verbatim, so a spaced header won't match a camelCase model field. Remap the keys after conversion.
Converter output:
[{ "First Name": "Ada", "Email": "ada@x.dev" }]
The tool does NOT camelCase. Use /tool/json-key-renamer:
"First Name" → firstName, "Email" → email
Result:
[{ "firstName": "Ada", "email": "ada@x.dev" }]
→ now matches prisma.user fieldsResolve a name-based foreign key in the seed script
ExampleSheets often carry the parent's name, not its id. Seed parents first, build a lookup, then map the child rows.
products.json from the converter:
[{ "name": "Cyber Mug", "category": "Electronics" }]
In seed.ts:
const cats = await prisma.category.findMany();
const byName = Object.fromEntries(cats.map(c => [c.name, c.id]));
const data = products.map(p => ({
name: p.name, categoryId: byName[p.category]
}));
await prisma.product.createMany({ data });Generate INSERT statements instead of using an ORM
ExampleFor a raw pg/mysql2 script, skip ORM seeding entirely — feed the converted array to json-to-sql to get the INSERT statements.
products.json:
[{ "id": 1, "name": "Cyber Mug", "price": 12.5 }]
Via /tool/json-to-sql (table: products):
INSERT INTO products (id, name, price) VALUES
(1, 'Cyber Mug', 12.5);Header-less sheet → positional keys, then rename
ExampleIf a reference table has no header row, turn off 'First row is header'. Keys become col0/col1 — rename to your field names afterward.
Sheet (no header):
Electronics | TRUE
Apparel | FALSE
Output (First row is header OFF):
[
{ "col0": "Electronics", "col1": true },
{ "col0": "Apparel", "col1": false }
]
Then /tool/json-key-renamer: col0→name, col1→activeErrors 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.
Headers are NOT camelCased
By designThe converter uses header text exactly as typed (trimmed of surrounding spaces) — it does not normalize First Name to firstName or unit_price to unitPrice. If your model fields are camelCase, either rename the headers in Excel before converting or remap the keys with json-key-renamer afterward.
Name-based foreign key, not an id
Manual stepIf the sheet stores a parent's name (e.g. category: "Electronics") instead of its id, the JSON carries the name. Seed the parent table first, query the inserted ids, build a name→id map, and replace the values in your script before createMany on the child table (see the cookbook). The converter cannot resolve relations.
ID column came through as strings
Excel coercionIf an id column has any non-numeric cell, that cell becomes a string and Prisma will reject the row (Int field got a String). Ensure the column is purely numeric in Excel, then re-convert with Infer types on.
Date cell needed as DateTime but stored as serial
Excel coercionA date column formatted as General/Number holds the serial (e.g. 46184), so it converts to a number, not an ISO string, and your DateTime/timestamptz column rejects it. Re-format the column as Date in Excel before saving.
Empty cells become null, not the column default
PreservedA blank cell is null, which overrides a database column default during insert. If you want the DB default to apply, strip the null keys with json-null-stripper so the field is omitted from the insert payload, letting the default kick in.
Only the first sheet is converted
By designSeeding several tables from a multi-sheet workbook means converting each sheet separately — the tool reads only the first sheet and has no selector. Convert in dependency order and seed parents before children so foreign keys resolve.
Duplicate primary keys violate the unique constraint
Constraint rejectIf the sheet has two rows with the same id, the array carries both and createMany fails on the unique constraint (unless skipDuplicates: true). Deduplicate in Excel first, or use skipDuplicates and accept that the first occurrence wins.
Mixed-type column breaks the column type
PreservedInference is per cell, so a qty column with 5, n/a, 12 yields a mix of numbers and strings. The string row fails an Int column. Normalize the column in Excel, or turn Infer types off and cast every field in the script.
Blank header → col index key
PreservedAn empty header cell (with 'First row is header' on) yields a positional key col<index>, which won't match any model field. Give every column a header, or rename col3 afterward with json-key-renamer.
Free tier size / row cap exceeded
Upgrade requiredFree tier caps this Pro tool at 2 MB and 500 rows; Pro raises it to 50 MB and 100,000 rows. A large reference export is blocked on free with an upgrade prompt. For a quick seed, trim under 500 rows; for production, Pro handles the full table.
Frequently asked questions
Does the converter rename 'First Name' to firstName automatically?
No. It keeps header text exactly as typed (trimmed). There is no camelCase option. Rename the headers in Excel before converting, or run the output through json-key-renamer to map First Name → firstName across every object so the keys match your model fields.
How do I handle foreign keys where the sheet has names, not ids?
Seed the parent table first, query the inserted rows, and build a name→id lookup in your seed script. Map the child array to replace each name with the resolved id before calling createMany. The converter outputs the names verbatim — relation resolution is your script's job, as shown in the cookbook.
Can I seed multiple tables from multiple sheets at once?
Not in one pass — the tool reads only the first sheet and has no sheet selector. Convert each sheet separately (move it to the first tab, or save it as its own file), then import each JSON file in foreign-key dependency order so parents exist before children.
Will my id column come through as numbers?
Yes, with Infer types on, provided every cell in the column is numeric. A single non-numeric cell makes that cell a string, and Prisma rejects a string in an Int field. Check the preview and clean the column in Excel if any id shows up quoted.
Why are blank cells overriding my column defaults?
Empty cells become null, and inserting null overrides a DB column default. If you want defaults to apply, strip the null keys with json-null-stripper so those fields are omitted from the insert payload entirely.
Can I generate raw INSERT statements instead of using an ORM?
Yes. Convert the sheet here, then feed the JSON array to json-to-sql with your table name. It emits INSERT INTO ... VALUES (...) statements you can run directly via pg, mysql2, or a .sql migration file.
What file types are accepted?
.xlsx and .xls. For a .csv reference file, use csv-to-json first — it handles CSV delimiters and quoting that a spreadsheet parser does not apply to plain-text input.
My date column came out as a number — how do I fix it?
That column is formatted as General/Number in Excel, so the cell stores the date serial rather than a date. Re-format it as a Date type and save again; the converter then emits an ISO-8601 string your ORM accepts for DateTime/timestamptz columns.
Does createMany fail if the sheet has duplicate ids?
Yes, on the unique constraint — unless you pass skipDuplicates: true to Prisma's createMany, which silently skips the colliding rows. Better to deduplicate in Excel first so the row count is predictable and you know exactly which records you're seeding.
Is the spreadsheet data uploaded anywhere?
No. SheetJS parses the workbook entirely in your browser. Employee records, pricing, or internal configuration in the sheet are never transmitted to JAD Apps or any third party — there is no server in the conversion path.
What's the row limit for a big seed?
Free tier caps this Pro tool at 2 MB / 500 rows; Pro raises it to 50 MB / 100,000 rows. For a seed beyond the Pro row cap, split the sheet, convert each part, and combine the arrays with json-object-merger, or seed each chunk in turn.
How do I validate the seed JSON before running the script?
Paste the output into json-validator to confirm it's well-formed. To derive a TypeScript type for the seed data (handy with Prisma's typed client), feed one record into json-to-typescript.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.