How to effectively import large MS Access DB to MySQL DB

Who is this article for?

Anyone looking for how to import large MS Access DB to MySQL DB effectively.

Before You Begin

There are several options which we can use to import the data to MySQL. Few of those are listed in these articles below -

https://stackoverflow.com/questions/5722544/how-can-i-convert-an-mdb-access-file-to-mysql-or-plain-sql-file

https://stackoverflow.com/questions/4809654/how-to-import-an-access-mdb-format-database-to-mysql

https://stackoverflow.com/questions/5465661/converting-mysql-to-ms-access

These options work well if the data that needs to be imported is small but is very slow for an extensive database. The reason the import is very slow is that it creates one insert statement per record. I recently had to convert a relatively large MS Access DB file to MySQL. The import process was prolonged, and I let it run overnight, but it didn't complete. So I had to come up with a different solution which can import the data much faster.

For this post, we can use the data provided by IPEDS. The file can be downloaded from here - https://nces.ed.gov/ipeds/use-the-data/download-access-database. We will use the 2017-18 Access file, which is the latest release at the time of writing this article.

Also, we are going to use mdbtools. I believe you can install it on Windows Operating system too - https://github.com/brianb/mdbtools/issues/107. Since I run a Linux distribution installing it was an easy setup.

Script

Conclusion

When I used this script, I was able to import the data in less than a few minutes. A general outline of how the script works.

  1. Creates login path to suppress warnings.
  2. Drops tables if they exist from the specified database.
  3. Recreate the table metadata from MS Access.
  4. Export each table to .csv file.
  5. Bulk import the .csv data to the specified table.

Limitations

  • The above script is tested on a Linux distribution.
  • mdbtools does not create indexes or foreign key references.

Sources

Posted in MYSQL, ACCESS, DATABASE, BLOG
Akki K

Akki

Experienced Backend Developer.