To import Excel data, first you need to have a Excel reader. It should be accurate enough to interpret Excel data as expected. There 's a good old Excel reader.
Download PHPExcelReader.
In the downloaded archive, you only need Excel directory with files including oleread.inc and reader.php.
Just extract it where your web server can access.
Next place your excel file or just create one with some dummy data. Make sure this file is readble by the web server.
Finally create your php script to connect with database, read Excel file and insert data into db.
[sourcecode language="php"]
<?php require_once 'Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('CP1251');
$data->read('a.xls');
$conn = mysql_connect("localhost","root","");
mysql_select_db("test",$conn);
for ($x = 2; $x <= count($data->sheets[0]["cells"]); $x++) {
$first = $data->sheets[0]["cells"][$x][1];
$middle = $data->sheets[0]["cells"][$x][2];
$last = $data->sheets[0]["cells"][$x][3];
$sql = "INSERT INTO mytable (First,Middle,Last)
VALUES ('$first','$middle','$last')";
echo $sql."\n";
mysql_query($sql);
}
?>
[/sourcecode]
Even your 1cent donation is appreciated.
This is fine solution for small xls files. But if you want read 10k rows, this way will not work.
ReplyDeleteFor big files you should use NoXLS API Parser http://noxls.net/documentation
[…] Import Excel Data into MySQL with PHP. […]
ReplyDelete$data->read('a.xls');
ReplyDeletewhat this line do.......
my browser shows the a.xls file is not readable
if the code is in server will your code search for a.xls on server or client machine
ReplyDeletewhere is require_once 'Excel/reader.php'; file
ReplyDeleteHi,
ReplyDeleteThere should be a folder named 'Excel' in PHPExcelReader download.
i dont have any Excel folder, i have PHPExcel folder and no reader.php file in it
ReplyDeleteThank u very much... i got solution from this one
ReplyDeleteHello Sir i am using codeigniter framework why not work this code plzz help me.
ReplyDeleteA PHP Error was encountered
Severity: Warning
Message: require_once(Excel/excel_reader2.php): failed to open stream: No such file or directory
Filename: controllers/lead_controller.php
Line Number: 163
dir this is my code
ReplyDelete$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'xls|xlsx';
$config['max_size'] = '1024';
$config['max_width'] = '1024';
$config['max_height'] = '768';
$this -> load -> library('upload', $config);
if (!$this -> upload -> do_upload()) {
$data['message'] = $this -> upload -> display_errors();
} else {
$data['message'] = 'add new success';
}
$upload_data = $this -> upload -> data();
$filename = $upload_data['file_name'];
echo $filename;
require_once 'Excel/excel_reader2.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read($filename);
//$conn = mysql_connect("localhost","root","");
//mysql_select_db("test",$conn);
for ($x = 2; $x sheets[0]["cells"]); $x++) {
$first = $data->sheets[0]["cells"][$x][1];
$middle = $data->sheets[0]["cells"][$x][2];
$last = $data->sheets[0]["cells"][$x][3];
$leads = array(
// 'lead_code_id' => $this -> getLeadCodeId($d2->format('y-m-d')),
'lead_name' => $first,
'client_name' => $middle,
'email_id' => $last,
/// 'date' => $d2->format('y-m-d'),
'active' => 1);
$id = $this -> modellead -> save($leads);
Make sure a file called "excel_reader2.php" exists in "Excel" directory. As the error message implies, obviously a failure of loading this file. Where have you placed the downloaded files in your project? You need to include the file in a proper way.
ReplyDeleteYou can place Excel lib or whatever necessary files within your application libraries. Then include the file in your controller. Something like below.
require_once(APP_PATH."libraries/ExcelReader........");
Dear ,sir this is showing following error:
ReplyDeleteunable to read record.xls file. please send me whole code my mail id rawat_pradeep@ymail.com
Hi, this is something simple right? Please close excel file before uploading or running the script. And verify that your excel file path is correct and can be accessible if it is placed in a folder.
ReplyDeleteDear, sir this is showing following
ReplyDeleteerror A PHP Error was encountered
Severity: Notice
Message: Undefined offset: 0
Filename: controllers/lead_controller.php
Line Number: 170
Total Sheets in this xls file: 0
and
$record= count($data->sheets[0]["cells"]);
count function also not working.
int(0)
tank you sir i got it..nice example.
ReplyDeleteGreat!
ReplyDeleteDear sir how we will upload also xlsx format file. by this code.
ReplyDeleteFor this, you need to add code for uploading the file. You may find several options here. If you need to upload a specific file always, you can rename the file before uploading and then move to a certain folder. In the code, you refers to the file path which would not change unless you are forced to do.
ReplyDeleteWithout renaming file, still you can get the file path and continue.
If you 're not familiar with file uploading, please play around this. It is not something hard.
For a all in one tut, Google pointed me this. http://www.discussdesk.com/import-excel-file-data-in-mysql-database-using-PHP.htm
Dear sir ,
ReplyDeletei have rename file .xls format its not working,if uploading time file extension is .xls its working fine.
this is my code
$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'xls|xlsx';
$config['max_size'] = '1024';
$config['max_width'] = '1024';
$config['max_height'] = '768';
$config['overwrite'] = TRUE;
// $config['file_name'] = $file_name;
// $config['encrypt_name'] = TRUE;
$this -> load -> library('upload', $config);
if (!$this -> upload -> do_upload()) {
$data['message'] = $this -> upload -> display_errors();
} else {
$data['message'] = 'add new success';
}
$upload_data = $this -> upload -> data();
$filename = $upload_data['file_name'];
$dir='./uploads/';
$files1 = scandir($dir);
//var_dump($files1) ;
unset($files1[0]);
unset($files1[1]);
foreach ($files1 as $value)
{
$oldfname='./uploads/'.$value;
$fname="records";
$ext=".xls";
$name=$fname.$ext;
$newfname='./uploads/'.$name;
$file_name=rename($oldfname,$newfname);
}
$uploaded_file_name = $_FILES["userfile"]['name'];
if ($uploaded_file_name != "" || NULL) {
require_once 'Excel/excel_reader2.php';
$dir='./uploads/';
$files1 = scandir($dir);
unset($files1[0]);
unset($files1[1]);
foreach ($files1 as $fname)
{
//$ext = pathinfo($fname, PATHINFO_EXTENSION);
$saved_file_name='./uploads/'.$fname;
// $path = './uploads/' . file_name; /*old path*/
$path=$saved_file_name;
$data = new Spreadsheet_Excel_Reader();
$data -> setOutputEncoding('CP1251');
// $newpath = fopen($path, "r");
$data -> read($path);
error:
The filename ./uploads/records.xls is not readable
yes. phpExcelReader would not work for .xlsx files.
ReplyDeleteYou may need PHPExcel library. It's really rich in capabilities.
You can check here for a sample.
https://www.dropbox.com/s/oao0eskflu8nyz1/PHPExcleReader.zip?dl=0
This is done by another person.
I am getting a error during import, but i can't figger it out what it is. The error is : Notice: Undefined offset:
ReplyDeleteHi,
ReplyDeleteIf you still haven't solved this problem, Please make sure you have set column names properly and excel file has valid content in your context.
Warning: require_once(Excel/reader.php): failed to open stream: No such file or directory in D:\xamp\htdocs\import.php on line 2
ReplyDeleteFatal error: require_once(): Failed opening required 'Excel/reader.php' (include_path='.;D:\xamp\php\PEAR') in D:\xamp\htdocs\import.php on line 2
How can we upload large xlsx file into mysql
ReplyDeleteSuccessfully Uploaded
ReplyDeleteHai this is lakshmi
here i am presenting my problem to you please give the correction on this code
This is my upload excel sheet to database or MYSQL it just inserting zeros to database
what is the problem in this code can you explain it to me.
Successfully Uploaded
ReplyDeleteHai this is lakshmi
here i am presenting my problem to you please give the correction on this code
This is my upload excel sheet to database or MYSQL it just inserting zeros to database
what is the problem in this code can you explain it to me.