How to read Excel file & Insert data into MySQL Database using PHP
I have received many requests from my readers to write tutorial on Excel file import in MySQL database, so today I am going to give you this tutorial on how to read excel file and insert data into MySQL DB using PHP. I have used a php library php-excel-reader its a very simple and easy to understand library to get excel data in your MySQL database. You can also print data in same excel format in HTML and display on browser.
You can get that library from here.
Database Details:
database name => phpgang
table name => excel
db.sql
Database file run in your MySQL to create database and add data in table.
CREATE TABLE `excel` ( `id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `eid` VARCHAR( 100 ) NOT NULL , `name` VARCHAR( 200 ) NOT NULL , `email` VARCHAR( 200 ) NOT NULL , `dob` VARCHAR( 40 ) NOT NULL ) ENGINE = MYISAM ;
db.php
Edit this file as per your database credentials.
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'username');
define('DB_PASSWORD', 'password');
define('DB_DATABASE', 'database');
$connection = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>index.php
Contains HTML and PHP include library and insert records in database.
<?php
ini_set("display_errors",1);
require_once 'excel_reader2.php';
require_once 'db.php';
$data = new Spreadsheet_Excel_Reader("example.xls");
echo "Total Sheets in this xls file: ".count($data->sheets)."<br /><br />";
$html="<table border='1'>";
for($i=0;$i<count($data->sheets);$i++) // Loop to get all sheets in a file.
{
if(count($data->sheets[$i][cells])>0) // checking sheet not empty
{
echo "Sheet $i:<br /><br />Total rows in sheet $i ".count($data->sheets[$i][cells])."<br />";
for($j=1;$j<=count($data->sheets[$i][cells]);$j++) // loop used to get each row of the sheet
{
$html.="<tr>";
for($k=1;$k<=count($data->sheets[$i][cells][$j]);$k++) // This loop is created to get data in a table format.
{
$html.="<td>";
$html.=$data->sheets[$i][cells][$j][$k];
$html.="</td>";
}
$eid = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][1]);
$name = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][2]);
$email = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][3]);
$dob = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][4]);
$query = "insert into excel(eid,name,email,dob) values('".$eid."','".$name."','".$email."','".$dob."')";
mysqli_query($connection,$query);
$html.="</tr>";
}
}
}
$html.="</table>";
echo $html;
echo "<br />Data Inserted in dababase";
?>First of all it count your sheets in excel file then get rows of each sheet and print rows of each column in html table and insert it in database.
You can get excel_reader2.php by downloading source code.
That’s all for today’s tutorial i hope it helps. Please feel free to give us your feedback in comments.
Tutorial Categories:
This script, besides being not-very-well-written, is dangerous and can destroy your database if someone hands you a malicious Excel file. ALWAYS escape ALL data you enter into a database! For more information read this: http://www.php.net/manual/en/security.database.sql-injection.php
Thanks for your comment Just edited code.
Notice: Use of undefined constant cells – assumed ‘cells’ in /opt/lampp/htdocs/extras/excel-to-mysql-using-php/index.php on line 19
There is error in the syntax [cells], should be [‘cells’]
what is the value of cells everywhere in index.php. Could you please tell its significance.
i have subscribed but still im not able to download
i have subscribed….but not able to download this code.. check your database
problem
Hi, I have a problem and kindly help ,me. I have created my mysql DB known as mwashu, this DB has 4 tables. the tables names are: 1. blinformation,2. vessel, 3. containers and4. housebl. I want to upload excel file with 4 sheets into the mysql tables. the excel sheets are havinng the same names as my tables’ names. I am having problems. pls i need help on how to have php code upload the excel sheets into my DB tables.
you can post the code here or you may send me via my email [email protected]
regards
NB: the excel sheets are large enough
why it is inserting duplicate rows all the time when i refresh ?
how can browse my own file but with same name like yours? but from another location other code directory
gave your list 2 of my emails, still not able to download! please fix asap and send a note out
Deprecated: Function split() is deprecated in C:wampwwwpropertyexcel_reader2.php on line 79
i have got this error plz help thanks for code
Use explode insted of split()
i need how to insert word doc data into database using php
nice job i need excel to mysql db using php
when i use this script, it imports only 551 rows at a time to db, how to increase the size of rows so that it would be able to import atleast 1000 rows to db.
Any help would be greatly appreciated !
Nice One.. Itz working
Is there any for Read data from .xlsx
Thnx
cells appears to be undefined. how to fix it?
Please HELP!
Notice: Use of undefined constant cells – assumed ‘cells’ in F:Php_Codeindex.php on line 16
Thank you so much, it’s work but i have received many notification as below:
Notice: Use of undefined constant cells – assumed ‘cells’ in C:Program Files (x86)EasyPHP-DevServer-14.1VC11datalocalwebexindex.php on line 14
Can you tell me how to fix?
instead of writing [cells] write like this [‘cells’];
I cant insert my data in mysql. Its giving me a lot of errors because of the query. When I write it this way –> $query = “INSERT INTO excel(kurs,potok,grupa,spec,kod,vid,disziplina,zan,prepodavatel,zala,stud,den,ot,do,sedmica) VALUES(‘”.$kurs.”‘,'”.$potok.”‘,'”.$grupa.”‘,'”.$spec.”‘,'”.$kod.”‘,'”.$vid.”‘,'”.$disziplina.”‘,'”.$zan.”‘,'”.$prepodavatel.”‘,'”.$zala.”‘,'”.$stud.”‘,'”.$den.”‘,'”.$ot.”‘,'”.$do.”‘,'”.$sedmica.”‘)”;
is giving me this error:Parse error: syntax error, unexpected T_VARIABLE
Use single quotes in values
thanks. ^ ^
can anybody tell me which functions are used from excel_reader2.php?
can anybody tell me which functions are used from excel_reader2.php?
thanks suhu , i’ll try it
Did nothing but run the example…
Why does it not work?
Notice: iconv(): Detected an incomplete multibyte character in input string in /Applications/XAMPP/xamppfiles/htdocs/KazarkDB/excel/excel_reader2.php on line 1718
Notice: iconv(): Detected an incomplete multibyte character in input string in /Applications/XAMPP/xamppfiles/htdocs/KazarkDB/excel/excel_reader2.php on line 1718
You just have to change the first line of code in index.php to
ini_set(“display_errors”,0);
SO that it will not display any Notices.
there is no database file in downloaded file why is it ? please thanks
Hi. I have followed guideline. The code is working nicely in my local machine. However, In production server Its not working. I see no data is added to the database. How to fix this issue? Thanks
It does not work
it says ” The filename sample.xls is not readable “.
thanks :).. this code worked.. but how about an action from the page to import an excel file.
Was you able to connect your php with the google spread sheet on live basis
you have to create web service using Google API to do something like that.
Can this work if data is in google spreadsheet and i want to put it in my SQL database.?
please help me…
Deprecated: Assigning the return value of new by reference is deprecated in C:xampphtdocssampleexcel_reader2.php on line 916
The filename sample.xlsx is not readable
How to get name of the sheets using excel_reader2.php library.
Thanx for the great tutorial.But it’s notify me see below image
these warnings are due to using [cells] without quote so use [‘cells’].
My integer value is replacing with ‘GENERAL’ keyword and it comes inside cellinfo array .
So How I get my actual value on same array ?
hi, help me
what the solution for this?
Parse error: syntax error, unexpected ‘new’ (T_NEW) in C:xampphtdocsexcelexcel-to-mysql-using-phpexcel_reader2.php on line 916
Hi @enichen:disqus
Have you resolved the error. As I am also facing the same issue.
to solve line 916: $this->_ole =& new OLERead(); (it’s Deprecated “=&” , put
only “=”)
$this->_ole = new OLERead();
incase this part too come up just replace
to solve line 844 (and other with split like): $parts = split(“;”,$format);
Split it’s been Deprecated use instead “explode”:$parts = explode(“;”,$format);
can read xlsx?
Above example only works for given example excell file only.its not working for new excell file why?
Hello sir, Thank you for giving a nice tutorial on Excel to mysql
i have imported this functionality but i am getting error
Parse error: syntax error, unexpected ‘[‘, expecting identifier (T_STRING) or variable (T_VARIABLE) or ‘{‘ or ‘$’ in C:xampphtdocsexcelToDbindex.php on line 28
for this code => $eid = mysqli_real_escape_string($conn,$data->[$i][‘cells’][$j][1]);
Please Convey me
Thank you
Hello Sir
I am getting below error
Parse error: syntax error, unexpected ‘new’ (T_NEW) in C:wamp64wwwReadExcelphp-excel-reader-2.21excel_reader2.php on line 916
Please provide the solution.
Parse error: syntax error, unexpected ‘new’ (T_NEW) in D:xampp7.0.9htdocsexcelexcel_reader2.php on line 916
how to solve this problem?
to solve line 916: $this->_ole =& new OLERead(); (it’s Deprecated “=&” , put
only “=”)
$this->_ole = new OLERead();
what about undefined offset? it shows me undefined offset so many times.
how can I solve undefined offset error?
i am a member of your site, but when i try to download, it shows subscribe.
Notice: iconv(): Detected an incomplete multibyte character in input string in C:xampphtdocsphp-excel-reader-2.21excel_reader2.php on line 1720
date is converted like Mm/Dd/Yyyy Hh:mm:ss insted of 01/31/2018 00:00:00
I’m getting date as mmm d YYYY instead of aug 04 2018
good
Hello,
I am getting Parse error:
syntax error, unexpected ‘new’ (T_NEW) in ..excel_reader2.php on line 916