Fastest way to import big CSV file into MySql using MySql CLI

Last updated Oct 26, 2017

In this post I am going to share Fastest way to import big CSV file into MySql using MySql CLI. You may have downloaded product feed or any other report from other sources, But the file size you have downloaded approx 2GB with 5 million rows. And looking for fastest way to import this huge CSV file into your MySql database, Then this post is for you.

Importing big .csv file aprox 2GB from PhpMyAdmin is very tedious task, And you may not able to succeed to import file properly. You may phase connection timeout, execution timeout error. Importing file using http port will take lot’s of time. So I suggest you to use MySql CLI to import this type of huge file into your MySql database. As you know command line is fastest way to make task done. Here i am going to share few MySql queries which help you to import CSV file into your MySql database in minutes.

Login into MySql database using CLI

 mysql -u username -p dbname --local-infile

After running following command it’ll ask for database password, type password and hit enter and you’ll logedin in your MySql command interface, Here you can run any query, each query runs supper fast here, try once you could really save your time on running big queries.

MySql query to import CSV file command line

LOAD DATA LOCAL INFILE 'csv_file_path/filename.csv'  INTO TABLE table_name 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

If you have tab separated CSV file you can use following query just replace FIELDS TERMINATED BY ‘,’ from FIELDS TERMINATED BY ‘\t’

LOAD DATA LOCAL INFILE 'csv_file_path/filename.csv'  INTO TABLE table_name 
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

If you want only specific column need to inset from CSV file to MySql database then use following query to ignore unwanted CSV columns.

LOAD DATA LOCAL INFILE 'csv_file_path/filename.csv'  INTO TABLE table_name FIELDS
 TERMINATED BY ',' LINES TERMINATED BY '\n' (@col1,@col2,@col3,@col4,@col5,@col6) 
set product_name=@col4,product_id=@col2, product_price=@col5;

Where col1,2,3,4,5,6 are the CSV file column @ will ignore all the column except those database column which you have set like product_name=@col4,product_id=@col2, product_price=@col5

mysql php