PHP import Excel into database (xls & xlsx)

Question

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!

Hardecanute

Google is your friend: de77.com/php/import-excel-xls-document-into-mysql-database – Buehrer

Is php-excel-reader not sufficient for you? – Priscilapriscilla

@Priscilapriscilla - title mentions both xls and xlsx, so php-excel-reader probably isn't adequate (unless it's been updated since I last looked) as it only handles xlsx – Gensler

Answer 1

This is best plugin with proper documentation and examples

https://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.

Annunciata

Thanks... I try to oblige, though I can't guarantee a response within a day – Gensler

@MarkBaker , but most of the problems already solved in your discussion forum :) – Annunciata

PHPExcel last version, 1.8.1, was released in 2015. The project is no longer maintained and should not be used anymore. All users should migrate to its direct successor PhpSpreadsheet, or another alternative. In a few months, the GitHub project will be marked as archived, so everything will be read-only. – Faustinafaustine

PHPOffice/PHPExcel is now deprecated. Direct link to its successor PHPOffice/PhpSpreadsheet: github.com/PHPOffice/PhpSpreadsheet – Indignation

Answer 2

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

Marutani

Answer 3

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)

Exenterate

So far it's the best and fastest way I have tried with huge files. The problem is I receive xls files with multiple sheets (same format) and each with thousands of records so I am still stuck here how to combine all in one csv sheet.. – Blavatsky

Answer 4

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.

Leftwards

Answer 5

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

?>

Schmo

Answer 6

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.

Leilani