Btrieve to SQL: how to get your data out

Moving data out of Btrieve is usually less about the engine and more about understanding what's inside each file. Btrieve stores records as raw bytes. The column names, data types and meaning live somewhere else: in data dictionary files, in the application's source code, or only in the heads of the people who built it. Here's how we approach it.

Before you start: work on a copy

Never experiment on the live files. Stop the application (or wait until nobody is using it), copy the entire data folder, including any .DDF files, to a separate machine, and do all of your work there. A bad tool setting or a "repair" option run on the original files can turn a data project into a data recovery project.

Step 1: Find out what you have

Start by making a list of every data file, its size, and which Pervasive or Actian Zen version is installed. Then check whether the folder contains data dictionary files. They're usually named FILE.DDF, FIELD.DDF and INDEX.DDF, and they describe the tables and columns in a form the engine's SQL side can understand.

Next, run the engine's BUTIL utility against each data file to see its structure:

butil -stat CUSTOMER.DAT

The output shows the record length, number of records, page size, file format version, and every key (index) with its position, length and type. That's your first map of the file, even if no other documentation exists.

Step 2: Map the record layouts

If you have DDF files

DDFs define tables and columns, which lets you query the data with SQL through the Pervasive or Actian ODBC driver. Don't trust them blindly, though. DDFs are often incomplete or out of date, especially when an application was changed over the years and nobody updated the dictionary. Check a sample of records against what the application shows on screen.

If you don't have DDF files

You'll need to rebuild the layout of each record. The best sources, in order:

• Source code. VB6 Type definitions, C structures, COBOL copybooks or Clarion dictionaries usually describe each record field by field.
• Key definitions from butil -stat. Keys tell you where certain fields start, how long they are, and what type they are.
• The raw records themselves. Viewing records in a hex viewer, side by side with the same records on the application's screens, reveals where each field starts and ends.

These are the field types you'll run into most often:

Step 3: Extract the data

There are three main ways to get records out, depending on what you have:

With DDFs: use ODBC

Create an ODBC data source that points at the database, then pull tables into SQL Server with the SQL Server Import and Export Wizard, SSIS, or a linked server. For smaller tables, Excel or Access can read the ODBC source directly. This is the fastest route when the DDFs are accurate.

Without DDFs: dump the records

butil -save writes every record to a sequential file that a script can parse, using the layout you built in step 2. For damaged files, butil -recover extracts every record the engine can still read.

For complex files: a custom extraction program

A short program that calls the Btrieve API directly, stepping through each record and writing CSV, gives you the most control. It's the best option when one file holds several record types, or when some fields need to be decoded conditionally.

Step 4: Clean it up on the way

Most migration problems show up here, not during extraction. The usual suspects:

• Character encoding. Older data is often stored in a DOS code page, not the Windows or UTF-8 encoding your new system expects. Accented letters and special symbols come out garbled if you don't convert them.
• Dates. Watch for dates stored as text, as integers, or as zeros meaning "no date."
• Implied decimals. A value of 12345 may really mean 123.45.
• Multiple record types in one file. Older applications often stored headers and detail lines in the same file, told apart by a type field.
• Duplicates. The old system may have allowed duplicate keys that your new primary key won't accept.

Step 5: Load and verify

Create tables in SQL Server, MySQL or PostgreSQL with proper types, then load the cleaned data. Before anyone relies on it, verify it three ways:

1. Compare record counts for every file against the counts from butil -stat.
2. Check totals, such as invoice amounts or inventory quantities, against reports from the old system.
3. Spot-check a couple of dozen random records against the old application's screens.

Keep the old system running meanwhile

Reading data doesn't require downtime. A common approach is a scheduled job that copies the Btrieve data to SQL every night. Reporting and integrations can move to SQL right away, while the old application keeps running until the new system is ready. At cutover, you freeze the old data, run one final sync and verification, and switch.