Reading data from Excel via ODBC

Hi I'm trying to connect a PHP file to an Excel file, all on a remote Windows server.

So I've uploaded a file called "Book2.xls" into a folder called "testfolder".

DNS is called "test".
DBQ is hshome\myhostingpackage\mywebsite.com\testfolder\Book2.xls
where the relevant values have been changed to my own.

My code is as below (nicked from another post on EE) but it gives the following error, can someone please help???? Many thanks:

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides., SQL state S1009 in SQLConnect in D:\hshome\myhostingpackage\mywebsite.com\testfolder\test.php on line 3

Warning: odbc_tables(): supplied argument is not a valid ODBC-Link resource in D:\hshome\myhostingpackage\mywebsite.com\testfolder\test.php on line 5

Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in D:\hshome\myhostingpackage\mywebsite.com\testfolder\test.php on line 7

<?
$hCon=odbc_pconnect("test", "","");
$hStmt=odbc_tables($hCon);
 
while(odbc_fetch_row($hStmt))
{
      echo "<h1>Contents of Worksheet ".odbc_result($hStmt, "TABLE_NAME")."</h1>";
 
      $sql="SELECT * FROM [".odbc_result($hStmt, "TABLE_NAME")."]";
 
      $hStmt2=odbc_exec($hCon, $sql);
 
      odbc_result_all($hStmt2);
 
}
?>

Open in new window

kbitAsked:
Who is Participating?
 
Richard QuadlingSenior Software DeveloperCommented:
You don't need a DSN. You can use a DNS-less connection.See Example #1 DNS-less connections in http://docs.php.net/odbc_connect.
0
 
NerdsOfTechTechnology ScientistCommented:
make sure it is a system DSN (ODBC) in Windows, not user DSN.
0
 
kbitAuthor Commented:
Attached is my screenshot of the ODBC setup page
dns.jpg
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
kbitAuthor Commented:
Is there a way (using PHP) to fully set up all the ODBC parameters in code instead of using the screen I've shown? Thanks
0
 
NerdsOfTechTechnology ScientistCommented:
Test #1
$conn=odbc_connect('test','','');
$query="SELECT * FROM sheet1";
$result=odbc_exec($conn,$query);
while (odbc_fetch_row($result))
{
  $field1=odbc_result($result,1);
  echo $field1 . '<br/>';
}
odbc_close($conn); 

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
There is probably an another way to do it; however, I have not found a way other than admin to create the connection.

I suggest that you convert your data to mysql instead for increased flexibility and scalability nonetheless so that you can utilize the suitability of table structures etc.
0
 
NerdsOfTechTechnology ScientistCommented:
Thanks RQ
0
 
Richard QuadlingSenior Software DeveloperCommented:
Once you've got the connection, there are several "table" types.

A worksheet is a "table", but so are other things like named lists, etc.

I think you have to use table names like this ...

'sheet1$'

With the quotes and the $ symbol.

The quotes may be optional for sheets without a space in the name.
0
 
NerdsOfTechTechnology ScientistCommented:
DNS-less

Nice!
// Microsoft Excel
$excelFile = realpath('C:/ExcelData.xls');
$excelDir = dirname($excelFile);
$connection = odbc_connect("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=$excelFile;DefaultDir=$excelDir" , '', '');

Open in new window

0
 
kbitAuthor Commented:
I got it to work without the DNS being set up!!!

Below is the winning code, many thanks for all the suggestions and help.

<?
 
// Microsoft Excel
$excelFile = realpath(D:\hshome\myhostingpackage\mywebsite.com\testfolder\Book2.xls');
$excelDir = dirname($excelFile);
$connection = odbc_connect("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=$excelFile;DefaultDir=$excelDir" , '', '');
 
$query="SELECT * FROM [names$]";
$result=odbc_exec($connection,$query);
while (odbc_fetch_row($result))
{
  $field1=odbc_result($result,1);
  echo $field1 . '<br/>';
}
odbc_close($connection);
 
?>

Open in new window

0
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.