21 August 2010

Import Excel (XLS) document into MySQL database

Another straight to the point solution

The best way to read XLS files in PHP is using PHPExcelReader. It's a great class because it does not use any COM objects, however the downloadable package on SourceForge sometimes contains invalid XLS or example files, so I prepared a working package and you get get it here:

Download XLS.zip

And here's an example how to read an XLS file and create SQL queries for MySQL database:

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

//columns:
$sql = "INSERT INTO `table` (";
for ($j = 2; $j <= $data->sheets[0]['numCols']; $j++) 
{
	$sql .= "`" . mysql_escape_string($data->sheets[0]['cells'][1][$j]) . "`,";
}
$sql = substr($sql, 0, -1) . ") VALUES\r\n";
//cells
for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) 
{
	$sql .= "(";
	for ($j = 2; $j <= $data->sheets[0]['numCols']; $j++) 
	{
		$sql .= "'" . mysql_escape_string($data->sheets[0]['cells'][$i][$j]) . "',";
	}
	$sql = substr($sql, 0, -1) . "),\r\n";
}
$sql =  substr($sql, 0, -3) . ";";

echo '<pre>';
echo $sql;
[code]

It will output something like this:
[code]
INSERT INTO `table` (`First`,`Middle`,`Last`,`Email`) VALUES
('Jane','H','Kowalski','jane@kowalski.com'),
('Michael','J','Canne','michael.canne@yahoo.com');

Import Excel (XLS) document into MySQL database

Comments