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

CrisIt's not working with xslx files, is it ? Tks!
de77No, sorry. This only works with XLS files. XLSX files are basically ZIP files with XML inside so you might want to just unzip them and edit XML.