Importing data from DBF files in Excel

In addition to exporting worksheet data to DBF files (see my previous post) the add-in that I'm developing can also import data from DBF files to Excel worksheets.

After selecting a source file, user can adjust import settings.

And preview the data.

Export/Import functions can also be accessed in VBA.

Exporting Excel worksheets to DBF files

I am working on development of Excel Add-in that exports data to DBF files. No data provider (ODBC, OLE DB, ADO.NET etc.) is required. Planned release date: August 2014.

• Supported Excel versions: 2007 to 2013.
• Supported DBF formats: dBase III Plus (0x03), FoxPro (0x30) (free tables, Memo support is yet to be decided upon). DBase IV support is yet to be decided upon.
• Data types supported: Character, Logical, Date, DateTime, Numeric, Integer, Double, no Memo support yet.
• Exports either whole worksheet or a rectangular contiguous range.
• Automatically skips hidden columns and rows filtered out.
• Determines the most suitable data type for each exported column. Data types can be manually overridden.
• Allows to modify output table structure: names, data types, width, decimals; selected columns can be skipped
• Allows to set code page mark.
• The setup may request installing run-times for .NET 4.0 and VS2010 Tools for Office.

Useful tools: Remote Desktop Connection Manager

Remote Desktop Connection Manager -- free tool from Microsoft, quite useful if you need to connect to more than one computers remotely. Organizes sessions in compact window, allows storing credentials (though not always a good thing but often a time saver).

Visual FoxPro Memo file format uses big-endian convention

Contrary to the little-endian convention used across DBF file format, and generally across the Windows, VFP Memo file format uses the big-endian one.

A screen shot below displays the header record of a memo file. The first four bytes define the location of next free block. Bytes 6 to 7 (0x00, 0x40) defines the size of a block -- 64 bytes, VFP default value (SET BLOCKSIZE). Clearly the big-endian convention is used.

In no way this is an obstacle had you decided to parse or modify Memo files, but of course needs to be taken into account.

As I found after completing this post, Visual FoxPro MSDN page states exactly the same: [for offset, length, and size fields in Memo File Structure (.FPT)] Integers stored with the most significant byte first.

Using PrivateObject class for unit testing of private methods in .NET

Whether private members should be unit tested or not, I should say that the PrivateObject class serves its purpose nicely.

Useful tools: Kleopatra for file and email encryption

Kleopatra of Gpg4Win for file and email encryption.

Will add some comments later.

Useful tools: Beyond Compare for file and folder comparison

Beyond Compare of Scooter Software for file and folder comparison.

Will add some comments later.