Relicbridge

Moving Visual FoxPro data to SQL Server, row for row

Visual FoxPro shipped its last release in 2007 and left support in 2015. The applications did not leave. We still get calls about VFP 6 and VFP 9 systems running order entry, inventory or job costing for a company doing real revenue, usually because the developer who wrote it is retiring or because a new integration cannot be built against DBF files on a share.

The rebuild of the front end is the long part. The data tier is the part you can do first, and it is the part that decides whether the rest goes well. This tutorial covers getting FoxPro tables into SQL Server accurately, while the FoxPro application keeps running, and proving the result row for row before anybody depends on it.

The same sequencing argument applies here as with Access: data first, front end later. We wrote that up separately in splitting an Access application.

Step 1: inventory the files before you read them

A FoxPro data directory is not just tables. Walk it and write down what you have:

  • .dbf — table. Every one of these is in scope until proven otherwise.
  • .fpt — memo file. Belongs to the .dbf of the same name. If it is missing or mismatched, memo fields read as garbage.
  • .cdx — structural index. Rebuildable; do not migrate it, but read it, because the index tags tell you which columns the application searches on and which ones you will want indexed in SQL Server.
  • .dbc — database container, if the tables are part of a database rather than free tables. It holds long field names, default values, referential rules and stored procedures written in FoxPro code. That code is business logic. Extract it and read it before you decide the data tier is "just data".

Count rows per table and note the last-modified dates. Tables nobody has written to since 2011 are usually archives, and they will drive migration effort that nobody needs. Ask before you carry them over.

Step 2: land the data as-is in a staging schema

Do not clean anything on the way in. The first load should be a boring, repeatable copy into a stg schema where every column is wide and forgiving, so a bad row fails your checks rather than the loader.

The most reliable reader we have used is the Visual FoxPro OLE DB provider driving a small .NET 8 console app. It is 32-bit only, so build the loader as x86:

<PropertyGroup>
  <TargetFramework>net8.0</TargetFramework>
  <PlatformTarget>x86</PlatformTarget>
</PropertyGroup>

using System.Data.OleDb;

var source = new OleDbConnection(
    @"Provider=VFPOLEDB.1;Data Source=D:\legacy\data\jobs.dbc;Collating Sequence=machine;");
await source.OpenAsync();

using var read = new OleDbCommand("SELECT * FROM orders", source);
using var reader = await read.ExecuteReaderAsync();

using var bulk = new SqlBulkCopy(sqlConnectionString)
{
    DestinationTableName = "stg.orders",
    BatchSize = 5000,
    BulkCopyTimeout = 0,
};
await bulk.WriteToServerAsync(reader);

Two things to get right on the first pass:

  • Run the load against a file copy, not the live share. Copy the whole data directory, .fpt and .cdx included, while the application is idle. FoxPro tables read from a share under load will give you torn results and no error.
  • Make the loader rerunnable. Truncate the staging table at the start of each run. You will run this load a dozen times before cutover, and the last run happens during the cutover window itself.

If the provider is not an option on your machine, an alternative is a small FoxPro script using COPY TO ... TYPE CSV per table, but you then own the quoting and encoding problems yourself. Prefer the provider.

Step 3: the type traps

This is where FoxPro migrations lose data quietly. Each of these has bitten a real project.

Deleted records are still there

FoxPro marks deleted rows with a flag and leaves them in the file until someone runs PACK. Depending on the connection setting, your SELECT * may return them. Decide explicitly:

SET DELETED ON

or read the flag and carry it into staging as a column. Then reconcile against what the application shows the user. A table that returns 240,000 rows to your loader and 180,000 rows to the user's screen is not a bug in your loader; it is 60,000 records the business deleted years ago and never packed. Do not migrate them into the live table, but keep them in staging until go-live is behind you.

Empty dates are not null

FoxPro's empty date is a blank date, not a null. Some providers surface it as 1899-12-30, some as 0001-01-01, some as null. Pick one target representation, usually NULL, and normalize once in a documented step. Then check the count: if 12,000 orders have a blank ship date, that is a real business fact about unshipped orders and the new system needs an answer for it.

Currency, numeric and float are three different things

  • FoxPro Y (Currency) is a scaled 64-bit integer, four decimal places. Map to decimal(19,4). It maps cleanly.
  • FoxPro N (Numeric) is decimal with declared width and precision. Map to decimal(width, decimals) using the declared values, not what you think the data holds.
  • FoxPro B (Double) and F (Float) are binary floating point. If money is stored in one of these, you have rounding differences waiting for you. Migrate to decimal, then reconcile totals to the cent and expect small differences you will have to explain to the controller rather than hide.

Character fields are padded and are not Unicode

C(30) fields are space-padded to 30 characters. char(30) in SQL Server keeps the padding; varchar/nvarchar with a trim on load does not. Trim on load, and record that you did.

Encoding matters more. VFP tables carry a code page byte in the header, commonly 1252 (Windows ANSI) or 437 (DOS). Read the header rather than assuming, load into nvarchar, and then scan for the characters that go wrong first: accented names, the degree sign, and the em dash pasted in from Word.

SELECT id, name FROM stg.customers
WHERE name COLLATE Latin1_General_BIN2 LIKE '%?%'
   OR name LIKE '%' + NCHAR(0xFFFD) + '%';

Memo fields

M fields live in the .fpt. They are usually notes, sometimes a whole document, occasionally binary somebody stuffed there. Load them into nvarchar(max), check the maximum length you actually received against the maximum length in the source, and look at the longest ten by hand.

AutoInc columns and the identity gap

VFP 8 added AutoInc columns. If you map one to a SQL Server IDENTITY, the values will renumber on load unless you set IDENTITY_INSERT ON. Keep the original numbers. They are printed on paperwork somewhere in that building.

Step 4: reconcile before you trust it

Write the reconciliation as a script, not as a look at the screen. Run it after every load. Three levels, cheapest first:

-- 1. Row counts per table, staging against a recorded FoxPro count
SELECT 'orders' AS table_name, COUNT(*) AS sql_rows FROM stg.orders;

-- 2. Aggregates on every money and quantity column
SELECT COUNT(*)        AS rows,
       SUM(total_amt)   AS total_amt,
       SUM(qty_ordered) AS qty_ordered,
       MIN(order_date)  AS first_order,
       MAX(order_date)  AS last_order
FROM stg.orders;

-- 3. A checksum over the business key and the columns that matter
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(order_no, cust_no, total_amt, order_date))
FROM stg.orders;

Produce the same three numbers from FoxPro on the source copy and diff them. Then go one level further and reconcile against the reports the business already trusts: run the FoxPro month-end sales report for three closed months, run the equivalent query against SQL Server, and make the totals match to the cent. When they do not, the difference is nearly always a float-stored amount, a deleted-but-not-packed row, or a report filter nobody documented. All three are worth finding now.

Keep the reconciliation script in the repository beside the loader. It becomes part of the characterization harness the rest of the migration hangs from, the same idea as the HTTP harness in golden-master tests for legacy web apps.

Step 5: keep FoxPro running on the new tier

You do not need to rewrite the front end to start getting value. Once the SQL Server copy is accurate, you have two ways forward while the VFP application keeps working:

  • Remote views or SQL pass-through in VFP. The application talks to SQL Server through SQLCONNECT and SQLEXEC, table by table. This is real work in the FoxPro code and is worth it when the FoxPro app will live for another year or more.
  • One-way sync into SQL Server. FoxPro stays the system of record; a scheduled load keeps SQL Server current for reporting, integrations and the first .NET 8 slices that only read. Much less invasive, and it is usually the right first move.

Either way, SQL Server becomes the place new code is written against, and the .NET 8 rebuild starts on a data tier that is already proven.

What we would not do

  • Run a converter over the FoxPro code and call it a migration. Tools that translate VFP forms and PRG files into C# produce code shaped like FoxPro written in a language that is not FoxPro. Nobody maintains it afterward. The data is what has value; the screens are worth rebuilding on purpose.
  • Clean the data during the first load. Land it as-is, reconcile, then clean in a separate documented step. Otherwise you can never tell whether a difference came from the source or from you.
  • Migrate before reading the DBC stored procedures and field rules. Validation rules living in the database container are business logic, and they will be missing from the new system in a way that shows up months later.

If you are looking at a FoxPro system now, the honest first step is a read-only copy of the data directory, one loader run and one reconciliation script. That is a few days of work and it turns every later estimate from a guess into arithmetic. We do that as part of a legacy codebase assessment, or you can do it yourself with what is above.