March 27, 2014 9:40 am

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.

read-excel-file-and-insert-

[wpdm_file id=93]

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.

Author Huzoor Bux

I am Huzoor Bux from Karachi (Pakistan). I have been working as a PHP Developer from last 5+ years, and its my passion to learn new things and implement them as a practice. Basically I am a PHP developer but now days exploring more in HTML5, CSS and jQuery libraries.


Tutorial Categories:

58 responses to “How to read Excel file & Insert data into MySQL Database using PHP”

  1. Toon Spin says:

    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

  2. Neesha says:

    what is the value of cells everywhere in index.php. Could you please tell its significance.

  3. prassu says:

    i have subscribed but still im not able to download

  4. shrikant kadam says:

    i have subscribed….but not able to download this code.. check your database
    problem

  5. mwashu says:

    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

  6. Simra Javed says:

    why it is inserting duplicate rows all the time when i refresh ?

  7. Remmy says:

    how can browse my own file but with same name like yours? but from another location other code directory

  8. jeff says:

    gave your list 2 of my emails, still not able to download! please fix asap and send a note out

  9. pandit says:

    Deprecated: Function split() is deprecated in C:wampwwwpropertyexcel_reader2.php on line 79

    i have got this error plz help thanks for code

  10. murali says:

    i need how to insert word doc data into database using php

  11. sivaraman says:

    nice job i need excel to mysql db using php

  12. Subhash Kapil says:

    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 !

  13. Raj Prince says:

    Nice One.. Itz working
    Is there any for Read data from .xlsx
    Thnx

  14. mastermind06 says:

    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

  15. Lão Còi says:

    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?

  16. Lidia Stancheva says:

    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

  17. Palmer Kung says:

    thanks. ^ ^

  18. Taushif Ali says:

    can anybody tell me which functions are used from excel_reader2.php?

  19. Taushif Ali says:

    can anybody tell me which functions are used from excel_reader2.php?

  20. thanks suhu , i’ll try it

  21. Sam Alexander says:

    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

    • sudhir koimattur says:

      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.

  22. Redz says:

    there is no database file in downloaded file why is it ? please thanks

  23. Nizam Uddin GM Sikder says:

    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

  24. Rockbaj Indra says:

    It does not work
    it says ” The filename sample.xls is not readable “.

  25. Erwin Justin says:

    thanks :).. this code worked.. but how about an action from the page to import an excel file.

  26. Yakin Shah says:

    Can this work if data is in google spreadsheet and i want to put it in my SQL database.?

  27. Jose Angelito Ranojo Diaz says:

    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

  28. Tharunjeni says:

    How to get name of the sheets using excel_reader2.php library.

  29. Milan Gajera says:

    Thanx for the great tutorial.But it’s notify me see below image

  30. shailendra says:

    My integer value is replacing with ‘GENERAL’ keyword and it comes inside cellinfo array .
    So How I get my actual value on same array ?

  31. Eni Chen says:

    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

    • mayank bhuvnesh says:

      Hi @enichen:disqus
      Have you resolved the error. As I am also facing the same issue.

      • Bamidele Alexander Oba says:

        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);

  32. Don Zanurano says:

    can read xlsx?

  33. Upender Upendra says:

    Above example only works for given example excell file only.its not working for new excell file why?

  34. Vinay Prabhu says:

    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

  35. mayank bhuvnesh says:

    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.

  36. Ranjith says:

    Parse error: syntax error, unexpected ‘new’ (T_NEW) in D:xampp7.0.9htdocsexcelexcel_reader2.php on line 916

  37. Selim Parvez says:

    what about undefined offset? it shows me undefined offset so many times.

  38. Selim Parvez says:

    how can I solve undefined offset error?

  39. pavan says:

    i am a member of your site, but when i try to download, it shows subscribe.

  40. Srinivasulu T says:

    Notice: iconv(): Detected an incomplete multibyte character in input string in C:xampphtdocsphp-excel-reader-2.21excel_reader2.php on line 1720

  41. Kavyanand Gowda says:

    date is converted like Mm/Dd/Yyyy Hh:mm:ss insted of 01/31/2018 00:00:00

  42. Mahesh says:

    I’m getting date as mmm d YYYY instead of aug 04 2018

  43. Usha Kalmani says:

    Hello,

    I am getting Parse error:
    syntax error, unexpected ‘new’ (T_NEW) in ..excel_reader2.php on line 916

Leave a Reply

Your email address will not be published. Required fields are marked *