{"id":609,"date":"2014-03-27T09:40:25","date_gmt":"2014-03-27T09:40:25","guid":{"rendered":"http:\/\/www.phpgang.com\/?p=609"},"modified":"2014-03-29T13:53:51","modified_gmt":"2014-03-29T13:53:51","slug":"how-to-read-excel-file-insert-data-into-mysql-database-using-php","status":"publish","type":"post","link":"https:\/\/www.phpgang.com\/how-to-read-excel-file-insert-data-into-mysql-database-using-php_609.html","title":{"rendered":"How to read Excel file & Insert data into MySQL Database using PHP"},"content":{"rendered":"

I have received many requests<\/a> from my readers to write tutorial on Excel file import in MySQL<\/a> database, so today I am going to give you this tutorial on\u00a0how to read excel file and insert data into MySQL DB using PHP<\/a>. I have used a php library php-excel-reader<\/strong>\u00a0its 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.<\/p>\n

\"read-excel-file-and-insert-\"<\/p>\n

[wpdm_file id=93]<\/div>\n

You can get that library from here<\/a>.<\/p>\n

Database Details:<\/strong><\/p>\n

database name => phpgang
\ntable name => excel<\/p>\n

db.sql<\/strong><\/p>\n

Database file run in your MySQL to create database and add data in table.<\/p>\n

CREATE TABLE  `excel` (\r\n `id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,\r\n `eid` VARCHAR( 100 ) NOT NULL ,\r\n `name` VARCHAR( 200 ) NOT NULL ,\r\n `email` VARCHAR( 200 ) NOT NULL ,\r\n `dob` VARCHAR( 40 ) NOT NULL\r\n) ENGINE = MYISAM ;<\/pre>\n
db.php<\/strong><\/p>\n
Edit this file as per your database credentials.<\/p>\n
<?php\r\ndefine('DB_SERVER', 'localhost');\r\ndefine('DB_USERNAME', 'username');\r\ndefine('DB_PASSWORD', 'password');\r\ndefine('DB_DATABASE', 'database');\r\n$connection = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);\r\n?><\/pre>\n
index.php<\/strong><\/p>\n
Contains HTML and PHP include library and insert records in database.<\/p>\n
<?php\r\n\r\nini_set(\"display_errors\",1);\r\nrequire_once 'excel_reader2.php';\r\nrequire_once 'db.php';\r\n\r\n$data = new Spreadsheet_Excel_Reader(\"example.xls\");\r\n\r\necho \"Total Sheets in this xls file: \".count($data->sheets).\"<br \/><br \/>\";\r\n\r\n$html=\"<table border='1'>\";\r\nfor($i=0;$i<count($data->sheets);$i++) \/\/ Loop to get all sheets in a file.\r\n{\t\r\n\tif(count($data->sheets[$i][cells])>0) \/\/ checking sheet not empty\r\n\t{\r\n\t\techo \"Sheet $i:<br \/><br \/>Total rows in sheet $i  \".count($data->sheets[$i][cells]).\"<br \/>\";\r\n\t\tfor($j=1;$j<=count($data->sheets[$i][cells]);$j++) \/\/ loop used to get each row of the sheet\r\n\t\t{ \r\n\t\t\t$html.=\"<tr>\";\r\n\t\t\tfor($k=1;$k<=count($data->sheets[$i][cells][$j]);$k++) \/\/ This loop is created to get data in a table format.\r\n\t\t\t{\r\n\t\t\t\t$html.=\"<td>\";\r\n\t\t\t\t$html.=$data->sheets[$i][cells][$j][$k];\r\n\t\t\t\t$html.=\"<\/td>\";\r\n\t\t\t}\r\n\t\t\t$eid = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][1]);\r\n\t\t\t$name = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][2]);\r\n\t\t\t$email = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][3]);\r\n\t\t\t$dob = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][4]);\r\n\t\t\t$query = \"insert into excel(eid,name,email,dob) values('\".$eid.\"','\".$name.\"','\".$email.\"','\".$dob.\"')\";\r\n\r\n\t\t\tmysqli_query($connection,$query);\r\n\t\t\t$html.=\"<\/tr>\";\r\n\t\t}\r\n\t}\r\n\r\n}\r\n\r\n$html.=\"<\/table>\";\r\necho $html;\r\necho \"<br \/>Data Inserted in dababase\";\r\n?><\/pre>\n
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.<\/p>\n
You can get excel_reader2.php<\/em> by downloading source code.<\/p>\n
That’s all for today’s tutorial i hope it helps. Please feel free to give us your feedback in comments.<\/p>\n
Facebook<\/a><\/blockquote><\/div><\/div>
Tutorial Categories:<\/strong>
\n        \"Database\"<\/a><\/div>
\n        \"MySQLi\"<\/a><\/div>
\n        \"PHP\"<\/a><\/div><\/div>","protected":false},"excerpt":{"rendered":"
I have received many requests<\/a> from my readers to write tutorial on Excel file import in MySQL<\/a> database, so today I am going to give you this tutorial on\u00a0how to read excel file and insert data into MySQL DB using PHP<\/a>. I have used a php library php-excel-reader<\/strong>\u00a0its 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.<\/p>\n
\"read-excel-file-and-insert-\"<\/p>\n","protected":false},"author":1,"featured_media":610,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"twitterCardType":"","cardImageID":0,"cardImage":"","cardTitle":"","cardDesc":"","cardImageAlt":"","cardPlayer":"","cardPlayerWidth":0,"cardPlayerHeight":0,"cardPlayerStream":"","cardPlayerCodec":"","footnotes":""},"categories":[164,5,190,3],"tags":[431,6,623,430,432],"_links":{"self":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts\/609"}],"collection":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/comments?post=609"}],"version-history":[{"count":0,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts\/609\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/media\/610"}],"wp:attachment":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/media?parent=609"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/categories?post=609"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/tags?post=609"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}