How to import a CSV into a database

Clean the CSV before you load it

Most failed imports are a file problem. Check these first.

1. Encoding. Save as UTF-8. The converter above removes a byte order mark at the start of the file; we confirmed the first column name came out clean. We did not test how each loader treats one, so strip it if a loader complains about the first column.
2. Delimiter and decimal comma. A semicolon-delimited file with 9,99 in it is read as text, not a number: the converter wrote '9,99' and '1.050,00' in quotes. Change the decimal separator to a point before you load into a numeric column.
3. One header row, unique names. The converter keeps a duplicate name as it is and the database then refuses the statement. Letters outside A to Z, digits and underscore become _, so First Name turns into First_Name.
4. Line breaks inside cells. The converter splits a quoted cell with a line break into two rows. Remove them first, or use a loader, which reads them correctly.
5. Dates as 2026-10-24. 24/10/2026 was rejected by MySQL ("Incorrect date value") and by PostgreSQL at its default DateStyle (ISO, MDY).
6. Leading zeros. If a spreadsheet already turned 02134 into 2134 before you saved the file, no importer can bring the zero back. Open the CSV in a text editor and look.
7. Booleans in the form your database wants. MySQL accepted 1 and 0 and rejected true. PostgreSQL accepted true and rejected 1.
8. Empty means NULL. In the converter an empty cell becomes NULL, and so does "". The word NULL becomes the four-letter text 'NULL'. In PostgreSQL's CSV mode an unquoted empty string is NULL by default and a quoted "" is an empty string.

Create the table first

Neither the converter nor the loaders guess your types well enough to trust. Make the table yourself, and use text for anything with leading zeros or a code:

CREATE TABLE customers (
  name  varchar(20),
  zip   varchar(10),
  phone varchar(30),
  sku   varchar(30),
  qty   int
);

That statement ran unchanged on MySQL and PostgreSQL. In our test, a zip column of type int stored 02134 as 2134 on both, without an error.

Method A: generated INSERT statements

Paste the CSV into the converter, set the table name, copy the output. Then:

1. Wrap it in one transaction. MySQL: SET autocommit=0; first and COMMIT; last. PostgreSQL and SQLite: BEGIN; first and COMMIT; last. If one row fails in PostgreSQL, the rows before it are rolled back with it.
2. On MySQL, set SET SESSION sql_mode = CONCAT(@@sql_mode, ',NO_BACKSLASH_ESCAPES');. The converter does not double backslashes. Without that mode, C:\temp\new was stored as a tab and a line feed, and a cell ending in a backslash broke the statement.
3. On PostgreSQL, swap the backticks: sed 's//"/g' out.sql > out_pg.sql`. This also changes any backtick in your data.
4. Run the file. In mysql: mysql -u user -p dbname < out.sql. In psql: psql -d dbname -f out_pg.sql. In SQLite: sqlite3 app.db < out.sql. SQLite accepts the backticks as they are.

Method B: the database's own loader

Use these when the file is large or has quoted line breaks. We ran all three, and all three kept "Line one<newline>Line two" as one cell.

MySQL

LOAD DATA INFILE '/var/lib/mysql-files/data.csv' INTO TABLE customers
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
  LINES TERMINATED BY '\n' IGNORE 1 LINES;

The server reads that file itself, and only from the directory in secure_file_priv; on our test server that was /var/lib/mysql-files/. The empty ESCAPED BY '' matters: with the default, C:\temp\new was again stored as a tab and a line feed. Add LOCAL to read a file from your own machine, if both client and server allow it.

PostgreSQL

\copy customers FROM 'data.csv' WITH (FORMAT csv, HEADER)

The server-side form is COPY customers FROM '/path/data.csv' WITH (FORMAT csv, HEADER);.

SQLite

sqlite3 app.db ".import --csv --skip 1 data.csv customers"

Create the table first. If it does not exist, SQLite makes it from the first row, which is not what you want with a header you plan to skip.

SQL Server (2017 and later, from the documentation, not run here)

BULK INSERT dbo.customers FROM 'C:\data\data.csv' WITH (FORMAT = 'CSV', FIRSTROW = 2);

Check the import worked

1. Count rows. SELECT COUNT(*) FROM customers; should equal the CSV's data rows. wc -l over-counts if any cell holds a line break, so count with a CSV-aware tool.
2. Look at the risky columns. SELECT zip FROM customers WHERE zip LIKE '0%' LIMIT 5; proves the zeros survived. SELECT COUNT(*) FROM customers WHERE phone IS NULL; shows how many empty cells became NULL.
3. Round trip. Export the table back to CSV and compare it with the original in the CSV diff. Added, removed and changed rows are reported by key, so a silent truncation shows up.

Errors you may hit, and what they mean

These are the messages we got.

• ERROR 1366 ... Incorrect integer value: 'true' for column 'active' (MySQL): text in a number or boolean column. Change the CSV value to 1 or 0.
• ERROR 1292 ... Incorrect date value: '24/10/2026' (MySQL) and date/time field value out of range (PostgreSQL): reformat to 2026-10-24.
• ERROR 1064 ... near ''back\')' (MySQL): a backslash at the end of a value. See step 2 of Method A.
• ERROR 1110 ... Column 'id' specified twice (MySQL) and column "id" specified more than once (PostgreSQL): duplicate header names.
• column "active" is of type boolean but expression is of type integer (PostgreSQL): a bare 1 or 0 going into a boolean column. Use true and false, or cast.
• syntax error at or near ""` (PostgreSQL): backticks in the SQL.
• invalid input syntax for type integer: "n/a" (PostgreSQL): a placeholder word in a number column. Replace it with an empty cell.
• ERROR 3948 ... Loading local data is disabled (MySQL): see the FAQ below.
• SQL Server error 10738: more than 1,000 rows in one INSERT ... VALUES.

Sources

Checked on 24 October 2026, all opened that day: MySQL LOAD DATA, MySQL InnoDB bulk loading, MySQL string literals, MySQL LOAD DATA LOCAL security, MySQL packet size, PostgreSQL COPY, PostgreSQL populating a database, SQLite shell, SQLite limits, SQL Server table value constructor, SQL Server BULK INSERT.