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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard QuadlingSenior 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.