How to quickly import CSV file into MySQL table

160

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.

MySQL said:

#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.

import csv to mysql



Related Articles & Comments

Thank you for visiting us. To continue receiving updates, please Subscribe to our Social Media Channels.

Google+
FACEBOOK
RSS
YOUTUBE
Menu Title
%d bloggers like this: