Jesse Jesse - 1 year ago 74
MySQL Question

SQL import CSV file with PHP

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
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]);
} 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);
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)

Answer Source

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

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download