Page 1 of 1

Inserting Excel Sheet data into Mysql; does not work

Posted: Wed Nov 29, 2006 12:47 am
by amir
Hello,

I am trying to insert excel sheet data into mysql database. First, I convert that file into comma delimited CSV and then apply this code.

Code: Select all

$link = mysql_connect("localhost", "user", "pass");
mysql_select_db("db");     

$file = $_FILES['excel_file']['name'];
@copy($_FILES['excel_file']['tmp_name'], "files/$file");
$query = 'LOAD DATA INFILE /www/trisha/admin/"'.$file.'" INTO TABLE student_info FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";';

mysql_query($query);
@unlink($file);
My purpose is
1; upload csv file
2; run load command and insert that file data into DB
2; remove that uploaded file

Please advise what I am doing mistake.

TIA!

Posted: Wed Nov 29, 2006 3:08 am
by dibyendrah
Are you using csv file or xls file ? Looking at your query, I guess there might be a problem in

Code: Select all

LINES TERMINATED BY "\\r\\n";';
Will you please show the sample lines from your file if you are suing csv format. If you are using xls file, I guess it should be done in different way using COM.

With Regards,
Dibyendra

Posted: Wed Nov 29, 2006 3:48 am
by iluxa
It would help if you also posted actual errors you are getting after upload.

Posted: Wed Nov 29, 2006 4:30 am
by dibyendrah
I have csv file like follows :

Code: Select all

"hw_id","hw_name"
69398,"test1"
69399,"test2"
Fields are seperated by "," and lines termintated by "\n"

Code: Select all

<?php
$cn = mysql_connect("localhost", "root", "mppadmin") or die("error connecting!");
mysql_select_db("np_dictionary_db", $cn) or die("unknown db");

$query = "load data local infile 'resultset.csv' into table `tbl_headwords`
fields terminated by ',' 
enclosed by '\"' 
lines terminated by '\n'
(hw_id, hw_name)";

mysql_query($query) or die(mysql_error());
?>
It works fine but the strange issue is that the topmost field names are inserted too. Like "hw_name" is also inserted . :o

I hope this sample will help you.

With Regards,
Dibyendra

Posted: Wed Nov 29, 2006 4:46 am
by Cameri
Might want to try:

Code: Select all

$query = "load data local infile 'resultset.csv' into table `tbl_headwords`
fields terminated by ','
enclosed by '\"'
lines terminated by '\n'
ignore 1 lines
(hw_id, hw_name)";
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Posted: Wed Nov 29, 2006 5:14 am
by dibyendrah
Cameri wrote:Might want to try:

Code: Select all

$query = "load data local infile 'resultset.csv' into table `tbl_headwords`
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
ignore 1 lines
(hw_id, hw_name)";
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Thanks Cameri!
That works fine now.

With Regards,
Dibyendra

Re: Inserting Excel Sheet data into Mysql; does not work

Posted: Wed Nov 29, 2006 5:36 am
by dibyendrah
amir wrote:Hello,

I am trying to insert excel sheet data into mysql database. First, I convert that file into comma delimited CSV and then apply this code.

Code: Select all

$link = mysql_connect("localhost", "user", "pass");
mysql_select_db("db");     

$file = $_FILES['excel_file']['name'];
@copy($_FILES['excel_file']['tmp_name'], "files/$file");
$query = 'LOAD DATA INFILE /www/trisha/admin/"'.$file.'" INTO TABLE student_info FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";';

mysql_query($query);
@unlink($file);

Cheers,
Dibyendra
My purpose is
1; upload csv file
2; run load command and insert that file data into DB
2; remove that uploaded file

Please advise what I am doing mistake.

TIA!
I have just finished making the code as per your requirement and it now works fine ! But I haven't removed the uploaded file and just kept in cvs file. If you want to do that just use unlink() function.

Code: Select all

<?php
if(isset($_POST["Submit_btn"])){
	$cn = mysql_connect("localhost", "root", "mppadmin") or die("error connecting!");
	mysql_select_db("np_dictionary_db", $cn) or die("unknown db");

	print_r($_FILES);
	$file_name = $_FILES["cvs_file"]["name"];
	$tmp_file = $_FILES["cvs_file"]["tmp_name"];
	$upload_path = "cvs";
	$upload_destination = $upload_path."/".$file_name;
	
	if(file_exists($tmp_file)){
		if(move_uploaded_file($tmp_file, $upload_destination)){
			print "File Uploaded!";
		}else{
			print "Failed to upload the selected file!";
		}
	}else{
		exit("File Doesn't Exists!");
	}

	$query = "load data local infile '$upload_destination' into table `tbl_headwords`
	fields terminated by ','
	enclosed by '"'
	lines terminated by '\n'
	ignore 1 lines
	(hw_id, hw_name)";

	if(mysql_query($query)){
		print "File Uploaded And Data Imported Into Table!";
	}else{
		die(mysql_error());
	}
	


}

?>

Code: Select all

<FORM NAME="cvs_upload" ACTION="<?=$HTTP_PHP_SELF?>" method="POST" enctype="multipart/form-data">

<input type="file" name="cvs_file">
<input type="submit" name="Submit_btn">

</FORM>