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
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 .
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
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>