How to Import a CSV in MySQL
This tutorial focuses on the
LOAD DATA command which is available if you use the MySQL command line client. By default it assumes your source data is delimited using tabs (vs commas), has strings that contain the tab delimiter in single quotes, and each row of data ends with the newline (
First you need to connect to MySQL:
$ mysql -u username -p -h database.host.name database_name
-p option will result in the user being prompted for a password.
-h database.host.name is optional if the MySQL server is on the same host. Also optional is
database_name but it is better to specify the database/schema name now rather than later.
Here are example uses of the
LOAD DATA command:
-- source file is located in the MySQL Server data directory, is tab delimited, and the columns exactly match the table
LOAD DATA INFILE 'user_data.tsv' INTO TABLE users;
-- source file is local, is comma delimited and contains only some columns. Database is remote
LOAD DATA LOCAL INFILE '/tmp/user_data.csv'
INTO TABLE users (first_name, last_name, email)
FIELDS TERMINATED BY ',';
-- source file is comma delimited, strings are enclosed by double quotes, lines are terminated by carriage return/newline pairs, has a single header row that has to be ignored
LOAD DATA INFILE 'data.txt' INTO TABLE my_table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
-- Ignore a column in the source file by assigning it to a user variable and not assigning the variable to a table column
LOAD DATA INFILE 'data.txt'
INTO TABLE my_table_name (column1, @dummy, column2, @dummy, column3);
-- Populate a column the source file does not have any data for using the SET clause
LOAD DATA INFILE 'file_no_timestamps.txt'
INTO TABLE my_table_with_timestamps
SET timestamp_column = CURRENT_TIMESTAMP;