6

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)")
Burf2000
  • 5,001
  • 14
  • 58
  • 117
  • Possible duplicate of [Read an Access database in Python on non-Windows platform (Linux or Mac)](http://stackoverflow.com/questions/25596737/read-an-access-database-in-python-on-non-windows-platform-linux-or-mac) – Gord Thompson Mar 23 '17 at 09:25
  • Not sure it is mate, it does not mention my error code or problem and only has a solution for Ubuntu – Burf2000 Mar 23 '17 at 09:40
  • `Microsoft Access Driver (*.mdb, *.accdb)` is the name of Microsoft's ODBC driver for Microsoft Access. It is only available for Windows. – Gord Thompson Mar 23 '17 at 09:46
  • So, what driver should I use, Jython / UCanAccess is a mess for OSX – Burf2000 Mar 23 '17 at 10:10

2 Answers2

6

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
Burf2000
  • 5,001
  • 14
  • 58
  • 117
  • Good find! I was pleasantly surprised to discover mdbtools was available on mac via homebrew (brew install mdbtools) – mcnutt Jan 19 '20 at 23:06
0

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
Pelonomi Moiloa
  • 516
  • 5
  • 12