My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
  Advanced search   Search tips   Subscriptions
Issue 32: Insertion into Excel file via ODBC
2 people starred this issue and may be notified of changes. Back to list
Status:  WontFix
Owner:  ----
Closed:  Mar 2009


 
Reported by abbconsu...@gmail.com, Feb 4, 2009
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.
Feb 4, 2009
#2 abbconsu...@gmail.com
I even tried explicitly committing at the end but it didn't help.
Feb 4, 2009
Project Member #3 mkleehammer
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.
Status: Investigating
Feb 4, 2009
#4 abbconsu...@gmail.com
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.
SQL.LOG
18.8 KB   View   Download
Feb 4, 2009
#6 abbconsu...@gmail.com
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.
Feb 4, 2009
Project Member #7 mkleehammer
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?
Feb 4, 2009
#8 abbconsu...@gmail.com
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.
Feb 4, 2009
#9 abbconsu...@gmail.com
Would it be possible for you to try to reproduce this and see how it works for you?
Mar 18, 2009
Project Member #10 mkleehammer
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.
Mar 18, 2009
#11 abbconsu...@gmail.com
Is there a way to make Excel appends fail and raise an exception?
Mar 18, 2009
Project Member #12 mkleehammer
Not really -- there isn't any way to know what works and what doesn't unless the
driver returns errors to us.

Status: WontFix
Mar 18, 2009
#13 abbconsu...@gmail.com
At the very least, there should be notes in the documentation to state that Excel
appends are known to silently fail.
Mar 18, 2009
Project Member #14 mkleehammer
Good idea.  This is a small start: https://code.google.com/p/pyodbc/wiki/Excel
Aug 8, 2009
#15 alexey.kvirc@googlemail.com
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!
Aug 8, 2009
Project Member #16 mkleehammer
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?
Aug 8, 2009
#18 abbconsu...@gmail.com
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.
May 19, 2010
#19 farid.zi...@gmail.com
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
Dec 19, 2011
#20 paul.mar...@gmail.com
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". 

Powered by Google Project Hosting