a few words about web development

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:



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

Comments

There are no comments yet