CSVFormat CSV Converter

CSV to SQL Quietly Turns Your Zip Codes Into Integers

CSVFormat — CSV Formatter & Converter Guides · Updated 2026-10-13 · All guides

The decision that rewrites your data

The SQL generator does not quote everything. It looks at each cell and asks one question, straight from the shipped source:

const isNum = v => v !== "" && v.trim() !== "" && Number.isFinite(Number(v));

Unquoted when true, single-quoted when false. One line, and it is the source of every problem below, because Number() accepts a much wider set of strings than "this value is really a number".

Leading zeros are deleted

IN   zip,n          OUT  INSERT INTO records (zip, n) VALUES (1234, 'x');
     01234,x

Number("01234") is 1234, so the cell is unquoted and the padding is gone. Zip codes, UK postcodes, bank sort codes, ISBNs, product SKUs, employee IDs — any identifier with semantic leading zeros — arrive in the database as a different number. Even if your column is TEXT, Postgres and MySQL both coerce an unquoted numeric literal, so '01234' becomes 1234 in the table.

The same thing happens through JSON quoting, which is the workaround people try first:

IN   zip           OUT  INSERT INTO records (zip) VALUES (1234);
     "01234"

Quoting in the CSV does not help. The check runs on the string after the quotes are already removed. There is no marking-a-column-as-text option, because there are no options.

IDs above 2^53 change value

IN   id                          OUT  ... VALUES (9007199254740992);
     9007199254740993

Off by one, silently. Number() is a float64, so anything past Number.MAX_SAFE_INTEGER gets rounded. Snowflake IDs, Discord/Twitter-style snowflakes, Stripe external IDs, nanosecond timestamps, and composite order numbers all live in that zone. The generated SQL is a valid number that is not your ID. A BIGINT column will happily store the wrong one.

Hex and scientific notation get converted

IN   h     OUT  INSERT INTO records (h) VALUES (31);      // Number("0x1F") === 31
     0x1F

IN   v     OUT  INSERT INTO records (v) VALUES (100000);  // Number("1e5") === 100000
     1e5

0x1F is a colour component, a flag word, a status code — and it becomes 31. 1e5 is often literally the text someone wrote. Both are unquoted because Number() is generous about what it parses. Also converted: 007 → 7, -0 → 0, " 42 " → 42.

The guard rails that do exist: NaN and Infinity are caught by Number.isFinite and come back quoted ('NaN', 'Infinity'), which is right. So the function is careful about the values humans think of as non-numeric and careless about the strings that look numeric but mean something else.

The fix, in the order that actually works

  1. Never let this be the last step before a load. Generate, then read the file back and diff the specific columns you care about against the source. A spreadsheet conditional-format or a grep -c on a known zero-padded value tells you in seconds.
  2. Rename the header to something non-numeric. The generator only unquotes when the value parses, so fixing the value is the lever — but renaming a header (zip → zip_code) is how you flag which columns need eyeballing during the diff. Headers with spaces or hyphens get auto-quoted correctly ("First Name" came back quoted), so header hygiene is free.
  3. Set the target column type before generating, not after. If the column is TEXT, you still need the value quoted in the SQL — the generator does not know your schema and cannot help you here.
  4. Use a real loader for anything you care about. COPY ... FROM CSV (Postgres), LOAD DATA INFILE / mysqlimport (MySQL), SQLite's .import. They take types from the table, stream the file, and do not run your ID through a float. This tool is fine for a 50-row lookup table and for reading a file's shape; it is not an ETL path.

What the generator does get right

Worth knowing, because "it mangles numbers" is not the same as "it is unsafe to use".

Injection is handled. Single quotes are doubled and the whole cell is wrapped, so the classic attempt stays inert data:

IN   note
     "); DROP TABLE users; --
OUT  INSERT INTO records (note) VALUES ('); DROP TABLE users; --');

IN   note        OUT  ... VALUES ('O''Brien');
     O'Brien

Formula-injection payloads (=cmd|calc!A1) are also just data in the SQL path — quoted, escaped, harmless.

Column names are quoted when they need to be. First Name → "First Name", and reserved-word-ish names pass through unquoted, which is a thing to check per-dialect but not a mangling of your data.

Empty cells become '', not NULL. 1, gives VALUES (1, ''). Explicit empty string, never a missing column or a NULL. Whether that is what you wanted depends on your schema, but at least it is consistent and visible.

The ragged-row warning appears in the SQL itself. This matters more here than in JSON, because the generator does not pad or trim to match the header — it emits exactly what it parsed:

IN   a,b,c          OUT  INSERT INTO records (a, b, c) VALUES (1, 2);
     1,2                 -- WARNING: 1 row(s) did not match the header column count

Three named columns, two values. In Postgres that is INSERT has more attributes than target relation — an error. In MySQL it is an error. So the malformed statement fails loudly at the database rather than silently shifting your columns, and the -- WARNING line is sitting right there in the file you are about to run. Keep the warning in the file; do not strip comments from generated SQL before loading it.

Note the two directions are equally broken but look different: too few values (a, b, c) VALUES (1, 2) and too many (a, b) VALUES (1, 2, 3). Both produce invalid SQL and both warn. The count in the warning is the number of rows affected, not the number of extra or missing cells, so 3 row(s) on a 3-row file means every row, which is usually a wrong-delimiter problem rather than a bad-data problem.

How to re-run this

Everything above came from the shipped csvToSql, extracted from the deployed dist/index.html and run under Node by the repo's fuzz harness — not a copy of the logic:

node -e 'process.env.FUZZ_APP_ROOT="/tmp/fuzzroot";
const {loadApp}=require("./scripts/fuzz/load.js");
const A=loadApp("csvformat",["csvToSql"]).api;
A.__setInput("in","zip,id\n01234,9007199254740993");
A.__setInput("out","");
A.csvToSql();
console.log(A.__readOutput("out"));'
# INSERT INTO records (zip, id) VALUES (1234, 9007199254740992);

The 9007199254740992 is not a typo in this page — it is the tool's output, and the mechanism is one line of plain JS:

node -e 'console.log(Number("01234"), Number("0x1F"), Number("9007199254740993"))'
# 1234 31 9007199254740992

Not verified

I did not run the generated statements against a live Postgres, MySQL, or SQLite instance — the type-coercion claims about '01234' landing as 1234 in a TEXT column, and the specific error text for ragged inserts, are stated from how those databases handle unquoted numeric literals and attribute-count mismatches, not observed here. Verify on your own engine before relying on either direction. I also did not test dialect-specific identifier quoting (backticks for MySQL, no-identifier-quoting modes) or multi-megabyte files.

Developer Sponsor / Partner
Copied to clipboard!