Using php to read data from excel file using a odbc connection ?

I have an excel file on win2003 server, and using php I want to read the data on a particular worksheet in the file so I can populate a table with the data. The sheet has 3 worksheets, Sheet1,Sheet 2,Sheet3. I want the data from Sheet1. Sheet 1 contains a table with row 1 containing header names for the columns.
I have created a dsn for the excel file called test using admin tools/data sources called test (but I didn't see a way of specifying Sheet1 data only ?)
In my php I open the dsn with
$myDB=odbc_connect(test, "",""); if (!$myDB){ echo"couldn't connect"; exit;}
which seem to work as it returns true. But any functions I use such as odbc_columns,odbc_exec,odbc_fetch rows fail.
Can anyone send me pointers to examples that do this ?
Who is Participating?
Richard QuadlingConnect With a Mentor Senior Software DeveloperCommented:
I use ODBC to read Excel worksheets also.

Some info.

1 - An excel workbook can be considered to be the "database".
2 - An excel worksheet can be considered to be a "table".

Some php snippets.

DNSless connection - i.e. all connection details are in the PHP function - portable (taken from one of my classes).

odbc_connect('Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=' . realpath($this->s_DB) . ';DefaultDir=' . dirname(realpath($this->s_DB)) . ('' !== $this->s_DBSpecific ? ';' . $this->s_DBSpecific : '') , '', '');

Table names are in the following format (EXACTLY AS BELOW - QUOTE INCLUDED)


There are other sheets in a workbook. Ranges and print ranges also come back in the list of tables, but only those matching the above format are REAL "tables".

This is the PHP code I use to wrap my table names for Excel ...

return 1 == preg_match("`^'.+\\$'$`", $s_TableName) ? $s_TableName : "'$s_TableName\$'";


REALPATH to the files also.

Loganathan NatarajanLAMP DeveloperCommented:
i would suggest to export those excel sheet as MS-Access database then connect it with ODBC functions
Richard QuadlingSenior Software DeveloperCommented:
logudotcom, why? If you can use ODBS to talk directly to an Excel file, why bother with adding another layer?

Loganathan NatarajanLAMP DeveloperCommented:
@ RQuadling,

I just pointed the other way or may be if he interest he can do it., sorry if this takes wrong way.,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.