Importing CSV into a SQL database

Getting a spreadsheet export into a database table sounds trivial until the first import fails on row 3,412. This guide covers the main approaches and the problems they share.

Two ways to import

There are two broad ways to get CSV rows into a SQL table.

1. Generate INSERT statements and run them like any other SQL script. This works with every database client, can be reviewed line by line, and is easy to commit to a repository as seed data. The CSV to SQL generator produces these statements in your browser.
2. Use the database's bulk loader, which reads the file directly. This is much faster for large files, but each database has its own command and options.

Step 1: create the table first

Neither approach should decide your schema for you. Create the target table with explicit types and constraints before importing:

CREATE TABLE customers (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  country    char(2),
  signed_up  date
);

Choosing types up front means the database rejects bad data, such as a date column containing N/A, instead of storing it as text you will have to clean later.

Step 2: generate or load the data

With INSERT statements, each CSV row becomes one statement:

INSERT INTO "customers" ("id", "name", "country", "signed_up")
VALUES ('1', 'Ada', 'GB', '2026-01-15');

The values are quoted strings. When the column types already exist, PostgreSQL, MySQL, SQLite and SQL Server convert well-formed strings such as '1' and '2026-01-15' to the column type, although each system has its own rules for what it accepts. Single quotes inside values are doubled, so O'Brien becomes 'O''Brien', which prevents the value from ending the string early.

For large files, the bulk loaders are:

• PostgreSQL: COPY customers FROM '/path/file.csv' WITH (FORMAT csv, HEADER true); on the server, or \copy in psql to read a file from your own computer.
• MySQL: LOAD DATA INFILE or LOAD DATA LOCAL INFILE, with FIELDS TERMINATED BY ',' and IGNORE 1 LINES for the header.
• SQLite: in the sqlite3 shell, .mode csv followed by .import file.csv customers.
• SQL Server: BULK INSERT with FORMAT = 'CSV', or the bcp command-line utility.

As a rough guide, INSERT scripts are comfortable for a few thousand rows. Beyond tens of thousands of rows, bulk loading is typically far faster.

Step 3: run it safely

Wrap the import in a transaction so a failure part-way through does not leave half the data behind:

BEGIN;
-- INSERT statements here
COMMIT;

Run the script against a development copy first, then compare row counts with the CSV file. In SQL Server the transaction syntax is BEGIN TRANSACTION and COMMIT TRANSACTION.

Common import errors and fixes

Empty cells and NULL

CSV has no way to express NULL. An empty cell is just an empty string, and a generated INSERT writes it as ''. For a text column that stores an empty string; for an integer or date column most databases reject it. Decide what empty means for each column. You can convert empty strings to NULL after loading with UPDATE customers SET country = NULL WHERE country = ''; or, with bulk loaders, use options such as PostgreSQL's NULL ''.

Number and date formats

1,234.50, 1.234,50 and $1234.50 are all common in spreadsheet exports, and none of them is a valid SQL numeric literal. Dates such as 03/04/2026 are ambiguous between month-first and day-first formats. Normalise these columns before importing: plain digits with a dot as the decimal separator, and ISO 8601 dates in YYYY-MM-DD form.

Column count mismatches

A row with an extra comma, such as an unquoted address containing a comma, has more fields than the header. The generator reports the row number so you can fix it. Make sure the exporting application quotes values that contain commas, quotes or line breaks.

Encoding problems

Characters such as é or ü appearing as é mean the file was written in one character encoding and read in another. Save the CSV as UTF-8, and make sure the database and client connection use UTF-8 as well.

Duplicate keys

If the table has a primary key and the CSV contains the same ID twice, the second INSERT fails. PostgreSQL offers ON CONFLICT DO NOTHING or DO UPDATE, MySQL has INSERT IGNORE and ON DUPLICATE KEY UPDATE, and SQL Server uses MERGE. Decide which behaviour you want before importing.

Security note

Generated statements quote both identifiers and values, so the text of a cell cannot break out of its string literal. Still, treat imported data as untrusted: review the script before running it with administrative privileges, and never execute SQL produced from files you do not recognise.

If your source data is JSON rather than CSV, convert it first with the JSON ↔ CSV converter, and read CSV pitfalls to avoid problems with quoting and encodings.

Tools for this task