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

Generating INSERT statements is a robust way to move data when bulk import tools aren't an option. Whether you use Excel formulas for a quick fix or Python for a robust pipeline, the key is handling quotes and data types correctly.

---

Need to generate SQL quickly? HappyCSV's SQL tool handles escaping, batching, and NULLs automatically.