Packing a DBF

I thought my days of dealing with DBFs as a "production data" source were over, but HA (no such luck).
I recently had to retrieve, modify and replace some data that needed to be delivered in a DBF file.

Everything was fine until I realized / remembered the DBF driver does not ACTUALLY delete records from the data source -- it only marks them for deletion.  You are responsible for handling the "chaff" either by using a utility to remove deleted records or by simply ignoring them.  If imported into Excel, the marked-deleted records are ignored, but the file size will reflect the extra content.  After several rounds of testing CRUD, the output DBF was huge.

So, I went hunting for a method to "Pack" the records (removing deleted ones and resizing the DBF file) and eventually ran across the FOXPRO driver at ( ).  Once installed, I changed the DSN in the code to the new one I created in the ODBC Administrator and ran some tests. 

Using Microsoft Query, I simply tested the raw SQL command Pack {tablename} and it WORKED!
One really neat thing is the PACK command is used like regular SQL instructions; "Pack {tablename}" is all that is needed.
It is necessary, however, to close all connections to the database (and re-open) before issuing the PACK command or you will get the "File is in use" error. 


Here is some C# code for a Pack method.

      /// <summary>
      /// Pack the DBF removing all deleted records
      /// </summary>
      /// <param name="strTableName">The table to pack</param>
      /// <param name="strError">output of any errors</param>
      /// <returns>bool (true if no errors)</returns>
      public static bool Pack(string strTableName, ref string strError)
         bool blnRetVal = true;

            OdbcConnectionStringBuilder csbOdbc = new OdbcConnectionStringBuilder()
               Dsn = "PSAP_FOX_DBF"

            string strSQL = "pack " + strTableName;

            using (OdbcConnection connOdbc = new OdbcConnection(csbOdbc.ToString()))
               OdbcCommand cmdOdbc = new OdbcCommand(strSQL, connOdbc);
         catch (Exception exc)
            blnRetVal = false;
            strError = exc.Message;

         return blnRetVal;


posted @ Tuesday, March 23, 2010 11:37 AM

Comments on this entry:

# re: Packing a DBF

Left by peter at 2/11/2013 3:51 AM
thanks man this helped me a lot

# re: Packing a DBF

Left by sumit parihar at 7/11/2015 4:30 AM
How can we pack .dbf file in java

# re: Packing a DBF

Left by pawan srivastava at 12/12/2016 12:16 AM
how can we pack .dbf file in vb6

Your comment:

(not displayed)


Live Comment Preview: