SQL import CSV file with PHP

Question

I'm trying to import a pretty big CSV file into my database (locally) the file is 230MB and its about 8.8 million lines the problem I have isn't opening the CSV or dont know how to import it, the file opens, imports about 500,000 lines and then it quits and trows no error or timeout or anything, i just get to see my webpage.

this is the code:

    try {
    $conn = new PDO("mysql:host=$servername;dbname=adresses_database", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    $row = 1;
if (($handle = fopen("bagadres.csv", "c+")) !== FALSE) {
    while (($data = fgetcsv($handle, '', ";")) !== FALSE) {
        if (!isset($write_position)) {        // move the line to previous position, except the first line
                $write_position = 0;
        $num = count($data); // $num is 15
        $row++; //i dont need this?
        $stmt = $conn->prepare("INSERT INTO adresses (openbareruimte, huisnummer, huisletter, huisnummertoevoeging, postcode, woonplaats, gemeente, provincie, object_id, object_type, nevenadres, x, y, lon, lat) VALUES (:openbareruimte, :huisnummer, :huisletter, :huisnummertoevoeging, :postcode, :woonplaats, :gemeente, :provincie, :object_id, :object_type, :nevenadres, :x, :y, :lon, :lat)");
        $stmt->bindParam(':openbareruimte', $data[0]);
        $stmt->bindParam(':huisnummer', $data[1]);
        $stmt->bindParam(':huisletter', $data[2]);
        $stmt->bindParam(':huisnummertoevoeging', $data[3]);
        $stmt->bindParam(':postcode', $data[4]);
        $stmt->bindParam(':woonplaats', $data[5]);
        $stmt->bindParam(':gemeente', $data[6]);
        $stmt->bindParam(':provincie', $data[7]);
        $stmt->bindParam(':object_id', $data[8]);
        $stmt->bindParam(':object_type', $data[9]);
        $stmt->bindParam(':nevenadres', $data[10]);
        $stmt->bindParam(':x', $data[11]);
        $stmt->bindParam(':y', $data[12]);
        $stmt->bindParam(':lon', $data[13]);
        $stmt->bindParam(':lat', $data[14]);
    $stmt->execute();
} else {
                $read_position = ftell($handle); // get actual line
                fseek($handle, $write_position); // move to previous position
                fputs($handle, $line);           // put actual line in previous position
                fseek($handle, $read_position);  // return to actual position
                $write_position += strlen($line);    // set write position to the next loop
            }
            fflush($handle);                         // write any pending change to file
        ftruncate($handle, $write_position);     // drop the repeated last line
        flock($handle, LOCK_UN);   
    }
   fclose($handle);
}
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }

$conn = null;

i came this far looking for help on stackoverflow and PHP manual, i also searched if it were a mysql error. but i cannot figure this out, (for any suggestions about mysql settings im using linux mint 18)

I'm sorry i dont have a full answer for you. i have lived this before and ended up just outputting to an sql file , dropping it on the server and running mysql to run it. sorry i dont have more advice for this :) i did not have the time to find out why half a million iterations was a cap... — Ralph Thomas Hopper, Nov 17 '16 at 16:02

score 6 · Accepted Answer · answered Nov 17 '16 at 16:02

6

I would strongly recommend that you use MySQL's LOAD DATA INFILE, which is probably the fastest and most efficient to get CSV data into a MySQL table. The command for you setup would look something like this:

LOAD DATA INFILE 'bagadres.csv' 
INTO TABLE adresses
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

If your fields are not enclosed by quotes, or are enclosed by something other than quotes, then remove or modify the ENCLOSED BY clause. Also, IGNORE 1 ROWS will ignore the first row, which would make sense assuming that the first line of your file were a header row (i.e. not actual data but column labels).

answered Nov 17 '16 at 16:02

Tim Biegeleisen

387,723
20
200
263

This is indeed an answer i come across more often, but if i use load data infile i get a permission denied to counter sql injections i think? Also adding load local data infile doesnt seem to work, it just loads the page and does nothing :/ – Jesse Nov 17 '16 at 17:33
I don't see how SQL injection is possible with `LOAD DATA` since no query is actually being run. Have a look here for some help on how to make this work from PHP: http://stackoverflow.com/questions/18915104/php-import-csv-file-to-mysql-database-using-load-data-infile – Tim Biegeleisen Nov 17 '16 at 17:37
thank you that link is quite detailed, I'll try via php otherwise i'll do it like that answer from console, that should work too i suppose :) – Jesse Nov 17 '16 at 17:45

SQL import CSV file with PHP

1 Answers1