Migrating 18 Years of Billing Data from Microsoft Access, Without Losing a Taka

27,151 bills, 24,154 payments and 1,494 customer balances, out of an Access database from 2008. The goal wasn't to move the data — it was to prove every balance was still right afterwards.

When I started on MRF Sales, the business it was for — a printer-consumables wholesaler in Chattogram — had been running on the same Microsoft Access application since 2008. It ran on Windows XP-era PCs, nobody could support it any more, and it held everything: 27,151 bills, 24,154 payments, 1,494 customer accounts and eighteen years of stock movements.

The new app was the easy part to explain. The hard part was this: on the day the office switches over, every customer's balance in the new system has to be exactly what it was in the old one. Not roughly. A shop owner who's told he owes ৳12,400 when he knows it's ৳12,000 won't trust anything else the software says. So the migration was treated as its own project, with its own proof at the end.

Read the database directly

There was no export feature worth using, and I didn't want to depend on Access being installed anywhere. mdbtools reads .mdb files on macOS and Linux, and mdb-export turns any table into CSV. The whole import is one Python script that starts like this:

def mdb_rows(mdb, table):
    out = subprocess.run(['mdb-export', mdb, table],
                         check=True, capture_output=True).stdout
    return list(csv.DictReader(io.StringIO(
        out.decode('utf-8', errors='replace'))))

T = {t: mdb_rows(args.mdb, t) for t in [
    'Party_Name', 'Item_Information', 'Open_Balance_Party',
    'Sales_Entry_Main', 'Sales_Entry_Sub', 'Receive_Money',
    'Stock_Return_Entry', 'DamageORFaulty_Stock',
    'Purchase_Godown_Main', 'Purchase_Godown_Sub', ...]}

The output is a fresh SQLite database built from the app's own schema file, so it always matches the version of the app that will open it. The owner loads it with Settings → Restore from file.

Reverse-engineer the rules before moving a single row

Copying tables is not a migration. The old software had its own idea of how a balance is calculated, and nobody had written it down. Before importing anything, I reproduced the balance for a handful of customers by hand and compared it with the old software's own printed statements until the formula matched. What came out:

  • Due = opening balance + bills − bill discounts − every row in Receive_Money — cash, cheques, discounts, returns and "others" alike.
  • Return slips didn't credit the customer. The clerk entered a separate "Return" receipt for the money. Import both as credits and every customer with a return would be credited twice. So return slips became stock records with no money effect, and the receipts carried the credit.
  • Cheques reduced the due the moment they were entered, even if they later bounced. The new system keeps cheques pending until an admin marks them passed — which, as you'll see, was the only "difference" in the end.

The rule I'd give anyone doing this: find the old system's printed output — a statement, an invoice, a stock report — and make your numbers match that. The printout is the closest thing to a specification an old system has.

Clean up eighteen years of typing

Data entered by hand over eighteen years has every kind of inconsistency. The fixes that mattered:

  • Brands from free text. The old item table had no brand column; the brand lived in each sale line's description, spelled several ways. Each item got the brand it was most often sold under, by counting votes across every sale and purchase line.
  • Phone numbers buried in addresses. Addresses like "House 12, Agrabad, CTG. MOB: 01XXX-XXXXXX" were split into a clean address and a phone field with a couple of regular expressions.
  • Duplicate customer names got a suffix instead of being merged — merging two real customers' ledgers is far worse than a visible "(2)".
  • Internal accounts like the opening-cash and daily-expenses "customers" were left out, and opening stock for items that had been deleted was skipped and listed.
  • Money became integer paisa. int(round(float(v) * 100)), once, at the edge. No floating-point amounts anywhere in the new system.
  • Salesmen became user accounts, keeping their old credit limits, with a forced password change at first login.

Everything onto one ledger

The old database spread money across several tables, and each report re-added them its own way. In the new one, every bill, discount, payment, return and adjustment becomes an entry in a single ledger table — debit for what the customer owes, credit for what reduces it. A customer's balance is then one query, and every report reads from the same place, so the numbers can't drift apart:

SELECT party_id, SUM(debit - credit) AS due
FROM ledger
WHERE voided = 0
GROUP BY party_id;

Stock needed a different strategy

Balances could be rebuilt from history exactly. Stock couldn't — eighteen years of small counting errors don't replay cleanly. So the script starts from the old software's own stock summary at the last month-end, adds that month's purchases and returns and subtracts its sales and faulty replacements, and writes one dated adjustment per item to land on that figure. A handful of items came out negative and were set to zero and listed. The office then did a physical count on cutover day, and the app has an audited "adjust stock" for the differences.

Prove it

The last thing the script does is recompute every customer's balance the old way, straight from the Access tables, and compare it with the new ledger. The result goes into a spreadsheet for the owner — one row per customer, with old due, new due, pending cheques and a match column:

new_due = dict(db.execute(
    'SELECT party_id, SUM(debit - credit) FROM ledger '
    'WHERE voided = 0 GROUP BY party_id'))
pending = dict(db.execute(
    "SELECT party_id, SUM(amount) FROM payments "
    "WHERE cheque_status = 'pending' AND voided = 0 GROUP BY party_id"))

ok = (new_due[pid] - pending.get(pid, 0)) == old_due[pid]

Every customer with a balance matched. The only differences between "old due" and "new due" were cheques that hadn't cleared yet — money the old software had already subtracted and the new one rightly keeps pending until the bank confirms it. That spreadsheet did more for the owner's confidence than any demo of the new screens.

What carries over to any legacy migration

  • Treat the migration as its own project with its own deliverable: a reconciliation the business can check, not "the data is in".
  • Reverse-engineer how the old system calculated things, and verify it against its printouts, before writing a single insert.
  • Make the import a repeatable script, not a one-off. You will run it many times as you find edge cases, and once more on cutover day with the final backup.
  • Rebuild what can be rebuilt exactly (money), and reset what can't (stock) to a known figure with a dated, explained adjustment.
  • Never merge records you're unsure about. A visible duplicate is fixable; a silently merged ledger isn't.