Search code examples
phpmysqldatabaseload-data-infile

Importing CSV file to mysql table using "LOAD DATA" command

I am running a windows machine for local development and attempting to load large csv files into mysql. My code looks like this:

$sql_query = 'LOAD DATA LOCAL 
    INFILE "' . $tempLoc . '"
    INTO TABLE users
    FIELDS
    TERMINATED BY " "
    ENCLOSED BY "\'"
    LINES
    TERMINATED BY "\\n"                                
    (
     id,
     name,
     value 
    )';

$statement = $this->adapter->query($sql_query);
$resultSet = $statement->execute();
$this->adapter->getDriver()->getConnection()->commit();

When I load the file I got this error:-

"PDOStatement::execute() [pdostatement.execute]: LOAD DATA LOCAL INFILE forbidden in..."

I have searched for an answer, but I have not been able to resolve the issue. But when i connect the db with local db in my local m/c(ie, localhost with root) it runs well

  • my application is in local machine
  • DB is connected to another server
Solution

    1. Make sure the server allows LOAD DATA LOCAL INFILE

      [server]local-infile=1

    2. Make sure that your MySQL user has 'FILE' privilege

    3. Set the PDO attribute in your PHP script,

      $pdo = new PDO($dsn, $username, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE=>1))