How can I convert an MS Access database (.mdb) file to an Sqlite?

Question

I have an MS Access .mdb file, which I would like to convert into an SQLite database.

I need to convert all the tables and dump the data inside.

Are there any FOSS command-line tools that can do this on Linux?

Tumefacient

Answer 1

It's remarkably hard to find such a thing, but I've built a handy Python utility to do it using SQLAlchemy and pandas_access, which relies on mdbtools.

Everything you need can be acquired with:

pip3 install sqlalchemy pandas_access
sudo apt install mdbtools

The code is as follows:

#!/usr/bin/env python3

import pandas_access as mdb
from sqlalchemy import create_engine
import sys
import os

if len(sys.argv)!=3:
  print("{0} <MDB File> <Sqlite3 File>".format(sys.argv[0]))
  sys.exit(-1)

if os.path.isfile(sys.argv[2]):
  print("Refusing to modify existing database!")
  sys.exit(-1)

engine = create_engine('sqlite:///{0}'.format(sys.argv[2]), echo=False)
tlist  = [tbl for tbl in mdb.list_tables(sys.argv[1])]
tables = {tbl:mdb.read_table(sys.argv[1], tbl) for tbl in tlist}

for k in tables:
  tables[k].to_sql(k, con=engine)

Tumefacient