Inserting Excel Sheet data into Mysql; does not work

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
amir
Forum Contributor
Posts: 287
Joined: Sat Oct 07, 2006 4:28 pm

Inserting Excel Sheet data into Mysql; does not work

Post 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!
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
iluxa
Forum Newbie
Posts: 15
Joined: Wed Nov 29, 2006 3:29 am

Post by iluxa »

It would help if you also posted actual errors you are getting after upload.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
User avatar
Cameri
Forum Commoner
Posts: 87
Joined: Tue Apr 12, 2005 4:12 pm
Location: Santo Domingo, Dominican Republic

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

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

Post 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>
Post Reply