⚙️ File Operations · 9 min read

CSV to SQL: INSERT Statements That Survive Quotes and Commas

Decide first whether you need a file of INSERT statements or the data inside the database, because the two jobs have different answers. For actual loading, use the engine's bulk loader: PostgreSQL COPY, MySQL LOAD DATA INFILE, SQL Server BULK INSERT, SQLite .import. Reach for generated INSERTs only when you have no file access to the server or the file is small. Either way, never build a statement by pasting raw CSV fields into it, because CSV quoting and SQL quoting are not the same thing. Everything that breaks follows from that one mismatch.

The frustration is well documented. On r/SQLServer the question was blunt: “How are people creating tables for new CSVs without raging in frustration?” A blog post on the same problem names the cause precisely: “the people who filled the CSV files put all kinds of strange characters in them, like quotation marks and carriage returns.” Quotes and line breaks are exactly the two characters that a CSV is allowed to hide inside a field and that a naive INSERT script cannot survive.

Two different jobs disguised as one

“CSV to SQL” covers two tasks that need different tools.

The first is producing a script: a .sql file full of CREATE TABLE and INSERT statements that a colleague can run, review, or keep in version control. That is a text-generation job, and its hard part is escaping.

The second is getting rows into a database that is already running. That is a data-loading job, and its hard part is throughput and partial failure, not text. The engine has a loader built for it and it will be faster than anything you generate.

Which route fits

Why naive scripts break

Take one row, from a real customer list:

id,name,note
1,O'Brien,"Said ""ship it"" on Friday, then left"

The CSV rules come from RFC 4180. The field is wrapped in double quotes because it contains a comma, and the double quotes inside it are doubled. Correct CSV, and it parses back to exactly what the customer typed.

The SQL rules are different. ISO/IEC 9075, the SQL standard, marks a string literal with single quotes and expects a literal single quote inside to be doubled. So the value O'Brien has to be written as 'O''Brien'. Nothing in the CSV told you that, which is why the generated statement dies at the apostrophe.

Then there is the line break. A CSV field may legally contain a newline, and the note above could have been spread over three lines. A generator that splits the file on newlines will see three malformed rows where the parser sees one clean field. This is the failure mode that produces the near-useless error message about a syntax error near a comma, when the real problem started two lines earlier.

A third trap is MySQL specifically. In MySQL, a backslash inside a string literal is an escape character by default, so a Windows file path like C:\temp\new does something unexpected to the statement. Enabling NO_BACKSLASH_ESCAPES in the session removes that behaviour, but it changes how every literal in the same script is read, so set it deliberately rather than adding it while debugging something else.

Generating the script in code

Let the CSV parser and the database driver do the quoting. This is the shortest correct version, and it produces a file of INSERTs you can review:

import csv

def sql_literal(value):
    if value == "":
        return "NULL"
    return "'" + value.replace("'", "''") + "'"

with open("customers.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.reader(f))

header = rows[0]
cols = ", ".join(f'"{c}"' for c in header)
batch = 500

with open("load.sql", "w", encoding="utf-8") as out:
    for i in range(1, len(rows), batch):
        chunk = rows[i:i + batch]
        values = ",\n".join(
            "(" + ", ".join(sql_literal(v) for v in r) + ")" for r in chunk
        )
        out.write(f"INSERT INTO customers ({cols}) VALUES\n{values};\n")

Three details matter more than the rest. csv.reader is given the file object, so quoted fields containing commas and newlines are handled by the parser rather than by string splitting. The column list is written out explicitly, so the statement does not depend on column order. And the output is chunked at 500 rows, which keeps each statement inside the limits described below.

The replace call is the only escaping rule the script needs for standard SQL. If you are targeting MySQL with default settings, backslashes need attention too, and the safest answer is to use a parameterised insert through the driver instead of writing literals by hand. That also removes any chance of SQL injection from a file you did not produce.

The limits that force chunking

None of these limits is visible until a script fails halfway through, which is worse than failing at the start. Chunk from the beginning and wrap the chunks in a transaction so a failure leaves the table empty rather than half filled.

Loading it instead of inserting it

For anything above a few thousand rows, skip generation entirely.

PostgreSQL: COPY customers FROM '/path/customers.csv' WITH (FORMAT csv, HEADER true); — the loader reads the CSV format directly, header and all, and runs in one pass.

MySQL: LOAD DATA INFILE with FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' and a line terminator. Set the escapes to match how the file was written rather than assuming.

SQL Server: BULK INSERT into a table that already exists, with the field terminator and row terminator declared. For JSON that arrived in a column instead of a file, OPENJSON reads the structure without a second tool.

SQLite: the sqlite3 CLI takes .mode csv followed by .import file.csv table_name. Files that are too big for Excel are unremarkable here, because SQLite is not holding a window. It is also public domain, which makes it the least complicated way to answer a question about a large file without asking anyone for permission. There is a full walkthrough of importing CSV into SQLite.

If your question is about reading a file with SQL rather than loading it into a database, that is a different and shorter path, covered in querying a CSV with SQL directly.

NULL, empty string and type inference

A CSV cannot distinguish an empty field from a missing one. Both are nothing between two commas, and everything downstream has to guess. The guess is often wrong.

Type inference only sees the rows it sampled. A column that holds three-digit numbers for the first thousand rows and the text N/A at row 1,200 will be created as an integer and then fail on row 1,200, or worse, load as text in a table that expected numbers. When you generate the schema, scan the whole file for the widest value in each column, or declare the column as text and cast in SQL. It is a dull answer that prevents a dull afternoon.

Before you run the script

Three checks catch most failures before they reach a database. Confirm the row count you expect against the row count the CSV file actually contains, because a file with embedded line breaks will report more rows to a naive counter than it has records. Confirm the delimiter, since a semicolon-separated export loaded with a comma setting produces one very wide column, and changing a CSV delimiter first is simpler than fixing it in the schema. Then check the header against your table columns by name rather than by position.

If the script is going into version control, that also settles a practical question: split large files with something like splitting a large CSV into per-table files, so a reviewer can see what changed instead of scrolling past ten thousand identical-looking rows.