FoxPro to MySQL Database Migration

The process of migrating FoxPro databases to MySQL server is rather straight forward, compared to migration between another DBMS. The reason of this relative simplicity is that FoxPro does not have such complex database objects as stored procedures, triggers and views. In other words, FoxPro databases are just used as storages while all data handling logic is enclosed in the corresponding application(s). Therefore, it is only required moving the data from FoxPro to MySQL database.

However, even migration of FoxPro data to MySQL server may be a difficult task. The main issues one may encounter include:

How different strategies of FoxPro to MySQL migration solve the issues above? The most transparent way is to export DBF files (FoxPro tables) to Comma Separate Values format and then import it to MySQL. DBF files can be converted into CSV format using free tool dbf2csv available at SourceForge.net. The second part of the procedure can be done via MySQL "LOAD DATA INFILE" statement as follows:

  1. Copy the CSV file(s) into data folder of the destination MySQL database, because MySQL will only allow to load data from a CSV file that is in the data folder (for security reasons).
  2. Run the following statement
    LOAD DATA INFILE 'student.csv' INTO TABLE mydatabase.student 
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
    LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
    

Obviously, none of two described challenges is solved by this approach, it is necessary to take certain post-processing steps in order to resolve them manually.

Script dbf2sql.php available at https://github.com/xtranophilist/dbf2sql allows to convert DBF files into SQL script that creates table and fill it with data avoiding intermediate steps like CSV file. However, it does not allow to configure mapping of FoxPro logic type and to specify user-defined encoding, and so it does not resolve possible issues of FoxPro to MySQL migration in intelligent manner.

Commercial tools like DBF-to-MySQL by Intelligent Converters allow to customize every possible parameter of the conversion process: how to process logical values, what encoding should be used and others. These capabilities help avoid intermediate steps and manual efforts during the migration. Moreover, the program can either migrate FoxPro database to MySQL server directly or export the data into local MySQL script file containing SQL statements to create tables and fill them with data. The second option should be used if the target MySQL server does not accept remote connections. That MySQL script file can be imported to MySQL server via phpMyAdmin as it is described here: http://www.convert-in.com/import-mysql-dump.htm