I know that vendors like using FoxPro because of the lack of security built in but gezzz. I have been unsuccessful with setting up Openrowset, bcp, bulk import. Is FoxPro import become out of the solution set due to its age? Of course I can convert dbf to Excel then import but this adds too much to some of our 'end users'??? Thanks and sorry. The most straight forward way to do FoxPro to PostgreSQL migration is to export DBF files (FoxPro tables) into Comma Separate Values format and then import it to PostgreSQL. DBF files can be converted into CSV format using free tool dbf2csv available at SourceForge.net. The second part of the migration process can be done via free pgloader tool. Import Foxpro Dbf Into Excel. DBF Converter & DBF Viewer for DBF File supports dBase, Clipper, FoxPro, Visual FoxPro and other DBF formats. Import from Excel 2007 to dbf files. Convert approach dbf to csv. Converter: XLS (Excel) to DBF Converter converts all your XLS files into DBF and makes them compatible with database management system. Even though, dealing with database files always sounds complicated, this program ensures you an easy and a quick handling.

A while back, someone handed over some scheduling data in a somewhat clunky old application in desperate need of modernization. I looked under the hood and figured out that this thing uses a FoxPro database! Microsoft discontinued development of Visual FoxPro way back in 2007, and I hadn’t seen a working FoxPro database since the late 1990s…so I knew it might be tricky to get the data out.

TL;DR: I fired up Microsoft Access to see if I could link to the external data. This was harder than I expected because Windows 10 and Access 2016 no longer ship with a Visual FoxPro ODBC driver. Once I found one, I could easily link to all of the tables—except for the ones that included variable-width memo fields. I could use an OLE DB provider to get to tables with memo fields, but only via Excel or programmatically. Access apparently can’t create a linked table via OLE DB.

As I mentioned above, if you’re on Windows 10 and Microsoft Access 2016, chances are good that your machine doesn’t have an ODBC driver for Visual FoxPro installed like earlier versions. You can find a page on Microsoft’s website that used to have a link to download of the driver, but it’s been removed since they no longer support it. Thankfully, it seems you can reliably find a link to the old Microsoft VFPODBC.msi file at the Visual FoxPro Wiki.

I know Microsoft Access isn’t an ideal tool for creating applications, but I sure saw and wrote plenty of them back in the day. It’s still a great tool to quickly test stuff like this.

So I clicked over to the External Data ribbon, clicked New Data Source→From Other Sources→ODBC Database and configured a connection to the FoxPro data I wanted to get into. I easily pulled up a table listing and decided to select all the tables to start off. Several prompts came up to indicate a unique identifier. Being that my attention span for message and dialog boxes has shrunk to zero, I answered the first two or three, and then just clicked Cancel through the rest. Everything seemed all too easy!

Problems with Tables Having Memo Fields :-/

How To Import A Dbf File Into Excel

Now that I knew this was indeed a FoxPro database and that I could access its contents, I set my focus on analyzing the table structure to find the specific data I needed. At this point I hit a snag…

After much googling and digging, I concluded that the cause of these error messages was that the Microsoft Visual FoxPro ODBC driver was choking on the memo fields.

. Every other table I linked to via ODBC worked perfectly, but any table that included one or more memo fields bombed.

If you look at the file structure of a FoxPro database, you see that each data table has a corresponding .DBF and .CDX file. Using a text editor, I could infer that the .DBF file contains headings and data for the table’s fixed-width fields, and that the .CDX file contains indexes. Any table that included a variable-width memo field had a corresponding .FPT file containing that data.

So if you want a shortcut to determine whether ODBC will get you into all of your FoxPro tables, look for .FPT files. If you see them, you will have problems. If you don’t have problems, I hope you’ll tell me about it! The best solution I’ve found is only a workaround. I’d love to know if you’ve found a method to link directly.

In my quest to crack this open, I found others recommend OLE DB as a way to overcome the memo field problem. While an OLE DB driver for Visual FoxPro no longer ships with Windows 10 or Access 2016 of course, I easily tracked one down on Microsoft’s website here.

After I installed it, my next problem was how to use it in Microsoft Access to create a linked table. After again scouring the Internet, I found no way to do this. Access apparently only allows you to create linked tables to Access, SQL Server, and ODBC databases. I could however import the data into Excel, but that too was tricky.

Importing Visual FoxPro Data Into Excel via OLE DB

Getting to external data via OLE DB is pretty intuitive in Excel…up until you go to create the connection string. (Click Data→Get Data→From Other Sources→From OLE DB.) After I clicked Build, selected the Visual FoxPro driver, and clicked Next, Excel crashed…every single time…

Thankfully there’s more than one way to build an OLE DB connection string. For the most part, it’s pretty straightforward (so long as you don’t need any special options):

How To Import Dbf File Into Excel

Once I had my connection string, I pasted it into Excel and it connected to every table in the FoxPro database. I could import all of the data I needed with no problems.

From here I could link to the data in Excel to use it in Access. I could also use some VBA code to directly import the data into Access. These are not simple ways to get to my data, but at least I know I can get it out when the time comes.