Retrieve data from multiple .mdb files using Python.

JamesBrown54 — Wed, 01 May 2024 17:25:42 GMT

Hello,

I'm interested in accessing several .mdb Access files stored in either Azure Data Lake Storage (ADLS) or the Databricks File System using Python. Could you provide guidance on how to accomplish this? It would be immensely helpful if you could also share some code snippets for reference.

Re: Retrieve data from multiple .mdb files using Python.

NandiniN — Thu, 09 Jan 2025 11:41:18 GMT

To access multiple .mdb (Microsoft Access) files stored in Azure Data Lake Storage (ADLS) or the Databricks File System (DBFS) using Python, you can use the pandas_access library. Below are the steps and code snippets to help you achieve this:

Install the pandas_access library:
```
%pip install pandas_access
```

Import the necessary libraries and read the .mdb file:

import pandas_access as mdb

# Path to your .mdb file in DBFS
db_filename = '/dbfs/FileStore/Campaign_Template.mdb'

# Listing the tables in the .mdb file
for tbl in mdb.list_tables(db_filename):
    print(tbl)

# Reading a specific table into a DataFrame
df = mdb.read_table(db_filename, "Campaign_Table")
df.head()

Handling paths in ADLS: If your .mdb files are stored in ADLS, you need to mount the ADLS container to DBFS first. Here is an example of how to mount an ADLS container:

configs = {
    "fs.azure.account.auth.type": "OAuth",
    "fs.azure.account.oauth.provider.type": "org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider",
    "fs.azure.account.oauth2.client.id": "<client-id>",
    "fs.azure.account.oauth2.client.secret": "<client-secret>",
    "fs.azure.account.oauth2.client.endpoint": "https://login.microsoftonline.com/<tenant-id>/oauth2/token"
}

dbutils.fs.mount(
    source = "abfss://<container-name>@<storage-account-name>.dfs.core.windows.net/",
    mount_point = "/mnt/<mount-name>",
    extra_configs = configs
)

After mounting, you can access the .mdb files using the mounted path:

db_filename = '/mnt/<mount-name>/path/to/your/file.mdb'

Reading the .mdb file from the mounted path:

import pandas_access as mdb

# Path to your .mdb file in the mounted ADLS container
db_filename = '/mnt/<mount-name>/path/to/your/file.mdb'

# Listing the tables in the .mdb file
for tbl in mdb.list_tables(db_filename):
    print(tbl)

# Reading a specific table into a DataFrame
df = mdb.read_table(db_filename, "Campaign_Table")
df.head()

These steps should help you access and manipulate data from .mdb files stored in ADLS or DBFS using Python in Databricks. If you encounter any issues, ensure that the paths are correctly specified and that the necessary libraries are installed.

Re: Retrieve data from multiple .mdb files using Python.

NandiniN — Thu, 09 Jan 2025 11:42:47 GMT

These are a couple of blogs and docs too

https://docs.databricks.com/en/connect/storage/azure-storage.html

topic Retrieve data from multiple .mdb files using Python. in Machine Learning

Retrieve data from multiple .mdb files using Python.

Re: Retrieve data from multiple .mdb files using Python.

Re: Retrieve data from multiple .mdb files using Python.