Importing Excel files into MySQL with PHP

If you have Excel files that need to be imported into MySQL, you can import them easily with PHP. First, you will need to download some prerequisites:

PHPExcelReader - http://sourceforge.net/projects/phpexcelreader/

Spreadsheet_Excel_Writer - http://pear.php.net/package/Spreadsheet_Excel_Writer

Once you’ve downloaded both items, upload them to your server. Your directory listing on your server should have two directories: Excel (from PHPExcelReader) and Spreadsheet_Excel_Writer-x.x.x (from Spreadsheet_Excel_Writer). To work around a bug in PHPExcelReader, copy oleread.inc from the Excel directory into a new path:

Spreadsheet/Excel/Reader/OLERead.php

The PHPExcelReader code will expect OLERead.php to be in that specific location. Once that is complete, you’re ready to use the PHPExcelReader class. I made an example Excel spreadsheet like this:

Name                Extension   Email
----------------------------------------------------
Jon Smith           2001        jsmith@domain.com
Clint Jones         2002        cjones@domain.com
Frank Peterson      2003        fpeterson@domain.com

After that, I created a PHP script to pick up the data and insert it into the database, row by row:

require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('exceltestsheet.xls');

$conn = mysql_connect("hostname","username","password");
mysql_select_db("database",$conn);

for ($x = 2; $x < = count($data->sheets[0]["cells"]); $x++) {
    $name = $data->sheets[0]["cells"][$x][1];
    $extension = $data->sheets[0]["cells"][$x][2];
    $email = $data->sheets[0]["cells"][$x][3];
    $sql = "INSERT INTO mytable (name,extension,email)
        VALUES ('$name',$extension,'$email')";
    echo $sql."\n";
    mysql_query($sql);
}

After the script ran, each row had been added to the database table successfully. If you have additional columns to insert, just repeat these lines, using an appropriate variable for each column:

sheets[0]["cells"][$row_number][$column_number];

For more details, you can refer to a post in Zend’s Developer Zone.