[php] PHP import Excel into database (xls & xlsx)

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!

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

https://github.com/nuovo/spreadsheet-reader

I wrote an inherited class:

<?php_x000D_
class ExcelReader extends Spreadsheet_Excel_Reader { _x000D_
 _x000D_
 function GetInArray($sheet=0) {_x000D_
_x000D_
  $result = array();_x000D_
_x000D_
   for($row=1; $row<=$this->rowcount($sheet); $row++) {_x000D_
    for($col=1;$col<=$this->colcount($sheet);$col++) {_x000D_
     if(!$this->sheets[$sheet]['cellsInfo'][$row][$col]['dontprint']) {_x000D_
      $val = $this->val($row,$col,$sheet);_x000D_
      $result[$row][$col] = $val;_x000D_
     }_x000D_
    }_x000D_
   }_x000D_
  return $result;_x000D_
 }_x000D_
_x000D_
}_x000D_
?>

So I can do this:

<?php_x000D_
_x000D_
 $data = new ExcelReader("any_excel_file.xls");_x000D_
 print_r($data->GetInArray());_x000D_
_x000D_
?>

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.