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:

• Unmatched data types. FoxPro has type Logical having two possible values: True (stored as symbol 'T') or False (stored as symbol 'F'). MySQL has corresponding type BOOLEAN or BOOL that is synonyms for TINYINT(1) that also accept two possible values: 1 for True and 0 for False. According to semantic equivalent, 'T' must be mapped into 1 and 'F' must be mapped into 0. However, particular cases may require to preserve the original data "as is". The most appropriate type mapping for such situations is ENUM('T','F').
• Different character sets. Although DBF files store information about encoding in the header, sometimes it may be empty or incorrect. So, in order to get correct conversion it is necessary to review results after converting data with code page mentioned in the DBF file. If some parts of text are incorrect, the database migration specialist should run conversion again using another code page.

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-separated 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: https://www.convert-in.com/import-mysql-dump.htm