Magento: LOAD DATA LOCAL INFILE not working

Question

Trying to insert a CSV to a table via LOAD DATA LOCAL INFILE. It doesn't show errors but table is empty (CSV is not empty). There is a workaround doing foreaches and manual inserts but it's much slower compared to LOAD DATA INFILE.

Any suggestions?

$resource = Mage::getSingleton('core/resource');
$write = $resource->getConnection('core_write');

$filename = Mage::getBaseDir()."/var/mdb_processed/parts.csv";
$query = <<<eof
    LOAD DATA LOCAL INFILE '$filename'
 INTO TABLE ppi_parts
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' lines terminated by '\n'
 ignore 1 lines
(partID,sku,isFreight,quantity,price,lastCost,vwCode,shortDesc,longDesc,headline,consumerDesc,salesMessage,relatedProd1,relatedProd2,weight,oversized,corePrice,titleMetaDescription,metaKeywords,fits,notes,seoField,location)
eof;


$write->query($query);

http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile — Fiasco Labs, Sep 29 '15 at 18:59

score 0 · Answer 1 · answered Jan 13 '16 at 22:28

Or the PDO version of this:

$options = array(PDO::MYSQL_ATTR_LOCAL_INFILE => true);
$local = simplexml_load_file(Mage::getBaseDir() . "/app/etc/local.xml");
$connection = $local->global->resources->default_setup->connection;
$pdoDb = new PDO(
    "mysql:host={$connection->host};dbname={$connection->dbname}", 
    $connection->username, 
    $connection->password, 
    $options
    );

$path_to_datafile = Mage::getBaseDir()."/var/mdb_processed/parts.csv";

$sql = "LOAD DATA LOCAL INFILE '{$path_to_datafile}'
        REPLACE INTO TABLE ppi_parts
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"' 
        LINES TERMINATED BY '\\n'
        IGNORE 1 LINES;";

$result = $pdoDb()->exec($sql);

score -1 · Answer 2 · answered Sep 29 '15 at 19:09

I resolved it by manually connecting to the DB. Varien DB object for some reason wasn't working.

To create a manual connection I had to do:

$local = simplexml_load_file( Mage::getBaseDir()."/app/etc/local.xml");

$connection = $local->global->resources->default_setup->connection;
$filename = Mage::getBaseDir()."/var/mdb_processed/parts.csv";

$link = mysql_connect($connection->host,$connection->username,$connection->password);
mysql_select_db($connection->dbname, $link) or die('Could not select database.');

Then:

$query = <<<eof
        LOAD DATA LOCAL INFILE '$filename'
     INTO TABLE ppi_parts
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' lines terminated by '\n'
     ignore 1 lines
    (partID,sku,isFreight,quantity,price,lastCost,vwCode,shortDesc,longDesc,headline,consumerDesc,salesMessage,relatedProd1,relatedProd2,weight,oversized,corePrice,titleMetaDescription,metaKeywords,fits,notes,seoField,location)
eof;
mysql_query($query);

Works perfectly.

Magento: LOAD DATA LOCAL INFILE not working

2 Answers2