How to import really huge CSV files very fast in one shot in remote MYSQL database server?

MySQL

How to import really huge CSV files very fast in one shot in remote MYSQL database server?

SOLUTION: Load Data command on MySQL

LOAD DATA command in MySQL is a very fast & reliable way to import a huge CSV (containing several millilon lines/rows) to a remote MySQL server. Here is the syntax:

LOAD DATA LOCAL INFILE 'c:/csv/eventsbig.csv' IGNORE
INTO TABLE 'testdb'.'eventsbig'
FIELDS TERMINATED by ’,’
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY ’\n’
IGNORE 1 LINES (`evt_type`, `evt_name`, `billing_date`);

Please refer here for further reading on LOAD DATA command on MySQL.

MYSQLIMPORT Utility:

Chris Eiffel wrote here how he has created a mysqlimport utility based on the LOAD DATA command to import a large csv file to a remote MySQL server.

How to Import a Large CSV file to MySQL

https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html

NOTE: While using the mysqlimport command with SSH, keep the following in mind:

    • Be sure that you have an SSH Account/access to the terminal on the remote server/computer with the MySQL Server.
    • Remember if your database is big, be sure to put the index/primary/unique keys on the tables created before you upload the CSV file to populate data into the database.

Another PHP script has been posted by Sanath which can help you in uploading a large CSV file to a remote MySQL Database server. You can download it from here

Happy programming!

Published by Pre-Press Admin

Entrepreneur, Database-freak, Motorhead, Traveller, Writer-Author, Tool-and-gadget-freak, Amateur photographer, Reader, Music lover, Toy-mechanic, Dad.

One thought on “How to import really huge CSV files very fast in one shot in remote MYSQL database server?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: