csv import issues

[swatisonee]

hello,

 

php ver.  2.6.4 , mysql 4.1.12 is what i have.

 

the foll. sql doesnt run ....and i dont know whether its a coding error or because its not ver 5 ?

$sql = "load data local infile 'abc.csv' into  TABLE `zeetest` fields terminated by ','  lines terminated by '\n'  (`A`, `B`, `C`)"; //(a,b,c are the fields in the table zeetest

$result= mysql_query($sql);

 

 

[PFMaBiSmAd]

Echo mysql_error() on the next line after the mysql_query() statement to find out if there are any mysql errors.

[swatisonee]

this is what i  got

 

Access denied for user 'xxx'@'localhost' (using password: YES)

[PFMaBiSmAd]

If you got that as a mysql_error() due to a mysql_query() statement, it means that you did not have a valid connection to the database server at the time you executed the mysql_query() statement.

 

What is your code that is connecting to the mysql server?

[swatisonee]

this is the page code ....i echoed out the userid and it works fine so how / why could the sql go wrong?

 

<?
header("Cache-Control: public");
include ("../include/session.php"); // session start
include ("../indl.php"); //db info of dbname , un, pw
$userid = $_SESSION['userid']; 

if(!isset($_SESSION['userid'])){
echo "<center><font face='Calibri' size='2' color=red>Sorry, Please login and use this page </font></center>";
exit;}

echo $userid; // works fine upto this point 
$sql = "load data local infile 'abc.csv' into  TABLE `zeetest` fields terminated by ','  lines terminated by '\n'  (`A`, `B`, `C`)"; 

//(a,b,c are the fields in the table zeetest

$result= mysql_query($sql) or die (mysql_error()); 

[PFMaBiSmAd]

Are the database details (username, password, and permissions) the same for the database with the`zeetest`table and for your user/log-in information?

[swatisonee]

yes it is and what i did was just rewrote the code ...there must've been something that i missed out but now the error is

 

 

File abc.csv not found (Errcode: 2)

 

now abc.csv is sitting on my desktop so i put the whole path  'C:\Users\xyz\Desktop\abc.csv'  in the sql as under :

 

$sql = "LOAD DATA LOCAL INFILE 'C:\Users\xyz\Desktop\abc.csv' INTO TABLE `zeetest`  LINES TERMINATED BY '\r\n' (`A`, `B`, `C`) ";

 

i also think maybe i should be defining the csv file in a more general manner ? i noticed in another forum post , someone used and wonder how i could incorporate this in my code ?

$sqlcsv = "LOAD DATA LOCAL INFILE '".$_FILES['file']['tmp_name'] INTO TABLE members 				FIELDS TERMINATED BY ',' (...field names)";

[PFMaBiSmAd]

Because php is actually what is reading the file (the LOCAL keyword) it is having a problem with the \ characters in the file path. If you use / in the path 'C:/Users/xyz/Desktop/abc.csv' it will work (at least it did form me when I just tried it.)

 

As to your second question. If you are uploading the files, then yes, you could probably (untested) use the ['tmp_name'] in the query to access the file.

[swatisonee]

i replaced the backslash with the forward slash and this is the error

 

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 'C:/Users/xyz/Desktop/abc.csv INTO `zeetest` (`A`, `B`, `C`)' at line 1

 

i have checked the table, the fields and my eyes are going spare but i cannot find the wrong syntax !

 

So as usual i rewrote the whole thing :

$sql = "LOAD DATA LOCAL INFILE 'C:/Users/xyz/Desktop/abc.csv' INTO TABLE `zeetest` fields terminated by ',' lines terminated by '\n' (`A`, `B`, `C`)";
$result= mysql_query($sql) or die (mysql_error());

  and i got an error " file ... not found (errcode2)

[PFMaBiSmAd]

Are you sure about the actual path to the file - /Users/xyz/ ?

[swatisonee]

yes path is correct...even moved the file to mydocuments to check., looked at options without the path as well...

 

is sqlimport only in ver 5 ?

[swatisonee]

here's the latest thing i tried  with the same file not found, errocode 2 result:

 

$File = addslashes(".\C:\Users\xyz\Documents\abc.csv");

$SQL = "Load Data Local InFile \"" . $File . "\" into table zeetest";

$result = mysql_query($SQL) or die(mysql_error());

 

I then ran the sql query directly in phpadmin where it works fine . The php code created there  was

$sql = 'LOAD DATA LOCAL INFILE \'/tmp/php5HBa4B\' INTO TABLE `zeetest` FIELDS TERMINATED BY \';\' ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\''; 

which i swapped with my file name as under but it still throws up the errcode 2 and says file not found

$sql = 'LOAD DATA LOCAL INFILE \'C:/Users/xyz/Documents/abc.csv\' INTO TABLE `zeetest` FIELDS TERMINATED BY \';\' ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\''; 

 

[fenway]

Are you sure you have the correct privileges to do this?