How to Convert CSV to SQL
There are two ways to get CSV data into a SQL database: generate INSERT statements (portable, slow for big files) or use the database's bulk-loader (fast, requires database access). For anything over a few thousand rows, use the bulk-loader.
Postgres: COPY
COPY users (name, email, country)
FROM '/absolute/path/to/users.csv'
DELIMITER ','
CSV HEADER;
Use \copy (lowercase) instead of COPY to read a file on the client machine via psql.
MySQL: LOAD DATA INFILE
LOAD DATA LOCAL INFILE 'users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
SQLite: .import
sqlite3 mydb.db
.mode csv
.import users.csv users
Generate INSERTs (portable)
import csv
with open('users.csv') as f:
reader = csv.DictReader(f)
for row in reader:
cols = ', '.join(row.keys())
vals = ', '.join(f"'{v.replace(chr(39), chr(39)*2)}'" for v in row.values())
print(f'INSERT INTO users ({cols}) VALUES ({vals});')
Frequently asked questions
Why is COPY so much faster than INSERT?
COPY is one transaction with minimal parsing overhead; INSERT statements are parsed and planned one at a time.
How do I handle dates?
Make sure your CSV uses ISO format (yyyy-mm-dd) — every database parses it without ambiguity.