How to convert excel reference data to sql insert for database migration
- Step 1Open the converter — The Excel → SQL link redirects to the JSON to SQL converter — the real engine — which accepts
.xlsx,.xls,.ods,.csv, and.jsondirectly. No pre-conversion needed. - Step 2Prepare the source sheet — Put the migration data on the first sheet with a clean header row. Flatten any formulas to values first with formula-to-value so the migration captures results, not
=VLOOKUP(...)text. - Step 3Set the table name to match your schema — Type the destination table (default
data) so the generated DDL/INSERT matches the table your migration tool expects. - Step 4Choose the dialect for the environment — Pick PostgreSQL, MySQL, or SQLite to match the target. The dialect drives quoting, booleans, and the conflict syntax — generate once per engine if your environments differ.
- Step 5Make it idempotent and trim noise — Keep Include CREATE TABLE and IF NOT EXISTS on, tick ON CONFLICT DO NOTHING, and list spreadsheet-only columns in Exclude columns so the committed migration is clean and re-runnable.
- Step 6Drop it into a migration file — Click Generate SQL, then Download SQL (or Copy) into your migrations folder as the next numbered step. Commit it; reviewers see the exact rows being seeded in the diff.
Migration-tool fit
How the generated output drops into common migration frameworks. The tool produces plain SQL; the framework wrapper around it is yours to add.
| Framework | Where the SQL goes | Idempotency tip |
|---|---|---|
| Flyway | V003__seed_regions.sql (versioned) | Keep IF NOT EXISTS + ON CONFLICT so re-baselining is safe |
| Liquibase | A <sqlFile> changeSet | Set a precondition or rely on ON CONFLICT |
| Alembic (Python) | op.execute("""...""") in upgrade() | ON CONFLICT DO NOTHING for repeatable upgrades |
| Rails / ActiveRecord | execute(<<~SQL ... SQL) in a migration | ON CONFLICT keeps db:seed reruns clean |
| Raw psql / mysql CLI | A .sql file run with \i / source | IF NOT EXISTS prevents create errors on re-run |
What you must fix before committing a migration
Predictable transforms needed because the generator infers a narrow type set. Do these in Excel (or the linked tool) so the committed migration is correct.
| Source data | Generated as | Fix before commit |
|---|---|---|
| Excel date column | INTEGER serial | Standardise to ISO text, then CAST/ALTER to date |
| Currency / money | DOUBLE PRECISION | Edit DDL to NUMERIC(12,2) to avoid float rounding |
| Formula cells | Formula result via SheetJS | Run formula-to-value to lock values |
| 18-digit IDs | Risk of precision loss | Format column as Text in Excel → emitted as quoted TEXT |
| Spreadsheet audit fields | Extra columns | Add to Exclude columns |
Cookbook
Migration-oriented fragments: a versioned seed step, an idempotent rerun, and the date/precision fixes you apply before committing.
A versioned Flyway seed step
Generate Postgres SQL with CREATE TABLE + INSERT and save it as a numbered Flyway migration. The whole step is one reviewable file.
File: V003__seed_region.sql
CREATE TABLE IF NOT EXISTS "region" (
"id" INTEGER,
"code" TEXT
);
INSERT INTO "region" ("id", "code")
VALUES
(1, 'EU'),
(2, 'US'),
(3, 'APAC');Idempotent CI re-apply
With ON CONFLICT DO NOTHING the migration is safe to apply again in CI or against a partially-seeded staging DB.
INSERT INTO "region" ("id", "code")
VALUES
(1, 'EU'),
(2, 'US') ON CONFLICT DO NOTHING;
-- second run: 0 rows changed, no errorEmbed in an Alembic upgrade()
Copy the generated SQL into op.execute() so the seed travels with your Python migration history.
def upgrade():
op.execute("""
INSERT INTO region (id, code) VALUES
(1, 'EU'), (2, 'US') ON CONFLICT DO NOTHING;
""")Fix dates before the migration is committed
Don't ship serial numbers. Standardise to ISO text, generate, then alter the column type as part of the same migration.
INSERT INTO "region" ("id", "created")
VALUES (1, '2024-01-15');
-- same migration, after the INSERTs:
ALTER TABLE region
ALTER COLUMN created TYPE date USING created::date;Money as NUMERIC, not float
Decimal columns infer DOUBLE PRECISION. For a price column in a migration, hand-edit the DDL to NUMERIC so values stay exact.
Generated: "price" DOUBLE PRECISION Edit to (before committing the migration): "price" NUMERIC(12,2)
Edge cases and what actually happens
Re-applying the migration duplicates rows
Use ON CONFLICTPlain INSERTs aren't idempotent. If a migration may run more than once (CI, re-baseline), tick ON CONFLICT DO NOTHING so existing keys are skipped. This requires a unique/PK constraint on the table to have anything to conflict on.
Date columns become serial integers
By designExcel dates arrive as numbers and infer INTEGER, which is wrong for a migration. Standardise to ISO text before generating, then add an ALTER ... USING ::date to the same migration step.
Formulas captured as stale results
Flatten firstSheetJS reads the computed value, but if the workbook wasn't recalculated the value may be stale. Run formula-to-value so the migration locks in correct, recalculated values rather than =... text or cached numbers.
Float rounding on money columns
Precision riskDOUBLE PRECISION is floating point. A 19.99 may store as 19.989999.... Hand-edit the inferred type to NUMERIC(p,s) in the migration before committing currency data.
Cross-environment dialect mismatch
Generate per engineIf local tests use SQLite but staging uses Postgres, the boolean and quoting syntax differ. Generate the migration once per engine, or keep the data dialect-neutral (avoid booleans, quote everything as TEXT) — there is no single ANSI mode.
Workbook spans multiple sheets
First sheet onlyA migration that seeds several tables from several tabs needs one generation per sheet (move each to the first sheet in turn), or pre-combine with the sheet joiner.
Large dataset exceeds code-gen cap
Upgrade requiredCode generation caps at 100 rows (Free), 1,000 (Pro), 10,000 (Pro+Media), unlimited (Developer). A big migration is truncated below your tier's cap. For very large loads, a CSV COPY/LOAD DATA outside the migration may be the better tool.
NOT NULL column meets a blank cell
Insert failsBlank cells generate NULL. Against a NOT NULL column without a default, the row's INSERT fails at apply time. Fill the cells in Excel before generating the migration.
Foreign-key order across seed files
Ordering noteThe tool seeds one table per run with no awareness of FK dependencies. Order your migration files so parent tables seed before children, or apply deferred constraints — the generator can't sequence this for you.
Frequently asked questions
Is the output suitable for a real migration file?
Yes — it's plain CREATE TABLE + INSERT SQL you paste into a Flyway/Liquibase/Alembic/Rails step or a .sql run via \i. Keeping IF NOT EXISTS and ON CONFLICT on makes the step safely re-runnable.
How do I make a migration idempotent?
Tick ON CONFLICT DO NOTHING (or it becomes INSERT IGNORE on MySQL) and leave IF NOT EXISTS on. The table won't be re-created and existing rows won't error on re-apply, provided the table has a unique/primary key.
Should I use this or a CSV bulk import for migration?
Use generated INSERTs when you want a small, reviewable, version-controlled seed (lookup tables, fixtures, ≤10K rows). Use a CSV COPY/LOAD DATA for very large one-off loads where reviewability matters less and raw speed matters more.
Why do my date columns import as numbers?
Excel dates are serial numbers and infer INTEGER. Convert them to ISO text with the date standardizer first, then add an ALTER ... TYPE date USING ...::date to the same migration.
Can the migration target multiple tables at once?
Not in one run — only the first sheet is read, producing one table. Generate each table separately (or join sheets first) and assemble the migration file by concatenating the outputs in dependency order.
How are formulas handled in a migration?
SheetJS captures the computed value, not the formula text — but recalculate the workbook first, or run formula-to-value, so the migration commits correct values rather than stale cached ones.
What about currency precision?
Decimals infer DOUBLE PRECISION (floating point), which can round money values. Edit the generated DDL to NUMERIC(12,2) for currency columns before committing.
Does the same file work on Postgres and MySQL?
Not necessarily — booleans (TRUE/FALSE vs 1/0), identifier quoting (double quotes vs backticks), and conflict syntax differ. Generate once per target engine. There is no single portable ANSI dialect.
How large a migration can I generate?
The code-generation quota caps rows at 100 (Free), 1,000 (Pro), 10,000 (Pro+Media), or unlimited (Developer); the Excel file ceiling is 5 MB to 500 MB by tier. The row cap usually binds first for migrations.
Is my staging data uploaded?
No. The workbook is parsed in your browser with SheetJS and the SQL is built locally. Nothing is sent to a server beyond an anonymous run counter you can opt out of — staging data mirroring production stays on your machine.
Can I exclude bookkeeping columns?
Yes — put imported_by, source_row (or whatever) in Exclude columns and they're left out of the DDL and INSERTs, keeping the committed migration clean.
What handles foreign-key ordering?
You do. The tool seeds one table without dependency awareness, so order the migration files so parents seed before children, or use deferrable constraints. The same engine powers the JSON to SQL converter if your source is JSON instead.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.