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.
<?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); } ?>
Even your 1cent donation is appreciated.
Igor Karpov
June 19, 2014 at 3:13 pm
This is fine solution for small xls files. But if you want read 10k rows, this way will not work.
For big files you should use NoXLS API Parser http://noxls.net/documentation
lakshmi
February 27, 2017 at 3:24 pm
Successfully Uploaded
Hai 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.
Akhilesh
November 25, 2014 at 6:28 pm
$data->read(‘a.xls’);
what this line do…….
my browser shows the a.xls file is not readable
sidd
January 16, 2015 at 6:33 pm
if the code is in server will your code search for a.xls on server or client machine
mahesh
February 17, 2015 at 5:01 pm
where is require_once ‘Excel/reader.php’; file
Rajitha
February 18, 2015 at 10:57 am
Hi,
There should be a folder named ‘Excel’ in PHPExcelReader download.
Guruprasad
April 7, 2015 at 4:00 pm
i dont have any Excel folder, i have PHPExcel folder and no reader.php file in it
sabari
July 10, 2015 at 12:32 pm
Thank u very much… i got solution from this one
pradeep
July 19, 2015 at 12:50 pm
Hello Sir i am using codeigniter framework why not work this code plzz help me.
A 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
pradeep
July 19, 2015 at 12:54 pm
dir 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’;
$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);
Rajitha
July 19, 2015 at 2:14 pm
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.
You 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……..”);
pradeep
July 20, 2015 at 6:28 pm
Dear ,sir this is showing following error:
unable to read record.xls file. please send me whole code my mail id rawat_pradeep@ymail.com
Rajitha
July 20, 2015 at 9:52 pm
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.
pradeep
July 22, 2015 at 11:49 am
Dear, sir this is showing following
error 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)
pradeep
July 22, 2015 at 1:21 pm
tank you sir i got it..nice example.
Rajitha
July 22, 2015 at 3:51 pm
Great!
pradeep
July 23, 2015 at 10:56 am
Dear sir how we will upload also xlsx format file. by this code.
Rajitha
July 23, 2015 at 1:39 pm
For 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.
Without 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
pradeep
July 30, 2015 at 6:21 pm
Dear sir ,
i 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
Rajitha
July 30, 2015 at 9:54 pm
yes. phpExcelReader would not work for .xlsx files.
You 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.
Lodewijk
January 8, 2016 at 3:47 am
I am getting a error during import, but i can’t figger it out what it is. The error is : Notice: Undefined offset:
Rajitha
January 8, 2016 at 7:43 am
Hi,
If 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.
pradeep
June 23, 2016 at 4:24 pm
Warning: require_once(Excel/reader.php): failed to open stream: No such file or directory in D:\xamp\htdocs\import.php on line 2
Fatal error: require_once(): Failed opening required ‘Excel/reader.php’ (include_path=’.;D:\xamp\php\PEAR’) in D:\xamp\htdocs\import.php on line 2
Vishal
August 12, 2016 at 4:20 pm
How can we upload large xlsx file into mysql
deva
November 24, 2017 at 12:54 pm
if i want insert excel file dynamically not to particular given one then this time how to import excel file in mysql database using php