How to automatically pull data from access into excel using a python script

Question

I am looking to automatically pull data from Access to Excel using a python script. Manually, I have to do the following inside Excel:

Click on "Data"--> "from Access". Step 1
Select the Data Source (.accdb) Step 2
Input the credentials for Oracle ODBC Driver Connect Step 3

This fairly easy process inside Excel I would like to automate using a python script. Could you get me an idea how this could be achieved? Could I possibly run a macro from python that does these 3 steps ?

Thanks for your help!

Greetings,

Daniel

Possible duplicate of Copy data from MS Access to MS Excel using Python — running.t
– running.t, Commented Apr 2, 2019 at 14:07

M.Fooladgar · Accepted Answer · 2019-04-02 14:08:12Z

1

Your question has two parts:

Reading an access database; to do so you can use pyodbc to read an access database and run query on it. e.g.:

import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path where you stored the Access file\file name.accdb;')
cursor = conn.cursor()
cursor.execute('select * from table name')

for row in cursor.fetchall():
    print (row)

Write your data to Excel file; for this you can use XlsxWriter to write excel files:

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write('A1', 'Hello world')

workbook.close()

answered Apr 2, 2019 at 14:08

M.Fooladgar

3841 gold badge4 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

How to automatically pull data from access into excel using a python script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related