How to quickly import CSV file into MySQL table
Import CSV into MySQL
This is a simple version of LOAD DATA command to import simple CSV file into MySQL table. It look only .02 seconds to import 40+ records.
LOAD DATA INFILE ‘c:\datafolder\t_order_details.csv”
INTO TABLE t_order_details
FIELDS TERMINATED BY ‘;’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS;
Assuming you have the first row as the column title, you have to include this
IGNORE 1 ROWS
I must use double slash “\” in the file name, because my csv file name starts with “t” and “\t” means escape sequence. So if I execute with single slash, I was receiving an error as below.
#29 – File ‘c:\xampp\mysql\data\datafolder_order_details.csv’ not found (Errcode: 22 “Invalid argument”)
otherwise, single slash is sufficient.
How about writing such command to import CSV files of large data size to MySQL?
If you are inserting records with multiple fields especially with empty values at the end or excel fields not in the same order as database fields, then you must include field names at the end in LOAD DATA command. This may be bit challenging as you need to match each column and data type. To ease the task, you can use graphical tools – www.heidisql.com to build LOAD DATA command and you can use it later.