How to Extract Data from a Legacy Visual FoxPro Application

Visual FoxPro is a low-code database tool for business that’s been around since the early ’90s. Many, many businesses run on a FoxPro database. Since Microsoft discontinued support for the platform in 2017, those businesses find themselves wanting to move to something with more of a future.

If you’ve been challenged to extract data from a Visual FoxPro database, use this recipe to get a development environment up and running fast. It’ll take a bit of work, but if you’re lucky, you can be up and running in an afternoon. As with all recipes, you may need to swap out a few ingredients or steps here and there.

Ingredients

• Windows VM. Any version will do, choose a 32-bit build if you have the option
• VS Code
• The .NET cli
• A VFP OLEDB driver
• PowerShell
• Some Visual FoxPro files

Setup

Using the .NET CLI, make a new project in whichever language you prefer. I’ll use C# here.

dotnet new cli —language 'C#'

Add a few packages from NuGet

dotnet add System.Data.OleDb
dotnet add VfpClient
dotnet add ClosedXML

OleDb is Microsoft’s generic interface for working with data sources. We’ll use it, combined with a Visual FoxPro OleDB Provider to read from FoxPro as though it were any other modern database.

VfpClient is a really handy package that provides a nicer-to-use interface on top of OleDB that we’ll use when actually working with FoxPro data.

ClosedXML is a wrapper library that makes it trivially easy to export things to an Excel workbook. This is handy when debugging your FoxPro app.

Install the VFP OleDB driver from Microsoft. This one is for FoxPro version 8.

Coding

Open up your new project in VS Code. In Program.cs, we’ll open a new connection using the VfpConnection class. Then, we’ll write out every table name to the Console.

// Program.cs 
using System;
using System.Data;
using VfpClient;

namespace myapp
{
    class Program
   {
       static void Main(string[] args)
       {
           var connection = new VfpConnection(“./path/to/your/foxpro-file.dbc”);
           connection.Open();
           var schema = connection.GetSchema(“Tables”);
           foreach (var row in schema.Rows)
           {
               foreach (var column in row.Table.Columns)
               {
                   if (row[column.ColumnName.ToString()].ToString() != "" && column.ColumnName.ToString() == "TableName")
                   {
                       Console.WriteLine(row[column.ColumnName.ToString()].ToString());
                   }
               }
           }
       }
   }
}

Reading Data

So having the table names is great, but you probably want to get some actual data out of this thing. There are a few different ways of getting data out, but I’ll show you how to do it with a command object.

Let’s continue in Program.cs.

// Program.cs, continued
// …
connection.open();
var command = connection.CreateCommand();
command.CommandTest = $“SELECT * FROM {tableName}”;
var reader = command.ExecuteReader();
reader.Read();
var ordinal = 0;

// Log out each row
while (reader.Read())
{
    while (ordinal < reader.FieldCount)
    {
        Console.WriteLine($“{reader.GetName(ordinal)}: {reader.GetValue(ordinal)}“);
        ++ordinal;
    }
}

If all went well, you should see a whole bunch of output with the data from your FoxPro database. You’ll likely need to play around with the query string to get all the data that you care about.

Now that you have the basic skeleton of a FoxPro extraction project ready, extend it to your heart’s content!