DBF to MySQL Database Migration

DBF to MySQL Database Migration

The process of migrating FoxPro databases to MySQL server is quite straight forward and easy as compared to migration between any other DBMS. The reason of this relative simplicity is that FoxPro does not have such complex database objects like

  • Stored procedures
  • Triggers
  • Views

In simple words, FoxPro databases are used as storages while all other data handling logic is enclosed in the corresponding applications. Therefore, it is only required to move the data from FoxPro to MySQL database.

However, migration of FoxPro data to MySQL server may be a difficult task due to following issues:

  • Unmatched data types.
  • FoxPro has type Logical with two possible values: True (stored as symbol ‘T’) or False (stored as symbol ‘F’).
  • MySQL has corresponding type BOOLEAN or BOOL synonyms for TINYINT(1) that also accept one two possible values: 1 for True and 0 for False.

According to semantic equivalent,

  • ‘T’ is mapped to 1
  • ‘F’ is mapped to 0

However, particular cases may require to preserve the original data as it is. The most appropriate type mapping for such situations is ENUM(‘T’,’F’).

  • Different character sets.

FoxPro format stores encoding information in the header of DBF files but 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 codepage.

Image result for Database Migration

Problem Resolutions

The most clear and easy way is to export DBF files being used as storage of FoxPro tables into Comma Separate Values format and then import it to MySQL. Dbase/FoxPro files can be converted into comma CSV format via dbf2csv, free tool 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 you load data from a CSV file that is in the data folder (for security reasons).
  2. Run the following statement
  1. LOAD DATA INFILE ‘student.csv’ INTO TABLE mydatabase.student
  2. FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
  3. LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES;

However, the challenges mentioned above are not solved by this approach so it is necessary to take certain post-processing steps in order to resolve them manually.

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

There are some commercial database migration tools like DBF to MySQL converter by Intelligent Converters that allow customizing every possible parameter of the conversion process: how to process logical values and what encoding to be used. These capabilities help avoid intermediate steps and manual efforts during the migration.

The program can either migrate database from DBF to MySQL server directly or convert it into local MySQL script file containing SQL statements to create tables and fill them with data. That MySQL script file can be imported to MySQL using standard tools like phpMyAdmin or command line client.

Categories: Featured

About Author