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 (column1, column2) SET timestamp_column = CURRENT_TIMESTAMP;
Spread the wordTweet