CODEWITHSUNDEEP
pythonpyodbc
Burf2000
Burf2000

Reputation: 5193

Pyodbc : Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found (0)

I have been sent an Access file to open, I am using a Mac and need to open it in Python

import pyodbc

DBfile = '/Users/burfies1/Dropbox/pricing/data.accdb' 
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBfile)  
cur = conn.cursor()

I get the following error

conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBfile)  #    user/password can be used
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found (0) (SQLDriverConnect)")

Upvotes: 6

Views: 14305

Answers (2)

Pelonomi Moiloa
Pelonomi Moiloa

Reputation: 582

https://medium.com/@wenyu.z/reading-ms-access-mdb-files-on-mac-969a176baa7a

This medium article by Wenyu Zhao was very useful for getting it into python using mac mdbtools as posted in previous answer. The following is copy pasted from his post

import pandas as pd
import subprocess


def show_data(path='<file_name>.mdb', table='<table_name>'):
    tables = subprocess.check_output(["mdb-export", path, table])
    return tables.decode().split('\n')

def convert_df(path, table):
    d = show_data(path, table)
    columns = d[0].split(',')
    data = [i.split(',') for i in d[1:]]
    df = pd.DataFrame(columns=columns, data=data)
    return df

Upvotes: 0

Burf2000
Burf2000

Reputation: 5193

The best solution for me was to just install MDBTools

https://github.com/brianb/mdbtools

Then use mdb-export to create a csv

mdb-export data.accdb TABLE > output_file.csv

Upvotes: 7

Related Questions