2

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

Abs
  • 56,052
  • 101
  • 275
  • 409

3 Answers3

2

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.

increddibelly
  • 1,221
  • 1
  • 15
  • 25
  • I agree, ODBC is a pain but I want to keep it inline with other code I have written for Access which works great. Do you know of a query that can show all sheets? Maybe this will help work out what each sheet is actually called! – Abs Feb 17 '11 at 13:43
1

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

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Using the dollar sign gives me `Syntax error in INSERT INTO statement.`. – Abs Feb 17 '11 at 13:42
  • I get an error `Too few parameters. Expected 1.` btw my queries look like this `INSERT INTO [Sheet1$] (A2) VALUES (TEST)` - is this correct? – Abs Feb 17 '11 at 15:03
  • @Abs No, it is not. If you have set HDR=Yes in your connection string, the first row contains field (column) names, if HDR=No, then the field names are F1,F2 ... Fn, numbering from the start of the table, for a whole sheet, this would be Column A = Field F1. You can also refer to ranges [Sheet1$B2:C45], if you wish, in this case, with no headers, Column B = F1 – Fionnuala Feb 17 '11 at 15:13
  • To continue, you probably want: `(F1) VALUES ('TEST')` Note the quotes. – Fionnuala Feb 17 '11 at 15:14
  • Ah I see, I seem to be getting `Operation must use an updateable query.` after those two changes. It looks like I am getting closer with your help. Btw, is there any documentation for this? How did you find out you've got use F1 as Field 1? How would you go to the next row? – Abs Feb 17 '11 at 16:00
  • You need to get the read only stuff in somewhere `...Driver={Microsoft Excel Driver " _ & "(*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=False;Dbq=` – Fionnuala Feb 17 '11 at 16:30
  • For documentation, have a look at W3Schools ADODB information, that is one of the places I have picked up information, there are a lot of others, but nothing large springs to mind. – Fionnuala Feb 17 '11 at 16:31
  • Next row where? You have a recordset, so all the SQL stuff works: "SELECT FROM [Sheet1$] WHERE F2 = 'MyText' AND F5 = 2 AND F3 = Now()". I do not know how to work with recordsets in PHP, but I am sure there is a lot of documentation. – Fionnuala Feb 17 '11 at 16:34
  • 1
    Thanks for your help Remou - I also found this article quite useful. http://support.microsoft.com/kb/257819 – Abs Feb 18 '11 at 15:48
  • @Abs Yes, that article is cited in the link I posted in my answer. It is useful. – Fionnuala Feb 18 '11 at 15:52
0

@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

Constantine
  • 650
  • 9
  • 15
Dharyll
  • 1