Insertion into Excel file via ODBC

[GoogleCodeExporter]

I'm using the latest version of pyodbc with Python 2.5 on Windows.

I connect to a simple Excel file which is linked through ODBC, with
autocommit=True. I can do SELECT queries without any trouble. When I an
INSERT query, it seems like it goes through, but it really doesn't. After
doing the insert query, if I do a select query, I can actually see the
newly inserted rows. However, after I close Python and open the Excel file,
I don't see any of the newly inserted rows there.

I know that read only is disabled in the ODBC data source configuration,
and I also tried disabling it explicitly in the connection string, but this
was of no use.

I have had no problem inserting stuff into other data sources such as
Access files, etc.

This issue is driving me nuts.

Original issue reported on code.google.com by abbconsu...@gmail.com on 4 Feb 2009 at 4:59

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I even tried explicitly committing at the end but it didn't help.

Original comment by abbconsu...@gmail.com on 4 Feb 2009 at 5:04

[GoogleCodeExporter]

You noted you are using the latest version of pyodbc, but it doesn't hurt to 
check. 
You are using a 2.1.x version, right?  This series fixes some problems from the 
2.0.x
series.

I'm not familiar with the Excel driver.  One thing I noticed in my Google 
search is a
lot of Excel connection strings include ReadOnly 
(http://support.microsoft.com/kb/165866)

If you don't already have ReadOnly=0, can you try it?  Alternatively, if you are
using a DSN, make sure the Read Only checkbox is cleared.  I would expect an 
error in
this case, so I don't really think it is the problem, but we have to work 
through them.

Now, all of the examples I've found for inserts create a "table" first (e.g.
http://support.microsoft.com/kb/141809).  Are you sure you don't need to do 
something
like that?  I have no idea yet.

Finally, for most issues here I end up asking for the same thing: Can you 
upload an
ODBC trace?  That will give us a lot more information.

Original comment by mkleehammer on 4 Feb 2009 at 5:46

• Changed state: Investigating

[GoogleCodeExporter]

Thanks for the fast response!

I'm using pyodbc 2.1.3 with Python 2.6. (I previously erroneously mentioned 
Python
2.5 out of habit.)

I had tried including ReadOnly=0 and also without it, but the insertion didn't 
work
either way. I made sure that Read Only in the ODBC DSN configuration is 
unchecked.

I also tried connecting directly to the file instead of through ODBC; this 
worked the
same way. I tried with both .xls (Excel 97-2003) and .xlsx (Excel 2007) files 
using
two different Microsoft Excel drivers in ODBC.

The ODBC trace is attached. It includes the sending of a select, insert, 
followed by
a select again, all of which seem to work as expected for me. Looking at the 
trace,
however, is the insert going through correctly or not?
The insert line in my code is:
excel_cursor.execute('INSERT INTO "'+excel_worksheet+'" (TestCol1) VALUES
(?)','TestValue3')
I don't know tracing works, but I ask because I don't see the string 
"TestValue3"
anywhere near the insert statement in the trace.

Original comment by abbconsu...@gmail.com on 4 Feb 2009 at 6:34

Attachments:

• SQL.LOG

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I also tried inserting using a named range in Excel, but that worked the same 
way too.

I sure hope that creating a table is not necessary, because if it is necessary, 
that
would defeat the goal of adding rows to an existing sheet.

Original comment by abbconsu...@gmail.com on 4 Feb 2009 at 6:45

[GoogleCodeExporter]

Everything looks correct.

A possibility: The Excel driver determines the data type of a column from the 
first 8
rows -- it chooses the majority data type.  If you insert something that doesn't
match that data type, it inserts NULL, which would be blank.  If you are only
inserting 1 column, you wouldn't see anything new.

For example, if 5 of the first 8 rows have numbers, the driver will assume it 
is a
column of numbers.  Non-number inserts will be ignored.

Could this explain it?

Original comment by mkleehammer on 4 Feb 2009 at 7:13

[GoogleCodeExporter]

I filled the first 10 rows of the sheet manually and retried, but got the same
results. I also tried with another spreadsheet that has over a 1000 rows, but 
didn't
get anywhere. The data type is consistent.

Original comment by abbconsu...@gmail.com on 4 Feb 2009 at 7:40

[GoogleCodeExporter]

Would it be possible for you to try to reproduce this and see how it works for 
you?

Original comment by abbconsu...@gmail.com on 4 Feb 2009 at 7:41

[GoogleCodeExporter]

OK, I've done a *lot* of testing and research and I can't find any examples of 
simply
appending items that works.

This appears to be a driver issue -- ODBC is just not that good for inserting 
data
into Excel.  (The reading seems to work pretty well.)

I'm thinking I'm going to have to close this since it really isn't a pyodbc 
issue.

Original comment by mkleehammer on 18 Mar 2009 at 10:00

[GoogleCodeExporter]

Is there a way to make Excel appends fail and raise an exception?

Original comment by abbconsu...@gmail.com on 18 Mar 2009 at 10:09

[GoogleCodeExporter]

Not really -- there isn't any way to know what works and what doesn't unless the
driver returns errors to us.

Original comment by mkleehammer on 18 Mar 2009 at 11:51

• Changed state: WontFix

[GoogleCodeExporter]

At the very least, there should be notes in the documentation to state that 
Excel
appends are known to silently fail.

Original comment by abbconsu...@gmail.com on 19 Mar 2009 at 12:18

[GoogleCodeExporter]

Good idea.  This is a small start: http://code.google.com/p/pyodbc/wiki/Excel

Original comment by mkleehammer on 19 Mar 2009 at 2:55

[GoogleCodeExporter]

sheet_name = u"Test Sheet"
sheet_filename = ur'c:\odbc.xls'
con = pyodbc.connect(u'Driver={Microsoft Excel Driver 
(*.xls)};FIRSTROWHASNAMES=0;READONLY=FALSE;CREATE_DB="%s;DBQ=%s' % 
(sheet_filename,sheet_filename), autocommit=True)

cur = con.cursor()
cur.execute(u"CREATE TABLE `%s`(Name TEXT,Age NUMBER)" % sheet_name)
for i in xrange(5000):
    cur.execute(u"INSERT INTO `%s` VALUES('Bruno Brutalinsky',%i)" % (sheet_name,i))
cur.close()

Works great on my system!

Original comment by alexey.kvirc@googlemail.com on 8 Aug 2009 at 8:58

[GoogleCodeExporter]

I noticed that alexey.kvirc actually closes the cursor in Comment 15.  Perhaps 
that 
helps?  Under the covers, that would cause the HSTMT to be closed; perhaps the 
Excel 
driver does something different when this happens?

Original comment by mkleehammer on 8 Aug 2009 at 5:27

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

mkleehammer: Closing the cursor did not matter. I also tested a lot of other 
points.
So far I haven't been able to pin down the issue.

I guess the issue can be closed. I wonder if anything change in pyodbc that 
affects
this. Also note that when I initially had the issue, I was using Python 2.5, 
but I
tried with 2.6 now. Also note that I was If you close the issue, I suppose you 
could
also consider deleting the wiki page on Excel.

Original comment by abbconsu...@gmail.com on 8 Aug 2009 at 6:58

[GoogleCodeExporter]

I had a similar issue using native ODBC API with Excel ODBC driver. Cause of 
the lost 
updates was a code path that skipped calling ::SQLDisconnect before freeing 
connection 
handle. Now it all works

Original comment by farid.zi...@gmail.com on 19 May 2010 at 3:58

[GoogleCodeExporter]

I struggled with this for a while in c++. I eventually succeeded by removing 
"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}" from my 
connection string and instead used "ODBC;DSN=Excel Files;DBQ=FileName.xls". 

Original comment by paul.mar...@gmail.com on 19 Dec 2011 at 8:23