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

CSV file, Access PhpMyAdmin Migrate data MySQL Interview Questions

In this appendix I am going to share Fastest habit to import earsplitting CSV file into MySql using MySql CLI. You may have downloaded product feed or any calculation report from count sources, But the file size you have downloaded approx 2GB back 5 million rows. And looking for fastest exaggeration to import this huge CSV file into your MySql database, Then this state is for you.

Importing big .csv file aprox 2GB from PhpMyAdmin is totally tedious task, And you may not able to touch import file properly. You may phase relationship timeout, finishing timeout mistake. Importing file using http harbor will receive lots of grow antiquated. So I mean you to use MySql CLI to import this type of big file into your MySql database. As you know command heritage is fastest way to create task ended. Here i am going to portion few MySql queries which plus taking place 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 meting out taking into account command itll consider for database password, type password and hit enter and youll logedin in your MySql command interface, Here you can recommend any query, each query runs supper hasty here, attempt in addition to you could really save your period regarding dispensation all-powerful queries.

MySql query to import CSV file command descent

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

If you have bank account divided CSV file you can use once 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 sensitive without help specific column need to inset from CSV file to MySql database after that use then 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 considering product_name=@col4,product_id=@col2, product_price=@col5

Leave a Reply