Convert CSV to SQL

It is worth saying this before anything else, because for a large share of the people who search for this conversion the answer is that they do not need it. Postgres has COPY, MySQL has LOAD DATA INFILE, SQLite has .import, SQL Server has BULK INSERT, and every one of them reads a delimited file directly, applies the column types once, and writes in blocks. On anything above a few thousand rows that route beats a script of INSERT statements comfortably.

What makes this conversion the right choice is a constraint, not a preference. The bulk loaders need the file to be somewhere the server can reach, and often need a privilege the account does not have. If all you have is a query window — a managed database console, a migration tool, a support ticket that will be run by someone else — then statements are the only currency available, and generating them by hand for four hundred rows is a poor use of an afternoon.

The output is a file of INSERT statements, one per data row, each naming the table and listing the columns taken from the header. Nothing precedes them. That is a refusal rather than an omission: a CSV records neither types nor lengths nor keys nor nullability, so a generated schema would be a set of guesses presented with the authority of a DDL statement.

The cost of a wrong guess is asymmetric. A bad row is one bad record and a delete fixes it. A VARCHAR(50) inferred from the longest value in a sample truncates addresses for two years before anybody notices. The assumption here is that you designed the table, which is true of almost everyone who wants this file, and that what you want automated is the tedious part. If you do need a table too, write the CREATE statement yourself and paste it at the top; the generated statements run against it unchanged.

The name of the CSV, with its extension removed, is used as the table. It is sanitised on the way: anything outside letters, digits and underscore becomes an underscore, and a name beginning with a digit gets one prefixed, since an identifier cannot start with a number. A file called `2024 orders.csv` therefore produces inserts into `_2024_orders`.

The same sanitising is applied to the column names taken from the header row, which is the part more likely to surprise you. A heading reading `Order total (£)` becomes `Order_total____` and will not match anything in your table. Tidy the header row in the source to match the target columns exactly, rename the file to the target table, and the generated file needs no editing at all — which is worth two minutes, because the alternative is a find-and-replace across fifty thousand lines.

This is the difference between converting a CSV and converting a spreadsheet, and it is the thing most likely to go wrong. A spreadsheet cell knows whether it holds text or a number. A CSV field is characters, so the parser infers, and whatever it infers is what the SQL writer sees: a value read as a number is written bare, a value left as text is written quoted.

The practical consequence is that identifier columns lose their quotes and their padding at the same time. A postcode of 01234 becomes `1234`, unquoted; a phone number without spaces becomes a large integer; a part number like `1e5` becomes `100000`. Inserted into a VARCHAR column those land as unpadded strings without error, which is the worst possible outcome because nothing complains. Export identifier columns quoted or with a non-numeric prefix, or check them in the generated file before running it — reading the first statement takes ten seconds and shows the table, the column list and one example of every value type at once.

A blank between two delimiters becomes the keyword NULL rather than an empty string. That is almost always the right reading of a gap in an export, and it is the reading a NOT NULL constraint will reject loudly rather than accept quietly, which is the behaviour you want.

Where a blank genuinely meant an empty string, the file never held the distinction and no converter can recover it. If your table has a column where "" and NULL mean different things — an optional free-text field, most commonly — encode the difference in the CSV before converting, or add a COALESCE in a staging step. The same applies in reverse: a column where every gap should become a default is better handled by omitting the column from the insert than by inserting NULL over the default.

Numbers and booleans are written bare. Text is wrapped in single quotes with any apostrophe inside it doubled, so O'Brien becomes 'O''Brien'. That is the ANSI convention and every major engine reads it the same way.

Backslash escaping is deliberately absent. It is a MySQL extension whose behaviour depends on the NO_BACKSLASH_ESCAPES server setting rather than on the statement itself, which means a file using it can run correctly on one server and corrupt data on another with the same version. Doubling the quote works everywhere, and the resulting file can be checked into version control and replayed against several environments without carrying an assumption about any of them.

A field reading `true` or `false` in the CSV is read as a boolean and written as the bare literal TRUE or FALSE. Postgres takes that directly, MySQL treats the words as aliases for 1 and 0, and SQLite has accepted them for years.

SQL Server does not. Its bit type wants 1 and 0, and a statement containing TRUE fails to parse — which you will discover on the first statement if you are lucky and on the four-hundredth if the boolean column has a run of blanks at the top. If that is the destination, do the replacement before running anything, and check first that no text column happens to contain the word on its own.

Nothing in this conversion interprets a date. A field reading `2024-03-11` is not numeric, so it stays a string and is written quoted, and a field reading `11/03/2024` does the same. That is better than it sounds: a quoted ISO date inserts correctly into a date column in every engine here, which makes a well-formed CSV the easiest of all the sources to load.

It is also where the ambiguity travels untouched. `11/03/2024` is the eleventh of March or the third of November depending on who exported it, and the file does not say. The database will parse it according to its own locale settings and will not warn you that it chose. If the CSV came from a system whose date format you cannot verify, load into a text staging column and convert explicitly, where the assumption is written down instead of inherited.

The statements are emitted individually, with no BEGIN, no COMMIT and no multi-row VALUES batching. Run as-is against a remote server that is fifty thousand round trips and fifty thousand implicit commits, which is slow enough to notice and leaves a half-loaded table if it fails in the middle.

Two lines fix both problems: wrap the file in BEGIN and COMMIT and the whole load becomes atomic and considerably faster. The file is also larger than the data it contains, because the column list is repeated on every line — a wide table with long column names can produce a SQL file several times the size of the CSV it came from, which is worth knowing before emailing it anywhere.

Parsing the file and writing the statements are both plain JavaScript running here, so nothing is uploaded and there is no queue; what bounds the job is the free tier’s 100 MB per file and then your own memory. That matters for this pair in particular: a CSV heading into a database is usually customer records, transactions or a correction list, and pasting one into a website to be processed is the thing most data policies are written to stop.

The ceiling is the whole table being held at once, which puts tens of megabytes comfortably in range. Above that the answer is the bulk loader from the first section rather than a bigger converter, and it is a better answer anyway.