Fork me on GitHub

Project Notes

Reading MDB Files

Reading Microsoft Access database files on a Mac with Python

:arrow_forward: return to the Catalog

Notes

I have some old Microsoft Access databases lying around … I wonder if there’s anything useful in them? And can I read them from my Mac without installing a whole suite of Microsoft tools?

There’s at least one way to do this that I’ve found to work: mdbtools for *nix MDB support and pandas_access for a tidy python wrapper.

I ran these tests with python 3..

Installing

$ brew install mdbtools
...
Installing dependencies for mdbtools: gettext, sqlite, python and glib
...
$ pip3 install -r requirements.txt

``

### Some `pandas_access` Basics

Loading a table as a [pandas.DataFrame](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html)

import pandas_access as mdb

df = mdb.read_table(‘quotes.mdb’, ‘Quotations’)


Getting column names.
`df.columns` returns a
[pandas.Index](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Index.html).
and `df.columns` returns a
[numpy.ndarray](https://docs.scipy.org/doc/numpy/reference/generated/numpy.ndarray.html)

print(‘Columns: {}’.format(list(df.columns.values))) => Columns: [‘AuthorLast’, ‘AuthorFirst’, ‘Quote’, ‘QuoteDate’, ‘Circumstance’]


### Some Examples

Listing tables in a database...

$ ./show_tables.py quotes.mdb Listing the tables in quotes.mdb.. Quotations


Listing records in a table...

$ ./show_records.py quotes.mdb Quotations Listing the records in quotes.mdb::Quotations.. Table has 2945 rows Columns: [‘AuthorLast’, ‘AuthorFirst’, ‘Quote’, ‘QuoteDate’, ‘Circumstance’] ————– AuthorLast: Edison AuthorFirst: Thomas Quote: …I have not failed. I’ve just found 10,000 ways that won’t work. QuoteDate: nan Circumstance: nan ————– AuthorLast: Asimov AuthorFirst: Isaac Quote: [John] Dalton’s records, carefully preserved for a century, were destroyed during the World War II bombing of Manchester. It is not only the living who are killed in war. QuoteDate: nan Circumstance: nan ————– AuthorLast: Gates AuthorFirst: Bill Quote: 640K ought to be enough for anybody. QuoteDate: nan Circumstance: In 1981 ————– AuthorLast: Berra AuthorFirst: Yogi Quote: 95% of this game is half mental. QuoteDate: nan Circumstance: nan ————– AuthorLast: Dirksen AuthorFirst: Everett Quote: A billion here, a billion there, and pretty soon you’re talking about real money. QuoteDate: nan Circumstance: nan ————– AuthorLast: Marx AuthorFirst: Groucho Quote: A child of five would understand this. Send someone to fetch a child of five. QuoteDate: nan Circumstance: nan …(etc)… ```

Credits and References

About LCK#86 python
Project Source on GitHub Return to the Project Catalog

This page is a web-friendly rendering of my project notes shared in the LittleCodingKata GitHub repository.

LittleCodingKata is my collection of programming exercises, research and code toys broadly spanning things that relate to programming and software development (languages, frameworks and tools).

These range from the trivial to the complex and serious. Many are inspired by existing work and I'll note credits and references where applicable. The focus is quite scattered, as I variously work on things new and important in the moment, or go back to revisit things from the past.

This is primarily a personal collection for my own edification and learning, but anyone who stumbles by is welcome to borrow, steal or reference the work here. And if you spot errors or issues I'd really appreciate some feedback - create an issue, send me an email or even send a pull-request.