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.

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.

All in One WordPress Hosting Starts at 30$ per month
All in One WordPress Hosting
WordPress
High optimized WordPress hosting, secure firewall, HTTPS, Backup, hack-fix guarantee and many others at 30$ per month

import csv to mysql

HostGator $2.75 per month
HostGator
24/7/365 Technical Support, Free Site Building Tools, 4500 Website Templates, Free Shopping Cart Software, Ideal for WordPress, 45 Day Money Back Guarantee