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, Feb 16 '14 at 13:50
wouldn't a `while` loop not being better than a `do while` here? ;) — hek2mgl, Feb 16 '14 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, Feb 16 '14 at 13:53
@Zarathuztra If there would be no rows a while loop would just not being entered — hek2mgl, Feb 16 '14 at 13:55
@hek2mgl Exactly, that's what I'm getting at. IN this case, while would work just fine. — Zarathuztra, Feb 16 '14 at 13:55
@user3315577 Are you receiving any errors? $data could be causing you to hit a memory limit. — Zarathuztra, Feb 16 '14 at 13:57
my situation is that when i am importing a large csv file. it doesn't continue. sorry a php begginer here. :( — dardar, Feb 16 '14 at 14:12
check my solution at http://stackoverflow.com/a/22744300/2037323 which includes some comparisons also — Raza Ahmed, Mar 30 '14 at 13:07

score 4 · Answer 1 · answered Feb 16 '14 at 13:58

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 '14 at 13:58

complex857

20,425
6
51
54

That's the prefered solution +1 However, in shared hosting environment this might not being allowed – hek2mgl Feb 16 '14 at 14:03
Where should I put this? Thank you for all of your answers. :) – dardar Feb 16 '14 at 15:03
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`. – complex857 Feb 16 '14 at 15:20
@complex857 I will try this at the best as I can. Perhaps, Thank you for your efforts. – dardar Feb 16 '14 at 15:41
@shijin, If you use `LOCAL` keyword then the file is read by the client and it should work with a remote server (the mysql*d* 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](https://dev.mysql.com/doc/refman/5.1/en/load-data.html), there's a paragraph starting with LOCAL around the middle. – complex857 Apr 29 '14 at 19:24

score 0 · Answer 2 · answered Feb 16 '14 at 14:23

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 '14 at 14:23

rocknrollcanneverdie

430
4
8

Importing Large CSV file in MySQL using php

2 Answers2