Nick BartlettPHP Developer in Minnesota

Sorry to say, I don’t have the magic hidden PHP function to do this as the process is a bit more involved.  I built functionality today to download an Excel inventory file for my side business and then have my website automatically read it and update the inventory.  This has been the missing automated link for a while and I’m finally getting around to it.  I won’t go over how to download a file via FTP with PHP in detail, but basically you use the ftp_connect, ftp_login, and ftp_get functions built into PHP.  Below I describe the magic involved with converting an Excel file into a delimited file that your existing code (like mine) can handle.

1. Download Spreadsheet Excel Reader from sourceforge

2. I received an error when testing because the file OLERead.php could not be found.  The path is incorrect in reader.php on line 31. Update it to “require ‘oleread.inc’;” so it includes the oleread.inc file from the same directory.

3. Give my code below a whirl and you’re done.  You can save it to a file as I did or print it to the screen by just echoing the $output variable.

//path and name for inventory file after download
$localFilePath = 'inventory/'.date('Ymd').'_inventory.xls';
 
//init excel reader
$reader = new Spreadsheet_Excel_Reader();
 
//set output encoding
$reader->setOutputEncoding("UTF-8");
 
//read file
$reader->read($localFilePath);
 
//set field delimiter
$fieldDelimiter = "\t";
 
//set row return/delimiter
$rowDelimiter = "\r";
 
//set new file name
$newFilename = 'inventory/'.date('Ymd').'_inventory.txt';
 
//loop through all rows
for ($i = 1; $i <= $reader->sheets[0]["numRows"]; $i++) {
 
	//loop through all columns for this row
	for ($j = 1; $j <= $reader->sheets[0]["numCols"]; $j++)	{
 
		//set field
		$output .= "\"".$reader->sheets[0]["cells"][$i][$j]."\"" . $fieldDelimiter;
	}
	//end row
	$output .= $rowDelimiter;
}
 
//save new delimited file
file_put_contents($newFilename, $output);