How to Import CSV in MariaDB

Aus MattWiki

This article describes how to proceed to import CSV files into MariaDB.

Probably this should work with MySQL as well, but I did not test it.

General Informations and Assumptions

Separator character is ";"

Text fields are enclosed with double quotes.

For the examples below CSV file needs to have exactly the same fields in the same order as the table, in which it will be imported.

The file is created on Windows, which results in CRLF on the end of every line.

If target table allows NULL values and CSV should also contain these NULL values, then they have to be expressed as \N

If CSV file should be imported into a table with auto_increment on the primary key column and if you wish to assign primary key values accordingly, then the primary key column also needs to contain \N

Example: 

"id";"user";"project_id";"start_time";"end_time";"duration";"fixed_rate";"timezone";"break"
\N;1;11;2022-08-01 08:00:00;2022-08-01 08:25:00;1500;\N;"Europe/Berlin";0
\N;1;11;2022-08-01 08:00:00;2022-08-01 08:48:00;2880;\N;"Europe/Berlin";0
\N;1;11;2022-08-02 08:00:00;2022-08-02 12:31:00;16260;\N;"Europe/Berlin";0
\N;1;11;2022-08-03 08:00:00;2022-08-03 10:30:00;9000;\N;"Europe/Berlin";0
\N;1;11;2022-08-04 08:00:00;2022-08-04 09:33:00;5580;\N;"Europe/Berlin";0
\N;1;11;2022-08-05 08:00:00;2022-08-05 13:30:00;19800;\N;"Europe/Berlin";0

Import via SQL

Put the file in /tmp directory, as MariaDB seems only to be able to read files from this directory.

LOAD DATA INFILE '/tmp/import.csv'
INTO TABLE target_table
FIELDS TERMINATED BY ';'
ENCLOSED BY '"' 
TERMINATED BY '\r\n'
IGNORE 1 LINES;

Notes:

When CSV is created on Windows, keyword TERMINATED BY '\r\n' might be needed, but in my case it was not needed. In case of errors try to insert it.

IGNORE 1 LINES is for ignoring headers, if the CSV contains a header line.

Further reading: https://mariadb.com/kb/en/importing-data-into-mariadb/

Import via CLI

mysqlimport --ignore-lines=1 --fields-terminated-by=";" --local -u username -p databasename tablename.csv

Notes:

databasename is the name of the database into which the CSV file will be imported.

The CSV filename needs to be the same like the table to which the CSV file will be imported. In the example above the CSV file would be imported into a table called tablename.

Options:

Paramter --columns can specify the columns to import the data to.

Abgerufen von „https://otremba.net/w/index.php?title=How_to_Import_CSV_in_MariaDB&oldid=3441“