Importing Large CSV file in MySQL using php

Question

I am trying to import a 60,000 or more rows in a CSV file. The code can import a 5000 lines only. Someone can help me?

require_once('connection.php');

if ($_FILES[csv][size] > 0) {

    //get the csv file
    $file = $_FILES[csv][tmp_name];
    $handle = fopen($file,"r");

    //loop through the csv file and insert into database
    do {
        if ($data[0]) {
            mysql_query("INSERT INTO transactions (EntryId_Pk, AccessCode, MobileNumber, TelcoCode, TIN, ORNo, ORAmt, NumOfEntries, ECN, AddedOn) VALUES
                (
                    '".addslashes($data[0])."',
                    '".addslashes($data[1])."',
                    '".addslashes($data[2])."',
                    '".addslashes($data[3])."',
                    '".addslashes($data[4])."',
                    '".addslashes($data[5])."',
                    '".addslashes($data[6])."',
                    '".addslashes($data[7])."',
                    '".addslashes($data[8])."',
                    '".addslashes($data[9])."'
                )
            ");
        }
    } while ($data = fgetcsv($handle,1000,",","'"));

Your whole post doesn't contains a single question about a programming problem. What's your problem? — idmean
– idmean, Commented Feb 16, 2014 at 13:50
wouldn't a while loop not being better than a do while here? ;) — hek2mgl
– hek2mgl, Commented Feb 16, 2014 at 13:51
@hek2mgl Agreed, unless there is always a first row guaranteed. Also, I'm going to give the usual spiel regarding mysql_* being deprecated and should not be used. Everytime you use that lib, Rasmus Lerdorf eats a kitten. — Zarathuztra
– Zarathuztra, Commented Feb 16, 2014 at 13:53
@Zarathuztra If there would be no rows a while loop would just not being entered — hek2mgl
– hek2mgl, Commented Feb 16, 2014 at 13:55
@hek2mgl Exactly, that's what I'm getting at. IN this case, while would work just fine. — Zarathuztra
– Zarathuztra, Commented Feb 16, 2014 at 13:55

complex857 · Accepted Answer · 2014-02-16 13:58:54Z

4

You might want to use the LOAD DATA MySQL statement. This can be really fast since you don't need to read everything into PHP-land and let MySQL be smart about the allocations. You can use it something like this from PHP:

// dont use mysql_* anymore in new code
mysqli_query($dblink, '
    LOAD DATA LOCAL INFILE "'.$file.'"
        INTO TABLE transactions
        FIELDS TERMINATED by ","
        OPTIONALLY ENCLOSED BY "\'"
        LINES TERMINATED BY "\n"
');

answered Feb 16, 2014 at 13:58

complex857

20.9k6 gold badges54 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

hek2mgl Over a year ago

That's the prefered solution +1 However, in shared hosting environment this might not being allowed

dardar Over a year ago

Where should I put this? Thank you for all of your answers. :)

complex857 Over a year ago

This would replace the do { ... } while loop in your code. You will still need the csv file's path itself as the parameter after the INFILE.

dardar Over a year ago

@complex857 I will try this at the best as I can. Perhaps, Thank you for your efforts.

complex857 Over a year ago

@shijin, If you use LOCAL keyword then the file is read by the client and it should work with a remote server (the mysqld don't have to see the file). For this to work both the server and the client have to allow this. See more about the LOCAL in the docs, there's a paragraph starting with LOCAL around the middle.

rocknrollcanneverdie · Accepted Answer · 2014-02-16 14:23:39Z

0

It could be a timeout error. Try to set

set_time_limit(0);

Also could be a good practice to import the data in chunks and insert multiple rows within a query instead of doing it row by row.

answered Feb 16, 2014 at 14:23

rocknrollcanneverdie

4404 silver badges8 bronze badges

Collectives™ on Stack Overflow

Importing Large CSV file in MySQL using php

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related