Using Microsoft Excel via an ODBC Driver with PHP

2
votes

I am trying to insert data and select data from Excel using an ODBC connection. I have managed to connect to it:

$dbh = new PDO("odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=$file_name", $username, $password);

However, I am unsure how to query Excel as there is minimal documentation for this! I attempted this:

$query = "INSERT INTO $sheet ($cell) VALUES ($value)";

$result = $dbh->query($query);

But this leads to an error:

Array ( [0] => 07002 [1] => -3010 [2] => [Microsoft][ODBC Excel Driver] The Microsoft Office Access database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly. (SQLExecute[-3010] at ext\pdo_odbc\odbc_stmt.c:254) [3] => 07002 )

By object, hopefully they mean an excel sheet?

Thanks all for any help

4
phpexcelodbc
Does "Sheet1" actually exist in your workbook, or should you be writing to "Worksheet1"? – Mark Baker
Sheet1 does exist, when I open excel, I can see the first sheet is named "Sheet1". – Abs

4 Answers

2
votes

do you need to use ODBC? I always find it a pain in the *.
If you're not forced to use ODBC, I suggest you try the PHPExcel class available here (not affiliated) which has made reading and writing Excel from PHP very easy for me on numerous occasions.

if you've opened the Excel sheet with Excel and verified that it does in fact contain a worksheet with the name 'Sheet1', you may want to broaden your search - is the worksheet or cell perhaps locked against editing? that can result in the weirdest errors.

1
votes

Sheet1 is called Sheet1$ in Excel. Excel workbooks opened in this way are read-only by default.

0
votes

$insert="insert into [Sheet1$] VALUES ('$client_id','$client_name') ";

0
votes

@Abs I found a solution for it. We are same problem. But my code is working now.

First step: go to odbc dsn setup. Uncheck the read only

Second step: assuming your .xlsx has a client_id and client_name and table name is Sheet1

here's the code

$insert="insert into [Sheet1$](client_id,client_name) VALUES ('$client_id','$client_name') ";
$result=odbc_exec($yourconnection,$insert);

uncheck the read only in your odbc dsn setup you can found it in configure