Importing Extra-Large File to MySQL Using LOAD DATA INFILE

Recently I try to make a WebApp to make weekly report for my work. Then I found an issue about importing large file to MySQL, my data type is CSV and it has about 1 Mil record (Holy !). Then I was wondering how to import this to MySQL by PHP, it takes an age. You can try to upload it at night and it will be done in the morning. Then it’s not the answer I need for my case. After doing some research on the internet, I found this function from MySQL. Yes its LOAD DATA INFILE. It is very simple, and also its work very fast.

The command is

LOAD DATA LOCAL INFILE ‘$file’
INTO TABLE depo
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES
(field1,field2,field3,field4,field5, etc)

Okay lets try how to make Simple import function with PHP.

First, make table on MySQL Database with this structure

4.PNG

Then make connection script to your database and name it connect.php.

1.PNG

After that make new file and name it import.php, and then write this.

23

Then you can try this import function!

5.PNG

6

7.PNG

Walah it works! I just need 30 secs to import 550K record from 100 MB file. Hope this can help you guys and save your day!

Well if you having error about File upload is too large you can Fix it here How to Fix ERROR:File upload is too large

Here credits : Stackoferflow-PHP Import CSV File to MySQL Database

Advertisements

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