Reading an Excel file in PHP

Question 1

I'm trying to read an Excel file (Office 2003). There is an Excel file that needs to be uploaded and its contents parsed.

Via Google, I can only find answers to these related (and insufficient topics): generating Excel files, reading Excel XML files, reading Excel CSV files, or incomplete abandoned projects. I own Office 2003 so if I need any files from there, they are available. It's installed on my box but isn't and can't be installed on my shared host.

Edit: so far all answers point to PHP-ExcelReader and/or this additional article about how to use it.

Question 2

I use PHP-ExcelReader to read xls files, and works great.

Question 3

Filesize isn't a good measure when using PHPExcel, it's more important to get some idea of the number of cells (rowsxcolumns) in each worksheet.

If you have no need for styling, are you calling:

$objReader->setReadDataOnly(true);

before loading the file?

If you don't need to access all worksheets, or only certain cells within a worksheet, look at using

$objReader->setLoadSheetsOnly(array(1,2))

or

$objReader->setLoadSheetsOnly(1)

or defining a readFilter

Are you using cell caching? If so, what method? That slows down the load time.

Question 4

There is a microsoft knowledge base article that lays out all the ways this is possible.

http://support.microsoft.com/kb/321686

I think using OPENROWSET or OPENDATASOURCE will be the easiest way, without the wizard. (see Distributed Queries)

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:testxltest.xls', [Customers$])

See OPENROWSET documentation, with examples lower down the page.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

Manually

Right click on the database name/go to task and then select import data, as a source select an excel file that you created before and choose it's path on the next page select sql server as destination

Question 5

Importing from Excel files (XLS) is way harder than improting from CSV files. Usually I save my XLS to CSV with Excel then work on this CSV with PHP...

Look at PHP function fgetcsv at: http://ca.php.net/manual/en/function.fgetcsv.php

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />n";
        }
    }
    fclose($handle);
}
?>

If you still want to load XLS directly from PHP it's possible (but how reliable)... A quick seach resulted in http://sourceforge.net/projects/phpexcelreader/ which might be helpful.

Question 6

Check the first examples here: http://www.connectionstrings.com/excel

What often goes wrong is that Excel will estimate the type of a column based upon the first X rows. When after that the values don't match, these rows get empty values. I'm afraid that going into the registry is sometime the only way to get the Excel driver to scan all rows first (as described in the connectionstrings.com article).

Play around with the HDR and IMEX settings in your environment. In some cases that will help as well.

Reading an Excel file in PHP

Answers

php,import-from-excel