cancel
Showing results for 
Search instead for 
Did you mean: 
Machine Learning
cancel
Showing results for 
Search instead for 
Did you mean: 

Access multiple .mdb files using Python

Dhara
New Contributor III

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.

10 REPLIES 10

User16764241763
Honored Contributor

Hello @Dhara Mandal​ 

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

Dhara
New Contributor III

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
New Contributor III

“how to open mdb file in python” Code Answer

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

My Insite

Dhara
New Contributor III

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

Kaniz
Community Manager
Community Manager

Hi @Dhara Mandal​, Please check the path where you stored the Access file.

Dhara
New Contributor III

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
Esteemed Contributor III

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
New Contributor III

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

Dhara
New Contributor III
 

User16764241763
Honored Contributor

@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()

image 

This article has some examples

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

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.