access-in-pandas.md

Reading MS Access data in Pandas

1. Download and install 32-bit Python 3.9 (this is the latest version that works with Access drivers) https://www.python.org/ftp/python/3.9.13/python-3.9.13.exe
2. Create a virtualenv: mkvirtualenv -p 3.9 pandas-32bit
3. Install pandas 2.0.3 (the last with 32-bit wheels) and pyodbc: pip install pandas==2.0.3 pyodbc sqlalchemy
4. List drivers: import pyodbc; pyodbc.drivers() - the usual driver will be Microsoft Access Driver (*.mdb, *.accdb)
5. Connect using sqlalchemy:

DRIVER = "Microsoft Access Driver (*.mdb, *.accdb)"
connstring = f"DRIVER={DRIVER};DBQ={YOUR_FILENAME_HERE}"
url = URL.create(drivername="access+pyodbc", query={"odbc_connect": connstring,},)
with create_engine(url=url).connect() as con:
    inspector = inspect(subject=con)
    for table_name in inspector.get_table_names():
        print(f"Table: {table_name}")
        df = pd.read_sql(sql=text(text=f"SELECT * FROM [{table_name}]"), con=con)
        print(df.columns)