Import & Export

Data rarely starts life inside your database. A supplier emails a price list as an Excel sheet, the accountant wants monthly sales in a spreadsheet, and when you move a website to a new host, the whole database has to travel with it.

In this lesson you will load CSV files with LOAD DATA, handle the secure_file_priv and LOCAL security settings, check imports in a staging table, export with SELECT ... INTO OUTFILE, copy databases with mysqldump, and use Workbench and phpMyAdmin.

Choosing a method

A CSV file (comma-separated values) is plain text with one row per line and commas between the values. It is the common language between spreadsheets and databases. Here is a price list a supplier might send:

product_id,product_name,price
13,"Webcam Cover, Pack of 3",199
14,Monitor Arm,2899
15,USB Desk Fan,649

The first line is a header row with column names. Values that contain a comma are wrapped in double quotes, so the comma inside "Webcam Cover, Pack of 3" is not mistaken for a separator. When you save from Excel, choose CSV UTF-8, so the ₹ sign and Hindi text survive.

Loading a CSV with LOAD DATA

LOAD DATA reads a text file and inserts its rows at high speed, much faster than thousands of separate INSERT statements:

LOAD DATA INFILE '/var/lib/mysql-files/new_products.csv'
INTO TABLE products
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(product_id, product_name, @price)
SET price = @price,
    category = 'Accessories',
    stock = 0;

Query OK, 3 rows affected
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Line by line:

• FIELDS TERMINATED BY ',' says values are separated by commas; OPTIONALLY ENCLOSED BY '"' removes the quotes around values that have them.
• LINES TERMINATED BY '\r\n' matches files saved on Windows. Files from Linux or macOS end lines with '\n' only. Get this wrong and the last column of every row picks up a stray invisible character.
• IGNORE 1 LINES skips the header row.
• The column list maps file columns to table columns in order. @price reads the value into a user variable, and SET can transform it before it is stored. SET also fills columns the file does not have.

If Warnings is not 0, run SHOW WARNINGS; to see which rows had problems. Remember that an empty field is not NULL: it becomes an empty string in a text column and 0 in a number column (in strict mode a number column may reject it with an error instead, unless you use LOCAL or IGNORE). The file must contain \N (or, with ENCLOSED BY, the unquoted word NULL) for a real NULL.

secure_file_priv and LOCAL

Reading and writing files on the server is powerful, so MySQL limits it in two ways.

secure_file_priv restricts which folder the server may read files from and write files to. Check it first:

SHOW VARIABLES LIKE 'secure_file_priv';

An empty value means no restriction, a folder name means "only this folder", and NULL means file import and export are switched off. Linux packages often use /var/lib/mysql-files/, and the Windows installer uses an Uploads folder under C:\ProgramData\MySQL. Put your file in that folder. Otherwise you get ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. The setting can only be changed in the server's configuration file, followed by a restart. You also need the FILE privilege, which ordinary application accounts should never have.

LOAD DATA LOCAL INFILE works differently: the client program reads the file from your own computer and sends its contents to the server. This is what you need when MySQL runs on another machine, such as a hosting server. It needs no FILE privilege and ignores secure_file_priv, but it must be allowed on both sides, and it is off by default in MySQL 8.4:

mysql --local-infile=1 -u root -p shop

SET GLOBAL local_infile = 1;   -- on the server, needs an admin account

LOAD DATA LOCAL INFILE 'C:/Users/priya/Downloads/new_products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(product_id, product_name, price)
SET category = 'Accessories', stock = 0;

If either side refuses, you see ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side. It is off by default because a server you connect to could ask your client for files you did not intend to send, so switch it on only for trusted servers. Use forward slashes in Windows paths, or double each backslash.

Check imports in a staging table

Never load a file straight into an important table. Load it first into a staging table, a temporary holding table whose columns are mostly text, then check it with SQL, and only then copy the good rows across. This example works on staged price updates from a supplier. It runs in the browser too:

CREATE TABLE price_updates (
  product_id INTEGER,
  new_price VARCHAR(20)
);

INSERT INTO price_updates (product_id, new_price) VALUES
  (1, ' 849 '),
  (4, '1999'),
  (8, ''),
  (99, '500');

SELECT u.product_id, u.new_price, p.product_name,
       CASE
         WHEN p.product_id IS NULL THEN 'unknown product'
         WHEN TRIM(u.new_price) = '' THEN 'missing price'
         ELSE 'ok'
       END AS problem
FROM price_updates u
LEFT JOIN products p ON p.product_id = u.product_id
ORDER BY u.product_id;

UPDATE products
SET price = (SELECT TRIM(u.new_price) FROM price_updates u
             WHERE u.product_id = products.product_id)
WHERE product_id IN (SELECT product_id FROM price_updates
                     WHERE TRIM(new_price) <> '');

SELECT product_id, product_name, price
FROM products
WHERE product_id IN (1, 4, 8);

product_id | new_price | product_name   | problem
-----------+-----------+----------------+----------------
1          |  849      | Wireless Mouse | ok
4          | 1999      | USB-C Hub      | ok
8          |           | HD Webcam      | missing price
99         | 500       | NULL           | unknown product
(4 rows)

product_id | product_name   | price
-----------+----------------+------
1          | Wireless Mouse | 849
4          | USB-C Hub      | 1999
8          | HD Webcam      | 2199
(3 rows)

The INSERT stands in for LOAD DATA. The LEFT JOIN report finds a blank price and an unknown product number to send back to the supplier. The UPDATE uses only clean rows, TRIM() removes stray spaces, and the webcam keeps its old price (MySQL shows 849.00 and so on). For a big import, wrap the final step in a transaction.

Exporting with SELECT ... INTO OUTFILE

SELECT ... INTO OUTFILE writes a query result to a file on the server, using the same FIELDS and LINES options as LOAD DATA:

SELECT o.order_id, o.order_date, c.first_name, c.city, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-04-01'
ORDER BY o.order_id
INTO OUTFILE '/var/lib/mysql-files/april_may_orders.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

The same secure_file_priv and FILE privilege rules apply. MySQL refuses to overwrite an existing file, writes no header row, and writes NULL as \N. Putting INTO OUTFILE at the end of the statement is the preferred style today.

If you cannot write files on the server, which is normal on shared hosting, you can still build CSV lines in an ordinary query and copy the result into a spreadsheet:

SELECT CONCAT_WS(',', product_id, product_name, category, price, stock) AS csv_line
FROM products
WHERE category IN ('Audio', 'Storage')
ORDER BY product_id;

csv_line
-------------------------------------------
6,Noise-Cancelling Headphones,Audio,8999,25
7,Bluetooth Speaker,Audio,2499,40
9,1TB External SSD,Storage,6499,30
10,64GB Pen Drive,Storage,549,200
(4 rows)

This quick method does not add quotes, so it is only safe when no value contains a comma. MySQL shows the prices as 8999.00 and so on.

Copying whole databases with mysqldump

To move a complete database, use mysqldump, which you met in Users, Privileges & Backups. It writes a .sql file of CREATE TABLE and INSERT statements that any MySQL server can replay. Useful options when moving data:

mysqldump -u root -p shop products > products.sql                 # one table
mysqldump -u root -p --no-data shop > shop-structure.sql           # tables only, no rows
mysqldump -u root -p --no-create-info shop orders > orders-data.sql   # rows only
mysqldump -u root -p --where="order_date >= '2025-04-01'" shop orders > recent-orders.sql

mysql -u root -p shop_copy < products.sql                          # load it elsewhere

Inside the mysql client you can also load a file with SOURCE products.sql;. For databases of many gigabytes, MySQL Shell's dump and load utilities work in parallel and are much faster.

Import and export without commands

• MySQL Workbench: right-click a table and choose Table Data Import Wizard or Table Data Export Wizard to load or save CSV and JSON files with a preview of the columns. Server > Data Export and Data Import create and load dump files.
• phpMyAdmin: open a database, then use the Import tab for CSV or .sql files (tick the option that says the first line contains column names) and the Export tab for SQL, CSV and other formats. The largest file it can import is limited by PHP's upload_max_filesize and post_max_size settings, so use the command line for big files.

Common mistakes

• Excel changing your data. It removes leading zeros from PIN codes and phone numbers, turns long numbers into 9.81E+09, and rewrites dates in your local format. Check the CSV in a text editor before importing.
• Wrong line endings. A Windows file loaded with '\n' leaves \r on the last column, so status = 'delivered' never matches.
• Forgetting IGNORE 1 LINES. The header row is loaded as data, or the load fails on it.
• Wrong encoding. If ₹ appears as ₹, the file was not read as UTF-8. Save as CSV UTF-8 and add CHARACTER SET utf8mb4.

Key points

• LOAD DATA INFILE loads text files quickly; set FIELDS, LINES and IGNORE 1 LINES to match the file.
• Server-side file access is limited by secure_file_priv and the FILE privilege.
• LOAD DATA LOCAL reads files from the client, but must be enabled on client and server; it is off by default.
• Load into a staging table, check it with SQL, then copy the clean rows.
• SELECT ... INTO OUTFILE writes a CSV on the server; it will not overwrite a file and adds no header.
• mysqldump and mysql move whole databases or single tables; GUI tools wrap the same ideas.

CREATE TABLE customer_import (
  first_name VARCHAR(40), last_name VARCHAR(40), city VARCHAR(40),
  state VARCHAR(40), email VARCHAR(80), joined_on VARCHAR(20)
);

LOAD DATA LOCAL INFILE 'C:/Users/priya/Downloads/new_customers.csv'
INTO TABLE customer_import
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

INSERT INTO customers (customer_id, first_name, last_name, city, state, email, joined_on)
SELECT m.max_id + ROW_NUMBER() OVER (ORDER BY i.first_name, i.last_name),
       TRIM(i.first_name), TRIM(i.last_name), TRIM(i.city), TRIM(i.state),
       NULLIF(TRIM(i.email), ''), i.joined_on
FROM customer_import AS i
CROSS JOIN (SELECT MAX(customer_id) AS max_id FROM customers) AS m;