FoxPro to MySQL Database Migration
Database migration from FoxPro or DBase to MySQL, MariaDB or Percona is quite easy, when compared to migration between another DBMS. The cause of this relative simplicity is the fact that FoxPro doesn’t have such advanced database objects as stored procedures, triggers and views. This means that, FoxPro databases are only utilized as storages while all data handling logic is confined in the matching application(s). As a result, it is only needed in transferring the data from FoxPro to MySQL database.
Even so, even migration of FoxPro data to MySQL server can be a complicated job. The primary issues you can encounter comprise of:
- Unmatched data types. FoxPro DBF format supports logical type that accepts two values: True (stored as symbol ‘T’) or False (stored as symbol ‘F’). In MySQL, MariaDB or Percona there is equivalent type BOOLEAN or BOOL that is synonyms for TINYINT(1), it also accept two possible values: 1 for True and 0 for False. Based on semantic equivalent, ‘T’ should be mapped into 1 and ‘F’ must be mapped into 0. But, particular cases may need to preserve the initial data “as is”. The most suitable type mapping for such situations is ENUM(‘T’,’F’).
- Different character sets. Although DBF files store details about encoding in the header, occasionally it could be empty or incorrect. So, to get correct conversion it’s important to analyze results after converting data with codepage described in the DBF file. If certain parts of text are incorrect, the database migration expert need to run conversion again using another codepage.
How different recommendations for FoxPro to MySQL migration address the problems above? The most transparent approach is to export DBF files (FoxPro tables) to Comma Separate Values format and thereafter import it to MySQL. There is free tool dbf2csv available at SourceForge.net to export data from FoxPro DBF files into CSV format. The next part of the process could be done via MySQL “LOAD DATA INFILE” statement as follows:
- Copy the CSV file(s) into data folder of the destination MySQL database, because MySQL will only allows you load data from a CSV file that is in the data folder (for security reasons).
- 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;
Apparently, none of two described difficulties is fixed by this approach, it is essential to consider certain post-processing process in to solve them manually.
Script dbf2sql.php enables you to convert DBF files into SQL script that builds table and fill it with data preventing intermediate steps like CSV file. But, it doesn’t give you the opportunity to set up mapping of FoxPro logic type and also to specify user-defined encoding, and so it doesn’t sort out potential problems with FoxPro to MySQL migration in intelligent manner.
Commercially produced tools like FoxPro to MySQL available through Intelligent Converters gives you the opportunity to personalize every possible parameter of the conversion process: how one can process logical values, what encoding ought to be used among others. These features aid in avoiding intermediate steps and manual efforts throughout the migration process. Furthermore, the software can migrate FoxPro database to MySQL server immediately or export the data into local MySQL script file containing SQL statements to generate tables and fill all of them with data. The next option needs to be used if the target MySQL server doesn’t accept remote connections.