Python: Adding excel file to an access database

Question

I am using pyodbc to access an access (accdb) file. I want to add an excel workbook into the access database programatically, but cannot find an API to do so. Here is my current code:

import pyodbc
DBFile = r'C:\Documents and Settings\IA.accdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBFile)

exFile = r'C:\Documents and Settings\IA_2006.xls'
conn1 = pyodbc.connect('DRIVER={Microsoft Excel Driver \ 
                       (*.xls)};DBQ='+exFile,autocommit=True)

cursor = conn.cursor()
####IA_1 is a table within IA.accdb
cursor.execute('select * from IA_1')
row = cursor.fetchone()
####For debugging, print a line
if row:
        print row

How should I import the data from the excel file (IA_2006.xls) into the IA.accdb?

mechanical_meat · Accepted Answer · 2010-11-08 22:51:26Z

3

It seems like you got to a certain point and gave up.
Don't give up! :-)

You've made the connection to the Excel spreadsheet, now you need to read it*.

curs1 = conn1.cursor()
# the following returns list of tuples
excel_results = curs1.execute('select [a_column]
                               from [Sheet1$]').fetchall()

Then you can insert to your MS Access db, e.g.:

curs.executemany('insert into mytable (mycolumn) values (?)', excel_results)
conn.commit()

*If in doubt, Excel sheet names can be found by running the following:

for row in curs1.tables():
    print row.table_name

edited Nov 8, 2010 at 22:51

answered Nov 8, 2010 at 22:32

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python: Adding excel file to an access database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related