Access multiple .mdb files using Python

[Dhara]

Hi, 

I wanted to access multiple .mdb access files which are stored in the Azure Data Lake Storage(ADLS) or on Databricks File System using Python. 

Is it possible to guide me how can I achieve it? It would be great if you can share some code snippets for the same.

[User16764241763]

Hello @Dhara Mandal​ 

Could you please elaborate on the requirements for using multiple .mdb files?​ 

[Dhara]

Few of our customers are sending us some data in the .mdb files very frequently and we wanted to access data for few tables from those files. Then want to perform some data manipulations on top of it ultimately saving it into the excel files.

I want to access the .mdb files and load the data into the data frame and then do multiple things on top of it.

[Brooksjit]

“how to open mdb file in python” Code Answer

1. import pyodbc.
2. ​
3. conn = pyodbc. connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path where you stored the Access file\file name.accdb;')
4. cursor = conn. cursor()
5. cursor. execute('select * from table name')
6. for row in cursor. fetchall():
7. print (row)

My Insite

[Dhara]

I have tried this code earlier but its not working. I am getting the following error:

rror: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found (0) (SQLDriverConnect)")

---------------------------------------------------------------------------

Error Traceback (most recent call last)

<command-753409017935809> in <module>

1 import pyodbc

----> 2 conn = pyodbc. connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=/dbfs/mnt/serviceslob/msaccess_file/FLReports_UK_M_5-16-22.mdb;')

3 cursor = conn. cursor()

4 cursor. execute('select * from table name')

5 for row in cursor. fetchall():

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found (0) (SQLDriverConnect)")

Command took 0.23 seconds -- by dm185336@ncr.com at 6/27/2022, 1:52:38 AM on Purviewdemo

[Dhara]

I have uploaded the file on the ADLS and have tried giving that path too but still I am getting the error. Please find the attached screenshot which has the file in that path and I have used the same path in the code too.

[Hubert-Dudek]

It can be as well missing driver.

You can install that library jaydebeapi and point to jar with the JDBC driver (http://ucanaccess.sourceforge.net/site.html).

You can look to that discussion as well: https://community.databricks.com/s/question/0D53f00001bdlQoCAI/what-is-the-best-way-to-read-in-a-ms-...

[Dhara]

Hi I have installed the library as well as uploaded the jar files too. Still I am facing the same error.

[User16764241763]

@Dhara Mandal​ Can you please try below?

# cmd 1
%pip instal pandas_access
 
# cmd 2
import pandas_access as mdb
 
db_filename = '/dbfs/FileStore/Campaign_Template.mdb'
 
# Listing the tables.
for tbl in mdb.list_tables(db_filename):
    print(tbl)
 
# Read a small table.
df = mdb.read_table(db_filename, "Campaign_Table")
df.head()

 

This article has some examples

https://stackoverflow.com/questions/25596737/working-with-an-access-database-in-python-on-non-window...