How to import CSV files into MySQL or MariaDB

CSV files carry rows without database column types. LOAD DATA LOCAL INFILE imports a client-side CSV into a matching MySQL or MariaDB table and exposes row counts and conversion warnings.

The LOCAL keyword makes the command-line client read the file from its own filesystem and stream the contents to the database server. Server-side LOAD DATA INFILE is different because the database server reads the file itself, which brings in server-side file privileges, allowed directories, and service sandbox rules. secure_file_priv restricts server-side loads, not LOCAL loads.

The import requires an existing database and a destination table whose columns match the CSV layout. Both the server and the client must permit LOCAL loading. An import can finish with skipped rows or converted values, so its warning report and newly added rows matter more than a successful status alone.

Steps to import CSV files into MySQL or MariaDB:

Save orders-import.csv in an existing client directory using your text editor.
orders-import.csv
```
customer_id,total,created_at
101,249.99,"2026-04-09 10:00:00"
102,89.50,"2026-04-09 10:05:00"
103,149.00,"2026-04-09 10:10:00"
```
The file is UTF-8 without a byte-order mark, with comma separators, a header, decimal points, and LF line endings including the final newline. The load statement assumes /home/dbops/imports/; for another directory, use the file's absolute path.
Open the mysql client with LOCAL loading enabled for a trusted server.

A malicious server can request other files readable by the client process when LOCAL is enabled. The dbops name represents your database account, which needs INSERT and SELECT on the target table.
```
$ mysql --local-infile=1 -u dbops -p
Enter password:
```
For MariaDB, use mariadb --local-infile=1 -u dbops -p. The client-side option is required even when the server allows LOCAL loads.

Select the target database.

mysql> USE appdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Check the server's current local_infile policy.
```
mysql> SHOW VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.002 sec)
```
The displayed value is the policy to restore after a temporary change. MySQL defaults to OFF and MariaDB defaults to ON; site policy can override either default.
Enable local_infile temporarily if its original value was OFF.

This global change permits client-side loads for other connections too. It requires administrative privileges and an approved import window. If your import account cannot change global variables, an administrator must perform this conditional step and restore the original policy afterward, including after an interrupted import.
```
mysql> SET GLOBAL local_infile = 1;
Query OK, 0 rows affected (0.000 sec)
```

Confirm that the server permits LOCAL loading.

mysql> SHOW VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.001 sec)

Inspect the target table so the column list matches the CSV layout.

mysql> DESCRIBE import_orders;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int           | NO   | PRI | NULL    | auto_increment |
| customer_id | int           | NO   |     | NULL    |                |
| total       | decimal(10,2) | NO   |     | NULL    |                |
| created_at  | datetime      | NO   |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

The CSV omits the AUTO_INCREMENT id, so the load lists only customer_id, total, and created_at. For another table, the column list follows the file's field order rather than relying on header names.

Record the target table's row count before the import.
```
mysql> SELECT COUNT(*) INTO @rows_before FROM import_orders;
Query OK, 1 row affected (0.001 sec)
```
The count difference measures this import only when other sessions are not inserting or deleting rows in the target table. The @rows_before session variable is available only in the connection where it was set.
Import the CSV file with LOAD DATA LOCAL INFILE.

This appends rows to the existing table. Repeating the load creates more rows when the table has only an automatically generated id, as shown here. A unique key can instead cause conflicting rows to be skipped; REPLACE can overwrite existing rows. After an error, warning, or interrupted connection, inspect the retained rows before retrying. Clearing the target deletes existing data and is a separate, deliberate operation.
Related: How to delete all rows from a table in MySQL or MariaDB
```
mysql> LOAD DATA LOCAL INFILE '/home/dbops/imports/orders-import.csv'
    ->   INTO TABLE import_orders
    ->   CHARACTER SET utf8mb4
    ->   FIELDS TERMINATED BY ','
    ->   OPTIONALLY ENCLOSED BY '"'
    ->   LINES TERMINATED BY '\n'
    ->   IGNORE 1 LINES
    ->   (customer_id,total,created_at);
Query OK, 3 rows affected (0.002 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
```
The IGNORE 1 LINES clause skips the header, not duplicate records. Without a header, omit that clause. For a CRLF file, use LINES TERMINATED BY '\r\n' instead.

Fields enclosed in double quotes can contain commas or line breaks; a quote inside a quoted field can be doubled. The default escape character is backslash, so \n, \t, and \N are interpreted as newline, tab, and SQL NULL, while \\ represents a literal backslash. A CSV using different escaping rules needs matching load options; quoting alone does not disable backslash interpretation.
Review warnings immediately after the load.
```
mysql> SHOW WARNINGS;
Empty set (0.000 sec)
```
A LOCAL load can convert invalid values and continue with warnings even under strict SQL modes. Warnings can also identify skipped duplicate keys, truncated values, invalid dates, row-shape mismatches, or character-set problems. A three-row increase alone does not prove that all values survived unchanged.
Restore local_infile to OFF if you enabled it for this import.
```
mysql> SET GLOBAL local_infile = 0;
Query OK, 0 rows affected (0.000 sec)
```
If it was already ON before the import, skip this step to preserve the existing policy.
Verify the increase in the target table's row count.
```
mysql> SELECT COUNT(*) - @rows_before AS imported_rows FROM import_orders;
+---------------+
| imported_rows |
+---------------+
|             3 |
+---------------+
1 row in set (0.000 sec)
```
The three data records add three rows even when the table already contains other data. A different result needs investigation before another load.

Check the imported values against the CSV.

mysql> SELECT customer_id,total,created_at FROM import_orders
    ->   WHERE customer_id IN (101,102,103)
    ->     AND created_at BETWEEN '2026-04-09 10:00:00' AND '2026-04-09 10:10:00'
    ->   ORDER BY customer_id;
+-------------+--------+---------------------+
| customer_id | total  | created_at          |
+-------------+--------+---------------------+
|         101 | 249.99 | 2026-04-09 10:00:00 |
|         102 |  89.50 | 2026-04-09 10:05:00 |
|         103 | 149.00 | 2026-04-09 10:10:00 |
+-------------+--------+---------------------+
3 rows in set (0.000 sec)

These customer and timestamp values identify the three CSV records. If those values already existed, the result can include earlier rows too; use identifiers belonging to your import when checking another file.

Written by

Mohd Shakir Zakaria

Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.