I tried to search for some plugins to import Excel file into MySQL database, one of them is http://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!
This is best plugin with proper documentation and examples
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.
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);
}
?>
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)
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.
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());
?>You can use PHPOFFICE as "diEcho" said. And then use bin2hex() function to insert data to database (SQL SERVER or MySQL) as a BLOB data type.
bin2hex() function helped me alot.
