Reading from Excel files w/ODBC

wrhodes

I have a MS Excel file that I need to read data from to update a series of MySQL tables. I am trying to use the ODBC connector but running into problems when trying execute the query on the file. (Tracing the ODBC connection shows a successfull connection through the DSN.)

The filename is DBImport.xls and I think the period is causing the problem, BUT not sure how to handle it. I have tried using . with no success.

Here's the code followed by the result:

<?
$filename = "c:\DBImport.xls";

If(!($connection = odbc_connect("Ximport","",""))) {
print(odbc_error());
}
else {
print("ODBC Connection Successfull<br>\n");
}
$Query = "SELECT titleid from $filename";

$result = odbc_do($connection, $Query);

print("Cursor: " .odbc_cursor($result) ."<br>\n");

while(odbc_fetch_row($result))
{
$t = odbc_result($result,1);
print("$t<br>\n");
}
odbc_close($connection);
?>

Result:

ODBC Connection Successfull

Warning: odbc_do(): SQL error: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'xls'. Make sure the object exists and that you spell its name and the path name correctly., SQL state S0002 in SQLExecDirect in C:\spm\dev\testodbc.php on line 12

Any help would be greatly appreciated.

William

Thomas_Liao

$Query = "select titleid from $filename";

The content in variable $filename should be a TABLE's name in general Database system. But if you want to read a sheet of Excel file from ODBC, it should be a Range Name, neither the sheet name nor the filename neither.

Hope this can solve your problem.