Since Microsoft announced the end of FoxPro support, many companies look for a way of migrating their database from legacy system to modern one. One of possible targets of such migration is MySQL database management system due to the following advantages:
- Data security
- Availability of all popular platforms
- On-demand scalability
- Transactional Support
- Tigh integration with the web
Database migration from FoxPro to MySQL is quite straightforward compared to other DBMS because it does not have complex entries handling database logic such as views, triggers, and procedures. However, FoxPro to MySQL database migration yet is not a totally simple procedure due to these challenges:
- Unmatched data types. FoxPro supports boolean data type that accepts two possible values ‘True’ and ‘False’ both of which are stored as symbols ‘T’ and ‘F’. In MySQL it is synonymous to TINYINT(1) with values ‘T’ and ‘F’ converted into 1 and 0 respectively. If the migration project requires to preserve original data “as is”, it should be converted into ENUM(‘T’,’F’) as the most appropriate type mapping.
- Distinguished character sets. According to DBF format specification, encoding settings must be stored in the file header. However, sometimes it may be incorrect or empty. The only charset validation routine is to check that the converted text data is correct in the destination database. In case of errors, the database administrator must convert files again providing correct charset information.
There are multiple solutions to migrate FoxPro database to MySQL ranged from manual approaches to completely automated solutions implemented via the special software tools.
One of the most straightforward migration methods is extracting FoxPro data in form of comma separate values (CSV) and then importing it into manually created MySQL tables having the appropriate structure. There is free tool called dbf2csv that can export DBF files into CSV format. Second part of the migration method is implemented via ‘LOAD DATA INFILE’ MySQL statement as follows:
- copy all CSV files in the data folder of the target MySQL database. This step is necessary since data can only be loaded from the local folder for security reasons.
- run this MySQL statement (pattern ‘IGNORE 1 LINES’ is only required if CSV file contains column names in the first line):
LOAD DATA INFILE ‘file_name.csv’ INTO TABLE database_name.table_name
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES;
This method may require manual post-processing to handle unmatched data types and incorrect character set and it is described above.
Another partially automated approach to database migration from FoxPro to MySQL is to use special PHP script dbf2sql.php. This method does not include intermediate export into CSV files because dbf2sql.php directly creates MySQL script to create appropriate table and fill it with the data. However, it is still missing customization of logic type mapping as well as resolving possible issues in migration from FoxPro to MySQL.
Last group of solutions includes commercial tools for complete automation of FoxPro to MySQL migration with minimal user efforts. On the other hand, those tools allow all necessary customizations of the migration, so it fits every possible database migration project. One of such professional solutions is FoxPro-to-MySQL converter developed by Intelligent Converters software company. The product supports all formats of FoxPro data and index files, allows to customize processing of logical values and to specify the encoding. If the destination MySQL database does not accept remote connections, FoxPro-to-MySQL converter can export the source data into MySQL script file that can be imported into MySQL database using any standard tools like phpMyAdmin.
Visit the official of Intelligent Converters to learn more about their FoxPro-to-MySQL converter.