Generate SQL INSERT Statements from CSV

You have a CSV file. You have a database table. You need the data in the table.

Sometimes you can't use LOAD DATA INFILE or a bulk import wizard (permissions issues, no direct server access, or you just need a script to run later).

In these cases, generating a list of SQL INSERT statements is a lifesaver.

The Goal

Input (CSV):

id,name,role
1,Admin,superuser
2,User,editor

Output (SQL):

INSERT INTO users (id, name, role) VALUES (1, 'Admin', 'superuser');
INSERT INTO users (id, name, role) VALUES (2, 'User', 'editor');

Why Do This?

• Portability: You can send the .sql file to anyone, and they can run it.
• Version Control: You can commit the seed data script to Git.
• Safety: You can review exactly what queries will run.
• Flexibility: You can modify the values (e.g., add a created_at timestamp) during generation.

Method 1: Online Converter (Fastest)

For quick, one-off tasks, use a tool.

1. Upload your CSV.
2. Specify the Table Name (e.g., users).
3. Download the .sql file.

-> Convert CSV to SQL

Method 2: Excel Formula Hack

If you are already in Excel, you can write a formula to generate the SQL.

Assuming:

• Col A: ID
• Col B: Name
• Col C: Role

In Column D, write:

="INSERT INTO users (id, name, role) VALUES (" & A2 & ", '" & B2 & "', '" & C2 & "');"

Note: Pay attention to the single quotes ' around text values (B2 and C2).

Drag the formula down. Copy Column D. Paste into your SQL editor.

Pros: No coding needed. Cons: Tedious with many columns; hard to handle quotes inside the text (e.g., "O'Connor").

Method 3: Python Script (Best for Automation)

This handles special characters (like quotes) correctly.

import csv

table_name = "users"
csv_file = "data.csv"
sql_file = "output.sql"

with open(csv_file, 'r') as f:
    reader = csv.reader(f)
    headers = next(reader) # Get column names

    with open(sql_file, 'w') as out:
        for row in reader:
            # Escape single quotes in data
            safe_row = [val.replace("'", "''") for val in row]

            # Wrap text in quotes, leave numbers alone (simplified logic)
            # Better: check data type or just quote everything if DB allows implicit conversion
            formatted_values = [f"'{v}'" for v in safe_row]

            vals = ", ".join(formatted_values)
            cols = ", ".join(headers)

            sql = f"INSERT INTO {table_name} ({cols}) VALUES ({vals});\n"
            out.write(sql)

print("Done!")

Tips for Better SQL Generation

1. Handle Quotes

If a name is O'Reilly, your SQL will break: 'O'Reilly'. You must escape it: 'O''Reilly' (standard SQL) or 'O\'Reilly' (MySQL). The Python script above handles the standard double-single-quote escape.

2. Batch Inserts (Faster)

Instead of 1,000 separate INSERT statements, group them:

INSERT INTO users (id, name) VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Bob');

This is much faster for the database to execute.

3. Handle NULLs

Empty string '' is not the same as NULL. If your CSV has empty cells that should be NULL in the DB, your script needs logic: if val == "": val = "NULL" (and don't wrap "NULL" in quotes!).

4. Date Formats

Ensure your CSV dates match your database format (usually YYYY-MM-DD). If your CSV has 12/31/2024, the database might reject it.

The Bottom Line

Generated INSERT statements are useful when a bulk loader is unavailable or when the output must be reviewed before execution. Correct escaping, explicit column names, deliberate NULL handling and a transaction around the import matter more than the method used to generate the statements.

Treat generated SQL as code

Do not execute a generated script against production simply because it parses. Review the target table and columns, run the script in a disposable or staging database, and compare inserted counts with the CSV. Wrap a manageable batch in a transaction so it can be rolled back when a constraint or mapping is wrong.

Database dialects differ in identifier quoting, boolean literals, date handling and escape rules. A script generated for PostgreSQL may not be correct for MySQL, SQL Server or SQLite. Select the intended dialect and inspect representative values containing apostrophes, line breaks, backslashes and non-ASCII text.

For untrusted data, parameterized application inserts or the database’s supported bulk loader are safer than constructing SQL text. Generated statements should never interpolate raw values without correct literal escaping.

Before generation, validate that the CSV headers map to the intended columns and reject unknown names. Do not derive a table name directly from an uploaded filename. After a staging run, query the inserted rows and reconcile null counts, date ranges and unique keys with the source. Retain the original CSV and generated script until the migration has been accepted, then handle both according to the data-retention policy.

---

Need to generate SQL quickly? HappyCSV's SQL tool quotes identifiers and string literals and emits one INSERT per row. Review the SQL dialect, inferred empty values, and every statement before execution.