MS Access to MySQL Migration Without Losing Data

"Just export it to CSV and import it." That is what I thought too.

The mill I work in kept years of production records in one Microsoft Access file. When I built the new web-based ERP, the new system could not start empty. Nobody trusts a system that has forgotten everything. So the old data had to come across, complete.

Here is what the job really looked like.

The numbers

• One 8 MB Access file
• 60 tables
• 6.8 MB of SQL once exported
• One single table was 4 MB of that

It does not sound like much. It still took far longer than building several screens.

What broke

1. Table names with spaces

Access lets you name a table with spaces and wraps the name in square brackets. MySQL wants backticks. Every query had to be quoted correctly for both sides, or it failed on the first line.

2. Foreign keys fought back

You cannot insert a production record before the customer it belongs to exists. I wrote the insert order by hand for 39 tables, parents first and children after. Get that order wrong and you see a wall of errors with no clue which table started it.

3. Dates that were not dates

Years of manual entry leave blank cells, zero dates and half-typed values. They arrive as invalid dates and kill the insert. Now anything that is not a real date becomes an empty value, and the row is kept.

4. One bad row should not kill 200 good ones

I insert in batches of 200 for speed. If a batch fails, the script tries that batch again row by row, logs the exact broken row and continues. That turned "migration failed, start again" into "migration finished, here are the three rows to look at".

5. The mistake that cost me the most

My first version wiped everything and imported it again, including the users table. Every time I fixed a bug and ran it again, it deleted the login accounts I had just created. I locked myself out of my own system more than once.

The fix was three lines: never touch the user tables.

The rule that changed everything

A migration you can only run once is not a migration. It is a gamble.

I made the script safe to run again and again. Each run lands in the same clean state. After that I stopped being afraid to test, and the work became much faster.

When two systems run side by side

A factory does not switch in one night. For a while the old Access file and the new system were both in use, sometimes for the same day's data. A simple "delete and re-import" would have destroyed entries staff had typed into the new system.

So I merge instead of replace:

• Match rows by what they mean (the day and the item), never by ID numbers. Each system numbers its rows on its own, so equal IDs are only a coincidence.
• Add what is missing. Fill blanks. Keep a real value over a zero.
• Stop and ask a person only when two real values disagree.

Advice if you are planning the same move

1. Take a backup before every run. Then take another one.
2. Expect dirty data. It carries years of typos and workarounds from people who only needed to get the job done. Do not blame the data. Build something that survives it.
3. Make it re-runnable before you make it fast.
4. Count the rows in both systems after every run, table by table.
5. Check the totals on a report people know well. If a familiar report shows a familiar number, staff will trust the new system.

The result for the mill: all historical records now live in MySQL, and every department enters data at the same time instead of waiting for one file. More detail is in the Access to MySQL migration project.

If your records are stuck in Access or Excel and you want them moved safely, tell me about your setup.