| Posted on May 12, 2021 | here is an example of how you can Import Excel to MySQL using PHP. Import Excel to MySQL database is a very important method. Sometimes you have a very big list of items in an excel file and at sometimes you need to import all that list into your database so you can see that list on your website. You may also like PHPExcel import excel to mysql in Codeigniter and How to Convert Excel Sheet into PHP Array using PHPExcel. Download PHPExcel
library. Steps For PHPExcel import Excel to mysql in PHP Step 1. Install PHPExcel Library Unzip or extract the downloaded PHPExcel library files and copy Class directory inside files any folder and call library file. Step 2. Create HTML Form Here we create the HTML form which contains the one file input element and one button. Step 3. Press Upload Excel In this step you
are press upload Excel button after save file one specific folder and read file. PHPExcel_IOFactory read excel file and create array after store one by one record in mysql with check duplicate record.
I tried to search for some plugins to import Excel file into MySQL database, one of them is //code.google.com/p/php-excel-reader/
The tool is so powerful that it displays the entire excel content into html.
However, I think I just need to read the Excel file and extract the contents, for example, into an array, and then write a SQL statement for entering into the database.
Would there be any good codes and packages? Thanks!
JasonMArcher
13.5k22 gold badges55 silver badges51 bronze badges
asked Jul 12, 2012 at 8:33
3
This is best plugin with proper documentation and examples
//github.com/PHPOffice/PHPExcel
Plus point: you can ask for help in its discussion forum and you will get response within a day from the author itself, really impressive.
answered Jul 12, 2012 at 8:37
diEchodiEcho
52.6k41 gold badges172 silver badges241 bronze badges
4
Sometimes I need to import large xlsx files into database, so I use spreadsheet-reader as it can read file per-row. It is very memory-efficient way to import.
<?php // If you need to parse XLS files, include php-excel-reader require('php-excel-reader/excel_reader2.php'); require('SpreadsheetReader.php'); $Reader = new SpreadsheetReader('example.xlsx'); // insert every row just after reading it foreach ($Reader as $row) { $db->insert($row); } ?>//github.com/nuovo/spreadsheet-reader
answered Jan 6, 2015 at 12:25
shukshin.ivanshukshin.ivan
10.7k3 gold badges50 silver badges67 bronze badges
0
If you can convert .xls to .csv before processing, you can use the query below to import the csv to the database:
load data local infile 'FILE.CSV' into table TABLENAME fields terminated by ',' enclosed by '"' lines terminated by '\n' (FIELD1,FIELD2,FIELD3)
Jon Taylor
7,8605 gold badges29 silver badges54 bronze badges
answered Jul 12, 2012 at 9:03
user1259132user1259132
3202 gold badges3 silver badges11 bronze badges
1
If you save the excel file as a CSV file then you can import it into a mysql database using tools such as PHPMyAdmin
Im not sure if this would help in your situation, but a csv file either manually or programatically would be a lot easier to parse into a database than an excel file I would have thought.
EDIT: I would however suggest looking at the other answers rather than mine since @diEcho answer seems more appropriate.
answered Jul 12, 2012 at 8:36
Jon TaylorJon Taylor
7,8605 gold badges29 silver badges54 bronze badges
I wrote an inherited class:
<?php class ExcelReader extends Spreadsheet_Excel_Reader { function GetInArray($sheet=0) { $result = array(); for($row=1; $row<=$this->rowcount($sheet); $row++) { for($col=1;$col<=$this->colcount($sheet);$col++) { if(!$this->sheets[$sheet]['cellsInfo'][$row][$col]['dontprint']) { $val = $this->val($row,$col,$sheet); $result[$row][$col] = $val; } } } return $result; } } ?>
So I can do this:
<?php $data = new ExcelReader("any_excel_file.xls"); print_r($data->GetInArray()); ?>
answered Feb 23, 2015 at 13:42