getValue from .xls returning mangled text

Dec 13, 2013 at 9:53 AM
Hi all,

I am trying to use PHPExcel to read an .xls file and use the data to populate a MySQL DB. I am finding however that the text in the DB is not being returned accurately as expected.
The code I am using is quite simple and an extract of the relevant bits is shown below.
$inputFileName = 'Capital20131209.xls';
echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using PHPExcel_Reader_Excel5<br />';
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 

echo ' File type is '.$inputFileType;
$objPHPExcel = $objReader->load($inputFileName);

$worksheet = $objPHPExcel->getActiveSheet();

$cells = array('A1','B1','C1','A2','B2','C2');
foreach($cells as $cell) {
   $value = $worksheet->getCell($cell)->getValue();
   echo '<b>'.$cell.' Value is </b>'.$value."<br />\n";
}
The output is below
File type is CSV

A1 Value is PK!
B1 Value is
C1 Value is
A2 Value is p
B2 Value is
C2 Value is

The actual excel text is below
P_DATE CAP ASINDEX
2013-12-05 12430964103763.236000 38864.840000

Any ideas please?
Dec 15, 2013 at 2:27 PM
It would seem that PHPExcel does not correctly identify the file type, it relies partly on the extension and my assumption is that it is wrong (xls-> he tries a reader Excel5, finds that it does not, it considers that this is a csv).
The first two characters displayed as PK, renames your file in xlsx or force the use of an Excel 2007 reader
Dec 23, 2013 at 11:23 AM
Thanks for your response. Is there a way to correctly identify the file type? I have a feeling this document I am trying to read was a .dbf.
Dec 23, 2013 at 12:14 PM
Open the file with a hex editor or a simple viewer of DBase files. If he tells you that he cannot open it, is that it is not a DBase file. If you use the first, look if the signature matches (see http://www.dbf2002.com/dbf-file-format.html for information on the format).
If you case a file of this format, PHPExcel will not handle it directly, however, there is a php extension that knows how to handle it. Conjunction with PHPExcel, you can transform the original file in Excel file.
Dec 30, 2013 at 9:37 PM
I believe this file is originally a binary excel workbook (.xlsb) and that is the reason it cannot be read using the phpexcel module. I'm digging around to see if I can convert it programatically into .xls before processing it in PHP.

Thanks