Migration begins before the first INSERT
A folder of DBF files is often an application database whose relationships live in index expressions, program code, screen logic and staff knowledge. List every DBF, its memo and index companions, record count, date range, deleted-record count, encoding and likely key fields. Preserve the original files and capture source reports that can later validate totals.
Decide whether SQLite or MySQL fits the destination. SQLite is a strong choice for a portable single-file archive or local application with limited concurrent writing. MySQL is appropriate for a server application with multiple users, permissions, backups and network access. The extraction and reconciliation controls are similar even though operations differ.
Map meaning, not only DBF type letters
| Source concept | Possible target | Decision required |
|---|---|---|
| Character | TEXT / VARCHAR | Is it free text, a fixed code or a foreign key? |
| Numeric / Float | INTEGER / DECIMAL / REAL | Required precision, scale and rounding behavior |
| Date / DateTime | DATE / DATETIME or ISO text | Blank and invalid legacy values; timezone assumptions |
| Logical | BOOLEAN / integer constraint | How to retain blank or unknown states |
| Memo | TEXT / BLOB | Encoding, binary content and missing-pointer policy |
| Character identifier | TEXT | Preserve leading zeros and case |
Do not create foreign keys until you have measured orphan values and duplicate candidate keys. Legacy data may violate rules that the original user interface enforced only most of the time. Record exceptions rather than silently deleting them.
Use a repeatable staging process
- Open and validate working copies, including memo pairing and code pages.
- Export normalized UTF-8 staging files with stable record identifiers.
- Create destination tables from a version-controlled schema script.
- Load into staging tables first, retaining source filename and source-row references.
- Transform invalid dates, logical states and whitespace through explicit rules.
- Populate final tables and build indexes after bulk loading when appropriate.
Keep a reject table for records that cannot be converted. An import that reports success after silently skipping bad rows is not a successful migration.
Reconcile before cutover
- Row counts by table, including the treatment of deleted DBF records
- Distinct key counts, duplicate keys and orphan references
- Sums of important amounts and quantities
- Minimum and maximum dates plus invalid-date counts
- Null, blank and memo-presence counts
- Samples covering old, recent, accented, negative and unusually long values
Run the old and new systems in a controlled comparison period when possible. Define a cutover point, freeze source writes, run the final repeatable import and retain a rollback path. Store the original DBF archive independently from the new database backup.
Use the DBF dialect comparison during inventory and the private CSV workflow when creating staging files.