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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

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
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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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

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
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
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
PHP

From novice to tech pro — start learning today.