My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
Excel  
Notes on Excel
Updated Jul 28, 2011 by mkleehammer

Read Only

You can read Excel spreadsheets pretty easily using the Microsoft ODBC driver. See the ConnectionStrings document for connection string examples.

Microsoft's documentation says that inserts should be possible, but I have never been able to make them work. If you can provide a working example, please do!

Data Types

The excel driver uses the majority data type in first 8 rows to determine the type of each column. So if you have 5 numbers and 3 text values in a column, the column will be considered a float column, but the 3 text values will be returned as NULL!

Comment by abbconsu...@gmail.com, Mar 18, 2009

If you need to make inserts but can't with odbc, you can use pywin32 instead. See http://www.markcarter.me.uk/computing/python/excel.html for some basic example code.

Comment by pastordp...@gmail.com, Jan 25, 2011

I have had no problem with doing updates or inserts on excel files with pyodbc. You do need to make sure that readonly=0 is included in the connection string. Here is a sample connection string...

cnxn=pyodbc.connect("""Driver={Microsoft Excel Driver (.xls)};

Dbq=d:\\datafiles\\XLSfile.xls; readonly=0""", autocommit=True)

Comment by ma...@wler.co.uk, Jan 18, 2013

Data Types:

Is there a way to force the Data type of a column to avoid String values being returned as NULL when the majority of the first 8 rows are numbers?

Comment by max.fell...@gmail.com, Jan 14, 2015

Thanks for that connect call syntax -- using that along with some other info from here: https://www.connectionstrings.com/excel/ I was able to get inserts working (key is "SQL syntax "SELECT Name One?, Name Two? FROM One$?". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.)

-- so if you create a new workbook with the default "Sheet1", this will append to the bottom of the sheet: INSERT INTO [Sheet1$] VALUES ('test')

Powered by Google Project Hosting