06-21-2022 07:50 AM
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.
06-26-2022 10:23 PM
Hello @Dhara Mandal
Could you please elaborate on the requirements for using multiple .mdb files?
06-26-2022 10:35 PM
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.
06-26-2022 10:48 PM
“how to open mdb file in python” Code Answer
06-26-2022 10:54 PM
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
06-27-2022 06:02 AM
Hi @Dhara Mandal, Please check the path where you stored the Access file.
06-28-2022 08:21 PM
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.
06-27-2022 07:23 AM
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-...
06-28-2022 08:15 PM
Hi I have installed the library as well as uploaded the jar files too. Still I am facing the same error.
06-28-2022 08:16 PM
07-18-2022 07:40 AM
@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
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group