Generating SQL INSERT Statements From a Spreadsheet

Getting a spreadsheet into a database is a job that comes up constantly and has no single right answer. Generating INSERT statements is the approach that works everywhere, because every database speaks SQL and not every database has a usable bulk loader.

Escaping is the part that matters

SQL string literals are wrapped in single quotes, so a value containing a single quote has to escape it by doubling it. O'Brien becomes 'O''Brien'. Miss that and the statement fails, or worse, succeeds having done something other than what you meant.

Backslashes need care too, because MySQL treats a backslash as an escape character inside string literals by default and standard SQL does not. A Windows path in a value can therefore mean different things in different databases.

Batching

A single INSERT with fifty thousand tuples is legal SQL and many clients refuse it. MySQL has a max_allowed_packet that defaults to a few megabytes. Some clients run out of memory building the query text. Some connection libraries have their own limits.

Splitting into statements of a hundred rows or so avoids all of that and is still far faster than one statement per row, because the round trip to the server rather than the insert itself is usually the cost.

Inferring column types

If you also need the table, the types have to come from somewhere, and the only thing available is the values. A reasonable inference widens as it goes: a column stays an integer until a value has a decimal point, then becomes a decimal, then becomes text when something arrives that is neither.

Treat the result as a first draft. It has seen the rows in your file and not the rows that will arrive next quarter. Two things are worth changing by hand almost every time: widen any text column that will grow, and check that anything holding an identifier is text rather than a number, because an identifier that undergoes arithmetic is a bug waiting to happen.

Dates

Write dates as ISO 8601 strings, yyyy-mm-dd, and let the database parse them into its own date type. Every database understands that format. Almost none agree on anything else, and a spreadsheet exports whatever the machine's locale was set to.

Generated SQL is not a pattern to copy

The statements produced here are string-concatenated with the values escaped. That is fine for an import you are running yourself against a database you control.

It is not how application code should build queries. There, the query and the data must travel separately, as a prepared statement with bound parameters, so that no amount of escaping cleverness is required and no input can change the shape of the query. Escaping is a last resort; parameterisation is the actual answer.

Alternatives worth knowing

• PostgreSQL has COPY, which reads a CSV directly and is far faster than INSERT for large files.
• MySQL has LOAD DATA INFILE, with the same trade-off.
• SQLite's shell has .import.
• All three need file access to the server or client machine, which is exactly what you do not have in a hosted database with a web console. That is when generated INSERT statements are the practical choice.