how to load data from local file in php with pdo driver

Question

I have a function that needs to load data into a mysql db from a csv file, so I wrote some code:

try {

    $pdo = new PDO('mysql:host=localhost;dbname=borsino_ittico',
                   'XXXXX', 'XXXXX', 
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
                          PDO::MYSQL_ATTR_LOCAL_INFILE => true, 
                          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
                  );


} 
catch (PDOException $e) {
       $mex= "database connection failed: ".$e->getMessage();
       return $mex;
    }

other code

try {
    $affectedRows = $pdo->exec("
    LOAD DATA LOCAL INFILE ".$pdo->quote($file)." INTO TABLE ".
            $pdo->quote($dbTable)." FIELDS TERMINATED BY ';'
            LINES TERMINATED BY '\n,\\n'");

    $mex= "Loaded a total of $affectedRows records from this csv file.\n";
    return $mex;

} 
catch (PDOException $e) {
        $mex= "ATTENZIONE, ERRORE CARICAMENTO DATI: ".$e->getMessage();
        return $mex;
}

Actually this doesn't seem to works and threw an exception:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''asta' FIELDS TERMINATED BY ';' LINES TERMINATED BY ' ,\n'' at line 1

First question is about $file (the resource): should be in absolute/relative form (likes c:\xxx\csvfile.csv or ..\xxx\csvfile.csv) or I've to expose it with a function likes $csvFile=fopen($file,"r");

Then, should I use pdo native function likes $pdo->quote() for insert arguments (like the file,table name, separator, lines terminated by) on the $affectedRows query?

How can I force the esecution of the query even with some errors on some row?

score 1 · Accepted Answer · answered Dec 02 '14 at 16:15

1

You're quoting your table name, producing

... INTO table 'asta' ...

'-quotes turn things into a strings. So asta is no longer a table name, it's just a string with the letters a, s, etc...

Identifiers must be quoted with backticks:

... INTO table `asta` ...
               ^----^---

answered Dec 02 '14 at 16:15

Marc B

356,200
43
426
500

I quoted as suggested the table name ASTA and then I rised this new exception`SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version` – JahStation Dec 02 '14 at 16:20
should be enabled it from php.ini or somewhere else? – JahStation Dec 02 '14 at 16:25
no. it's a mysql setting: http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html – Marc B Dec 02 '14 at 16:28
from some other post i found that `PDO::MYSQL_ATTR_LOCAL_INFILE => true,` should solve the problem...but it doesn't work! – JahStation Dec 02 '14 at 16:30
I found this solutions thats seem to works (now I've errors but from the data into the file :) ) [workaround solution](http://stackoverflow.com/questions/23525111/why-cant-i-use-load-data-local-with-pdo-even-though-i-can-from-cli-client) – JahStation Dec 02 '14 at 16:41

score 0 · Answer 2 · answered Dec 03 '14 at 16:05

0

LINES TERMINATED BY '\n,\\n'

doesn't work for me!!!! after many test the only solution is to remove the double escaped \\n

So

`LINES TERMINATED BY '\n'`

works even if somewhere I read about the needs of "escape" the "\" simbol for PHP.

answered Dec 03 '14 at 16:05

JahStation

893
3
15
35

how to load data from local file in php with pdo driver

2 Answers2