CSV to SQL

about this tool

From a spreadsheet export to a table

Paste a CSV — or open a .csv or .tsv file — pick the database, and the tool writes the SQL to load it: a CREATE TABLE with a column for each field, then INSERT statements with every row. The separator is detected (commas, semicolons, tabs or pipes), line endings are read alike whatever mix the file has, and quoted fields with commas, doubled quotes and line breaks inside them are read as RFC 4180 describes. A row of empty values is still a row; only a line with nothing on it at all is skipped. The first row names the columns, unless you say it is data — and if it looks like data, with numbers or dates in it, the tool says so, since it would otherwise not be inserted. A blank or repeated name is made unique rather than dropped, and a row with more values than the header adds a column instead of losing them.

Each column's type comes from every value in it. Whole numbers are INTEGER, or BIGINT when one is outside ±2,147,483,647, and DECIMAL when one is too big even for that. Numbers written with a decimal point are an exact DECIMAL, sized to the most digits seen before and after the point, because a price or a balance should not become a rounded floating-point number. A number in exponent form is floating point, unless the column also holds a number with more digits than floating point keeps exactly (15), in which case the column is an exact DECIMAL; one too large or too small for any database's floating point stays text. true and false, in any case, are BOOLEAN (1/0 and yes/no are not). A real calendar date written YYYY-MM-DD is a DATE, and one with a time a TIMESTAMP — a T or a space between them, seconds and up to six decimal places optional; one with a time zone offset stays text. Mixed numbers take the widest kind among them and a date mixed with a timestamp is a TIMESTAMP; any other mix is text. A number with a leading zero stays text — a postcode, an employee number, 007 — as does one with a thousands separator or spaces around it. Change any column's name or type in the list below the output; a value that no longer fits the type you chose is written as text and counted, since the database may reject it or quietly convert it.

Written the way each database reads it

The four databases disagree in the details, and the SQL follows each one's documented rules. Names are quoted with "double quotes" in PostgreSQL and SQLite, `backticks` in MySQL and [brackets] in SQL Server — only where a name needs it (spaces, capitals, or a word any of the four reserves, such as order, rank or top), or always if you ask. A name longer than the database keeps — 63 bytes in PostgreSQL, 64 characters in MySQL, 128 in SQL Server — is shortened and kept unique.

A string's single quotes are doubled, which is standard SQL. MySQL alone treats a backslash inside a string as an escape character by default, so there it is doubled, and a NUL, a carriage return, a line feed and Ctrl-Z are written as its escapes; if your server runs with NO_BACKSLASH_ESCAPES, that doubling will store two backslashes. A NUL is joined in with char(0) in SQLite and NCHAR(0) in SQL Server; PostgreSQL cannot store one at all, and the tool says so. SQL Server strings are written N'…' so text outside its code page survives. Booleans are TRUE and FALSE in PostgreSQL and MySQL and 1 and 0 in SQLite and SQL Server, whose types are BOOLEAN, BOOLEAN, INTEGER and BIT.

Text is TEXT in PostgreSQL and SQLite, a VARCHAR sized to the longest value in MySQL (TEXT past 255 characters, LONGTEXT past 16,383, and TEXT for the widest columns if a row would pass MySQL's 65,535-byte limit), and an NVARCHAR sized the same way in SQL Server (NVARCHAR(MAX) past 4,000). A primary key on text gets a sized type, since MySQL and SQL Server cannot index an unbounded one. Dates and timestamps are TEXT in SQLite; a timestamp is TIMESTAMP in PostgreSQL, DATETIME in MySQL — with its fractional seconds kept, which MySQL drops unless asked — and DATETIME2 in SQL Server. A decimal with more digits than the database's DECIMAL holds — 65 (30 after the point) in MySQL, 38 in SQL Server, 1,000 in PostgreSQL — goes in as text rather than being rounded. SQLite has no exact decimal type at all: its NUMERIC column stores decimals as floating point, so 1250.50 reads back as 1250.5, and a number with more than 15 significant digits goes in as text.

Empty cells become NULL — in a text column you can choose an empty string instead, and there a cell of spaces is a value, not empty — and a column with no empty cells is marked NOT NULL. The inserts come in batches of 100 rows a statement by default, inside a transaction by default, optionally after dropping the table if it exists and with a primary key, which the tool checks for empty or repeated values. SQL Server takes at most 1,000 rows in one INSERT, so its batches stop there, and its script starts with SET XACT_ABORT ON so a failed insert stops the whole load rather than committing the rest.

Limits

The converter reads up to 100,000 rows and 1,600 columns, and opens a file of up to 20 MB. For bigger loads a database's own bulk loader is much faster than INSERT statements: COPY in PostgreSQL, LOAD DATA in MySQL, .import in SQLite and BULK INSERT in SQL Server. Only PostgreSQL, MySQL (and MariaDB), SQLite and SQL Server are written for. To look through a CSV, sort it and search it before loading it, the CSV viewer does that. Nothing is uploaded.

questions

How are the column types chosen?

From every value in the column, not only the first. Whole numbers are INTEGER, or BIGINT past 2,147,483,647; numbers with a decimal point are an exact DECIMAL sized to the widest value; true and false are BOOLEAN; YYYY-MM-DD is a DATE and a date with a time a TIMESTAMP; anything else, or a mix, is text. A number with a leading zero, like a postcode or 007, stays text so the zero is not lost. Every choice can be changed per column.

How are quotes and backslashes in the data handled?

Each value is written as a literal the target database reads back as it was. A single quote is doubled, as standard SQL says, so O'Brien becomes O''Brien. MySQL also treats a backslash as an escape character by default, so there a backslash is doubled too, and control characters are written as its escapes; PostgreSQL, SQLite and SQL Server take a backslash literally. SQL Server strings are written N'…' so characters outside its code page survive. Names are quoted with each database's own quote.

What happens to empty cells?

An empty cell in a number, date or boolean column becomes NULL, since an empty string is not a number. In a text column it becomes NULL too unless you untick that option, in which case it is an empty string; a text cell of spaces is kept as it is. A column with no empty cells is marked NOT NULL.

Why are the inserts split into batches?

One INSERT with many rows is far faster to load than one statement a row, but a single statement with a hundred thousand rows can hit a database’s packet or parameter limits. The default is 100 rows a statement; set it to 1 for one statement a row. SQL Server allows at most 1,000 rows in one VALUES list, so batches are capped there, and the tool says so.

Is my data uploaded?

No. The CSV is parsed and the SQL written in your browser, which matters for a customer list or an export from production. For very large files, a database’s own bulk loader — COPY in PostgreSQL, LOAD DATA in MySQL, .import in SQLite, BULK INSERT in SQL Server — is faster than INSERT statements.