topic Re: Access multiple .mdb files using Python in Machine Learning

Access multiple .mdb files using Python

Dhara — Tue, 21 Jun 2022 14:50:11 GMT

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.

Re: Access multiple .mdb files using Python

User16764241763 — Mon, 27 Jun 2022 05:23:56 GMT

Hello @Dhara Mandal

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

Re: Access multiple .mdb files using Python

Dhara — Mon, 27 Jun 2022 05:35:11 GMT

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.

Re: Access multiple .mdb files using Python

Brooksjit — Mon, 27 Jun 2022 05:48:16 GMT

“how to open mdb file in python” Code Answer

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)

My Insite

Re: Access multiple .mdb files using Python

Dhara — Mon, 27 Jun 2022 05:54:36 GMT

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

Re: Access multiple .mdb files using Python

Hubert-Dudek — Mon, 27 Jun 2022 14:23:47 GMT

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-access-accdb-database-into-databricks-from-a-mounted-drive

Re: Access multiple .mdb files using Python

Dhara — Wed, 29 Jun 2022 03:15:55 GMT

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

Re: Access multiple .mdb files using Python

Dhara — Wed, 29 Jun 2022 03:16:13 GMT

Re: Access multiple .mdb files using Python

Dhara — Wed, 29 Jun 2022 03:21:18 GMT

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.

Re: Access multiple .mdb files using Python

User16764241763 — Mon, 18 Jul 2022 14:40:26 GMT

@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-windows-platform-linux-or-mac