Update MySQL from CSV file every day (load data is not allowed in stored procedures)

Question

I work in my website (php language) and I want to update my table every day with csv file so I have write this :

LOAD DATA LOCAL INFILE 'C:/wamp/www/mywebsite/data/m5.csv' 
INTO TABLE m4 
FIELDS TERMINATED BY "," 
LINES TERMINATED BY "\n"
IGNORE 1 LINES
(col1,col2,col3,col4,col5)

And I have create an event in PhpMyAdmin but when I whant to valid my event, I have this error :

#1314 load data is not allowed in stored procedures

How I can do to update my table every day with CSV file ?

Thank you

Your CSV contains all the database records or the updated records only? — fmineo, Feb 09 '17 at 14:20
Every day I Truncate my table and I want insert new information in the with a csv file — Rocstar, Feb 09 '17 at 14:23
Are you intrested to do this with a php script and not with PhpMyAdmin event? — fmineo, Feb 09 '17 at 14:25
Yes ! But it's possible to test it in my wamp serveur ? Thank you — Rocstar, Feb 09 '17 at 15:02

score 0 · Answer 1 · edited May 23 '17 at 12:01

In reference to this question: Importing CSV data using PHP/MySQL

The solution to your problem might be this

<?php
$query = <<<eof
    LOAD DATA INFILE 'C:/wamp/www/mywebsite/data/m5.csv'
    INTO TABLE m4
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (col1,col2,col3,col4,col5)
eof;

$db->query($query);
?>

However, if you are interested in a php script that does the same thing could try this

<?php
$dbhost =  "";
$dbname = "";
$dbusername = "";
$dbpassword = "";


$dbtable = "m4";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "C:/wamp/www/mywebsite/data/m5.csv";

/********************************/
/* Set this to 1 if your table have an auto_increment field.
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysqli queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 0;
$outputfile = "";


if(!file_exists($csvfile)) {
    echo "File not found. Make sure you specified the correct path.\n";
    exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
    echo "Error opening data file.\n";
    exit;
}

$size = filesize($csvfile);

if(!$size) {
    echo "File is empty.\n";
    exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$conn = mysqli_connect($dbhost, $dbusername, $dbpassword, $dbname) or die('Could not connect');

mysqli_set_charset($conn,"utf8");

$sqldel="DELETE FROM ".$dbtable;

$conn->query($sqldel);
$lines = 0;
$queries = "";
$linearray = array();

foreach(explode($lineseparator,$csvcontent) as $line) {

    $lines++;

    $line = trim($line," \t");

    $line = str_replace("\r","",$line);

    /************************************
    This line escapes the special character. remove it if entries are already escaped in the csv file
    ************************************/
    $line = str_replace("'","\'",$line);
    /*************************************/

    $linearray = explode($fieldseparator,$line);

    $linemysql = implode("','",$linearray);

    $linemysql = utf8_encode($linemysql);

    if($addauto)
        $query = "INSERT INTO $dbtable VALUES('','$linemysql');";
    else
        $query = "INSERT INTO $dbtable VALUES('$linemysql');";

    $queries .= $query . "\n";

    $res_i=$conn->query($query);
}


if($save) {

    if(!is_writable($outputfile)) {
        echo "File is not writable, check permissions.\n";
    }

    else {
        $file2 = fopen($outputfile,"w");

        if(!$file2) {
            echo "Error writing to the output file.\n";
        }
        else {
            fwrite($file2,$queries);
            fclose($file2);
        }
    }

}
?>

Update MySQL from CSV file every day (load data is not allowed in stored procedures)

1 Answers1