Data

FoxPro to MySQL Database Migration

Migrating FoxPro databases to MySQL is relatively simple compared to another DBMS due to the fact that FoxPro does not support such complex objects as stored procedures, triggers and views. FoxPro database is just a structured data storage while all logic is implemented in external application(s). Therefore, FoxPro to MySQL migration includes only moving the data from the source database to the destination.

Even so, database migration from FoxPro to MySQL server may require a lot of efforts when doing it manually. The main challengesare:

Unmatched data types – FoxPro logical type does not have direct equivalent in MySQL since it accepts true/false values as string representation, while in MySQL Boolean is synonym of bit. User may require to have two options of converting FoxPrological values: either map it into MySQL enum(‘T’,’F’) or translate into integer values according to ANSI standard (true = 1, false = 0)
Character set collision–according to DBF format,encoding information is stored in the file header, however it may be empty or incorrect sometime. To resolve such issues, it is necessary to review result of conversion with codepage mentioned in the DBF file. If person responsible for migration finds some incorrect fragments of text data, conversion must be run again using another codepage

There are multiple approaches to database migration from FoxPro to MySQL. Below each of them is described in aspect of solving challenges specified above. The most straight forward way of conversion is to export FoxPro tables (*.dbf files) into intermediate storage of comma separate values format and then import it into MySQL database. There is free tool dbf2csv available at SourceForge.net for this purpose. The nextpart of the migration procedure can be run via MySQL statement:

LOAD DATA INFILE’filename.csv’ INTO TABLE tablename;

It is not hard to guess that neither of two challenges specified above is solved by this approach. The person responsible for database migration has todo the appropriate post-processing steps in order to resolve those issues manually.

Next solution is PHP script dbf2sql.phpthat converts FoxPro databases into MySQL script files containing SQL statements to create tables and fill them with data. Although this approach does not require intermediate steps like import into CSV files, it cannot customize mapping of FoxPro logic type and encoding.

The last but not least solution to migrate Dbase / FoxPro databases to MySQL is to use special commercial software. This approach does not require any manual efforts after automated database conversion is completed.One of such tools is DBF-to-MySQLmade by Intelligent Converters. It provides high performance due to direct connection to source and destination databasesand allows to customize every possible parameter of the conversion process.