Skip to content
Tutorials Center
My learning

Topics

Lesson 64 of 125 ยท Changing Data

Importing and Exporting Data

Learn how to import and export SQL data. CSV with SQLite, PostgreSQL COPY, MySQL LOAD DATA and SQL Server BULK INSERT, plus INSERT scripts, JSON and fast bulk loads.

Data rarely starts or ends its life in one database. A supplier sends a price list as a spreadsheet, the finance team wants this month's orders in Excel, a mobile app needs products as JSON, and a developer needs a copy of production data to test with. Moving data in bulk is a core everyday skill, and typing thousands of INSERT statements by hand is not the way to do it.

This lesson covers the three formats you'll meet most (CSV, SQL scripts and JSON), the bulk-loading commands of SQLite, PostgreSQL, MySQL and SQL Server, and a reliable workflow for importing messy files: load into a staging table first, then clean the data with SQL. Commands that read or write files run in database tools on your computer, so they're shown read-only; everything you can do in plain SQL runs live in the examples.

CSV: the universal exchange format

CSV (comma-separated values) is plain text with one row per line and commas between the fields. Almost every tool can read and write it: spreadsheets, databases, programming languages.

TEXT
id,name,price,stock
1,Cast Iron Skillet,34.90,78
2,"Chef Knife, 20 cm",59.00,28
3,"The ""Pro"" Notebook",9.90,

The format looks trivial, but most import bugs come from a handful of details:

  • Quoting. A field that contains a comma, a double quote or a line break must be wrapped in double quotes, and a double quote inside it is written twice (""). Never split CSV lines on commas yourself.
  • Header row. The first line usually holds column names. Tell the import tool whether to skip it.
  • Empty vs NULL. CSV has no NULL. An empty field (like the last stock above) may be loaded as an empty string '' or as NULL depending on the tool. Decide which you want.
  • Encoding. Use UTF-8. Files saved by older spreadsheet software may use other encodings and turn รฉ into garbage.
  • Formats. Use ISO dates (2026-01-20) and a period as the decimal separator. Some locales export 34,90 and 20.01.2026, which won't load as numbers or dates.
  • Delimiter. Some "CSV" files use semicolons or tabs (TSV). Check before importing.

Importing CSV with database tools

Every database ships a fast bulk loader for files. These commands run in the database's command-line client or on the server, not in this browser runner.

SQL
-- SQLite command-line shell (sqlite3)
.import --csv --skip 1 products.csv products
-- If the table doesn't exist yet, leave out --skip 1: .import then creates it,
-- using the header row as column names and TEXT for every column.

-- PostgreSQL: the server reads the file
COPY products (id, name, price, stock)
FROM '/var/data/products.csv'
WITH (FORMAT csv, HEADER true);

-- PostgreSQL: psql reads the file on YOUR computer
\copy products (id, name, price, stock) FROM 'products.csv' WITH (FORMAT csv, HEADER true)

-- MySQL / MariaDB
LOAD DATA LOCAL INFILE 'products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, price, stock);

-- SQL Server
BULK INSERT products
FROM 'C:\data\products.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2);

A few things to know:

  • Server-side vs client-side. PostgreSQL's COPY ... FROM 'file' and SQL Server's BULK INSERT read files on the database server and need special permissions. For a file on your own machine, use psql's \copy, MySQL's LOCAL INFILE (which must be enabled with the local_infile setting) or SQL Server's bcp utility.
  • MySQL's secure_file_priv setting limits which server folder LOAD DATA INFILE and INTO OUTFILE may use.
  • GUI tools such as DBeaver, DB Browser for SQLite, pgAdmin, MySQL Workbench and SQL Server Management Studio all have import wizards built on the same mechanisms.

The staging-table workflow

Real files are messy: extra spaces, empty strings, prices with currency signs, dates in odd formats, duplicates. Loading them straight into a table with strict constraints either fails or, worse, succeeds with bad data. The professional approach has three steps:

  1. Load the file as is into a staging table where every column is TEXT and nothing is required.
  2. Inspect and clean it with SQL: trim, convert, validate.
  3. Copy only the good rows into the real table with INSERT ... SELECT.

The example below starts at step 2: price_import holds rows exactly as a supplier's CSV delivered them.

SQL Cleaning a staging table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Three rows can be fixed automatically (extra spaces, a dollar sign, a decimal comma), while two need a human: one has no name, and n/a is not a price. (The GLOB test only checks that the price starts with a digit; a production import would validate more strictly.) An empty stock becomes NULL through NULLIF. Step 3 copies only the rows marked ok:

SQL Loading the clean rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Three products arrive, with correct types and an empty stock turned into 0, while the two rejected rows stay in the staging table for someone to fix. (The category is fixed at 1 to keep the example short; a real import would look it up with a join.) After every import, compare row counts: rows in the file, rows loaded, rows rejected. They must add up.

Exporting to CSV

Exporting is the mirror image. Database tools write any query result to a file:

SQL
-- SQLite shell
.headers on
.mode csv
.once orders_2025.csv
SELECT id, customer_id, ordered_at, status FROM orders ORDER BY id;

-- PostgreSQL (psql)
\copy (SELECT id, customer_id, ordered_at, status FROM orders ORDER BY id) TO 'orders_2025.csv' WITH (FORMAT csv, HEADER true)

-- MySQL: writes on the server, inside the secure_file_priv folder
SELECT id, customer_id, ordered_at, status
FROM orders
ORDER BY id
INTO OUTFILE '/var/lib/mysql-files/orders_2025.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

When no tool is at hand, you can build CSV lines with SQL itself. The key is quoting text correctly: wrap it in double quotes and double any double quote inside it.

SQL Building CSV with SQL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The result is a header line followed by one valid CSV line per product, with prices always formatted to two decimals. The inner query is wrapped in a subquery so its ORDER BY applies only to the data rows; in a plain UNION ALL, an ORDER BY would sort the header in with them (see UNION).

Make exports reproducible. Always add an ORDER BY, so the same data produces the same file, and diffs between two exports show real changes. Include a header row, export dates in ISO format, and save the file as UTF-8. If Excel will open it, beware that it drops leading zeros (00742 becomes 742) and reformats anything that looks like a date.

SQL scripts: dumps and generated INSERTs

A SQL dump is a text file of CREATE TABLE and INSERT statements that rebuilds a database when you run it. It's the most portable way to copy a small or medium database, and the usual way to back one up:

Terminal
# SQLite: dump and restore
sqlite3 shop.db .dump > shop.sql
sqlite3 copy.db < shop.sql

# PostgreSQL
pg_dump --dbname=shop --file=shop.sql
psql --dbname=shop_copy --file=shop.sql

# MySQL
mysqldump shop > shop.sql
mysql shop_copy < shop.sql

(The sample databases on this site are exactly such scripts: open /datasets/shop.sql to see one.) You can also generate INSERT statements for selected rows with a query. SQLite's quote() function formats a value as an SQL literal: it adds single quotes around text, doubles any quotes inside, and writes NULL for missing values. That makes it a safe building block:

SQL Generating INSERT statements ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Notice the last statement: Yara Petrov has no email, and quote() wrote a bare NULL, not the text 'NULL'. PostgreSQL offers quote_literal() and quote_nullable() for the same job; in MySQL use QUOTE(). Never build literals by concatenating raw values with quotes yourself; a name like O'Brien will break the script, and in application code that mistake becomes SQL injection.

JSON: export and import

Web APIs and JavaScript apps speak JSON. With SQLite's JSON functions, a query can return a complete JSON document: json_object() builds one object per row, and the aggregate json_group_array() collects them into an array.

SQL Exporting JSON ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

One row, one column: a JSON array with the five Kitchen products, ready to send to a browser. json('true') produces a real JSON boolean instead of the string "true". PostgreSQL does the same with json_agg() and json_build_object(), MySQL with JSON_ARRAYAGG() and JSON_OBJECT(), and SQL Server with FOR JSON PATH.

Importing works in reverse. json_each() turns a JSON array into rows, one per element, and ->> extracts fields as SQL values:

SQL Importing JSON

Press Run (or Ctrl + Enter) to see the result here.

Three reviews are inserted; the third has no "text" key, so its comment is NULL. In an application, the JSON string would arrive as a parameter (json_each(?)), which makes importing an API payload a single statement.

Bulk loading tips

When you load millions of rows, speed matters. In rough order of impact:

  1. Use the native bulk loader (COPY, LOAD DATA, BULK INSERT, .import). It's usually many times faster than any INSERT statements.
  2. Use one transaction, or a few large ones. Committing after every row makes the database wait for the disk each time. See Transactions.
  3. Batch your INSERTs if you must use them: hundreds of rows per multi-row VALUES, through prepared statements.
  4. Create indexes after loading. Maintaining an index row by row is slower than building it once at the end. For big loads into an empty table, drop secondary indexes, load, then re-create them (see Indexes).
  5. Load into staging first, then validate and copy, as shown above. A rejected row costs you nothing.
  6. Check foreign keys deliberately. Load parent tables before child tables. PostgreSQL can defer constraint checks to the end of a transaction, and MySQL loads often run with SET foreign_key_checks = 0. If you switch checks off, verify the data afterwards.
  7. SQLite one-off loads can use PRAGMA journal_mode = OFF; and PRAGMA synchronous = OFF; for extra speed, but a crash during the load can corrupt the file. Only do this for a database you can rebuild from scratch.
  8. Verify. Count rows, sum a numeric column, and compare both with the source.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How must the text Mugs "Classic", set of 2 appear as a CSV field?

    A field containing a comma or quotes is wrapped in double quotes, and each double quote inside is written twice.
  2. 2. Why load a messy CSV into a staging table with only TEXT columns first?

    Staging tables accept everything, so you can find and fix problems with queries instead of fighting failed imports.
  3. 3. What does SELECT quote(NULL), quote('O''Brien'); return in SQLite?

    quote() writes NULL as a bare keyword and returns text as a valid SQL literal, doubling the inner single quote.
  4. 4. You need to import a CSV file from your laptop into a remote PostgreSQL server. Which command fits?

    COPY ... FROM 'file' reads the server's disk. psql's \copy reads the file on your machine and streams it to the server.
  5. 5. Which change usually speeds up a large INSERT-based load the most?

    Each commit waits for the disk. One transaction (or a few large ones) removes thousands of those waits; indexes are best built after loading.

Summary

  • CSV is the common exchange format: mind quoting (""), header rows, empty vs NULL, UTF-8 and ISO dates.
  • Bulk loaders: SQLite .import, PostgreSQL COPY / \copy, MySQL LOAD DATA, SQL Server BULK INSERT / bcp.
  • Import messy data into a staging table, clean it with SQL, then INSERT ... SELECT the good rows and check the counts.
  • Export with .mode csv, \copy ... TO or INTO OUTFILE; always ORDER BY for reproducible files.
  • SQL dumps (.dump, pg_dump, mysqldump) copy whole databases; quote() safely generates INSERT statements.
  • json_group_array(json_object(...)) exports JSON; json_each() imports it.
  • For speed: native loaders, one transaction, indexes after loading, and always verify.

Next: CREATE TABLE, where you'll start designing your own tables.