Converting Paradox DB Data to SQL Server
At Oregon College of Art and Craft, they used a SIS based on Paradox DB. Getting the data out of Paradox DB into something more current and workable was challenging. After scrounging the Internet for answers, here were my steps to convert the data with things I've learned. Hopefully it can help you.
Why SQL Server?
It's easier to get the data out of the source system into a Microsoft-based solution. Just use SQL Server for staging. Write scripts against the converted data to convert/transform the data into whatever new structure you need. For my current project, I used PHP to query out of SQL Server, transform, and save to MySQL.
Note: Must have installed Paradox Borland Database Engine (BDE) Client installed. The Microsoft Paradox DB driver uses it. Good chance that if the application is storing data in the Paradox DB that the software includes the Paradox BDE client.
1.) Create an ODBC data source using the Microsoft Paradox DB driver. May need to remove the value in the User Name field to get it to go.
2.) I use SQL Server Management Server to get the Import/Export Wizard up (Right-click on the database you want to import into and choose Import Data).
3.) Use the .NET Framework Data Provider for Odbc and enter the Dsn as the name you setup in Step 1. The destination database will be SQL Server.
4.) You will get a list of files to import based on the DB files in the ODBC data source's folder. Make sure you edit the mappings for each file.
- Dates don't import very well. Instead use a
varcharfield. Then use your conversion technology, such as PHP's
strtotimefunction, to parse the date string back into a date.
- Text fields are mapped to
(at least that's what happened to me). If you find that on importing you only get the first character of the string, change the field type to the non-unicode version,
textin this case.
May the importing gods be with you.