(PHP+MYSQL) LOAD DATA LOCAL INFILE with variable

[João_5]

Hello guys, i have been working on a new script that will import data from a TXT file to mysql.

Using this code works ok:

$sql = 'LOAD DATA LOCAL INFILE \'logons-16-12-2013.txt\' INTO TABLE pcvsuser COLUMNS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\\r\\n\'';

But if i use this it does not work:

$sql = "LOAD DATA LOCAL INFILE '".$file."' INTO TABLE pcvsuser COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n'";

Now i will show the entire code:

<?php
// Import shared data
include 'config_all.php';
// Connect to Mysql server
    $con = mysql_connect("$sqlhost","$sqluser",""); 
    if (!$con) 
    { 
    die('Could not connect: ' . mysql_error()); 
    } 
    // Connect to "ad" database
$sel = mysql_select_db("ad", $con); 
    if (!$sel) 
    { 
    die('Could not select DB: ' . mysql_error()); 
    } 

//Define a variables for a clean file name
$data = shell_exec ("echo %date%.txt");
$file = "logons-$data";
// Copy file from remote Domain Controler to HTTP Server
$cmdcopy = shell_exec ("copy z:\logons-%date%.txt logons-%date%.txt");
echo $cmdcopy;
// Load data from file to table "pcvsuser" table
$sql = "LOAD DATA LOCAL INFILE '".$file."' INTO TABLE pcvsuser COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n'";
mysql_query($sql) or die ("<br><br>Error: ". mysql_error()." with query ".$sql.""); 

?>

If i use $sql = 'LOAD DATA LOCAL INFILE \'logons-16-12-2013.txt\' INTO TABLE pcvsuser COLUMNS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\\r\\n\''; It works but i need to add $file.

The domain controler creates a file per day with specific date, and this script will be runned everyday at 23:55.

Importing as a variable, is esier because i do not have to do it by hand, i do not have to change de date by hand everyday.

What is wrong?

[pankaj_patel]

$data = shell_exec ("echo %date%.txt"); 

Make sure above code does not outputs any type of extra chars or EOL chars.

[João_5]

I already tested it with

$data = shell_exec ("echo %date%.txt");
$file = "logons-$data"
echo $file;

but i need to say that when i am using

$sql = "LOAD DATA LOCAL INFILE '".$file."' INTO TABLE pcvsuser COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n'";

The error that it shows is (Can't find File)

[pritaeas]

If you echo $sql; do you see the correct query?

[João_5]

Error echoed:

Error: Can't find file 'logons-16-12-2013.txt '. with query LOAD DATA LOCAL INFILE 'logons-16-12-2013.txt ' INTO TABLE pcvsuser COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

if instead of '".$file."' i insert **\'logons-16-12-2013.txt** it loads the file to the db.

[João_5]

Ok i set the script to show everything with detailed comments:

--->Copy files from domain controler to http server:
ECHO OF VARIABLE $ cmdcopy: 1 file(s) copied. 

--->Shows if variable $ data is defined correctrly:
ECHO OF VARIABLE $ data: 16-12-2013.txt 

--->Shows if variable $ file is defined correctrly:
ECHO OF VARIABLE $ file: logons-16-12-2013.txt 

--->Shows if variable $ sql is defined correctrly:
ECHO OF VARIABLE $ sql: LOAD DATA LOCAL INFILE 'logons-16-12-2013.txt ' INTO TABLE pcvsuser COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

--->Results of the MySql query:
ERROR: Can't find file 'logons-16-12-2013.txt '. WITH QUERY: LOAD DATA LOCAL INFILE 'logons-16-12-2013.txt ' INTO TABLE pcvsuser COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

This is the result:

[cereal]

It seems there's an extra space near the end of the filename, run the trim function against the $file variable: $file = trim($file); before populating the $sql variable.

[João_5]

Instead of trim i user this:

If i user $file shell_exec ("echo logons-%date%.txt");
I get the file name like logons-16-12-2013 .txt
But if i use $data = shell_exec ("echo %date%.txt");
and define $file = "logons-$data";
I get the file name like logons-16-12-2013.txt

I will test with trim

[João_5]

So simple.... i could cry...
$file = trim($file); works like a charm

thanks very much cereal

Thanks everybody for the efford

[cereal]

Good, if we are done, please mark the thread as solved. Bye! :)

[João_5]

sorry

[Abdul Waseem]

Do this work for excel file as well.