Read dBASE database (.DBF)

  • This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.
#1
Below is a batch file that displays the structure of a dBASE database file (.DBF), and displays a record of your choice.

Code:
::--------------------------------------------------------------------+
::  Open a dBASE database, display the structure,                     |
::  and a desired record                                              |
::  Joe Caverly                                                       |
::  March 2010                                                        |
::                                                                    |
::--------------------------------------------------------------------+
@setlocal
@echo off
::--------------------------------------------------------------------+
:: Designate a file to use for this batch                             |
::--------------------------------------------------------------------+
if not exist %1 goto nofile
set dbfName=%1
::--------------------------------------------------------------------+
:: Get a handle to the file                                           |
::--------------------------------------------------------------------+
set hnd=%@fileopen[%dbfName,read]
if %hnd eq -1 goto abort
::--------------------------------------------------------------------+
:: Move to offset one of the file                                     |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,0,0]
::--------------------------------------------------------------------+
:: Get the dBASE version (3, or &H83 if there's a memo file)          |
::--------------------------------------------------------------------+
set dBASEVersion=%@filereadb[%hnd,1]
::--------------------------------------------------------------------+
:: Move to offset two of the file                                     |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,1,0]
::--------------------------------------------------------------------+
:: Get the Year of last update                                        |
::   NOTE: 109 = 2009, 110 = 2010                                     |
::--------------------------------------------------------------------+
set YLU=%@filereadb[%hnd,1]
set YLU=%@eval[1900+%YLU]
::--------------------------------------------------------------------+
:: Move to offset three of the file                                   |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,2,0]
::--------------------------------------------------------------------+
:: Get The Month of last update                                       |
::--------------------------------------------------------------------+
set MLU=%@filereadb[%hnd,1]
::--------------------------------------------------------------------+
:: Move to offset four of the file                                    |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,3,0]
::--------------------------------------------------------------------+
:: Get The Day of last update                                         |
::--------------------------------------------------------------------+
set DLU=%@filereadb[%hnd,1]
::--------------------------------------------------------------------+
:: Move to offset five of the file                                    |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,4,0]
::--------------------------------------------------------------------+
:: Total Number of Records in the File (long integer)                 |
::--------------------------------------------------------------------+
set TRecords=%@filereadb[%hnd,4]
set b1=%@word[0,%TRecords]
set b2=%@word[1,%TRecords]
set b3=%@word[2,%TRecords]
set b4=%@word[3,%TRecords]
set TRecords=%@eval[%b1 + (%b2 * 256) + (%b3 * 65536) + (%b4 * 16777216)]
::--------------------------------------------------------------------+
:: Move to offset nine of the file                                    |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,8,0]
::--------------------------------------------------------------------+
:: Number of bytes in the Header                                      |
::--------------------------------------------------------------------+
set BytesInHeader=%@filereadb[%hnd,2]
set b1=%@word[0,%BytesInHeader]
set b2=%@word[1,%BytesInHeader]
set BytesInHeader=%@eval[%b1 + (%b2 * 256)]
::--------------------------------------------------------------------+
:: Move to offset eleven of the file                                  |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,10,0]
::--------------------------------------------------------------------+
:: Length Of Records                                                  |
::--------------------------------------------------------------------+
set LOR=%@filereadb[%hnd,2]
set b1=%@word[0,%LOR]
set b2=%@word[1,%LOR]
set LOR=%@eval[%b1 + (%b2 * 256)]
::--------------------------------------------------------------------+
:: Calculate number of fields in the database                         |
::--------------------------------------------------------------------+
set FieldCount=%@eval[(%BytesInHeader - 33) / 32]
set FieldPtr=32
::--------------------------------------------------------------------+
:: Display Structure                                                  |
::--------------------------------------------------------------------+
echo Structure for database: %dbfName
echo Number of data records: %TRecords
echo Date of last update   : %MLU/%DLU/%YLU
echo Field  Field Name  Type        Width   Dec
set TotalWidth=0
do FieldNumber=1 to %FieldCount
  screen %_row 0 %@format[5,%FieldNumber]
  ::--------------------------------------------------------------------+
  :: Move to offset of field definition                                 |
  ::--------------------------------------------------------------------+
  set ptr=%@fileseek[%hnd,%FieldPtr,0]
  ::--------------------------------------------------------------------+
  :: Field Name                                                         |
  ::--------------------------------------------------------------------+
  set FieldName=%@fileread[%hnd,10]
  screen %_row 7 %FieldName
  ::--------------------------------------------------------------------+
  :: Move to offset of field type                                       |
  ::--------------------------------------------------------------------+
  set ptr=%@fileseek[%hnd,%@eval[%FieldPtr+11],0]
  ::--------------------------------------------------------------------+
  :: Field Type (C, D, L, M, N)                                         |
  ::--------------------------------------------------------------------+
  set FieldType=%@fileread[%hnd,1]
  switch %FieldType
    case C
      set TheType=Character
    case D
      set TheType=Date
    case L
      set TheType=Logical
    case M
      set TheType=Memo
    case N
      set TheType=Numberic
  endswitch
  screen %_row 19 %TheType
  ::--------------------------------------------------------------------+
  :: Move to offset of field width                                      |
  ::--------------------------------------------------------------------+
  set ptr=%@fileseek[%hnd,%@eval[%FieldPtr+16],0]
  ::--------------------------------------------------------------------+
  :: Field Width                                                        |
  ::--------------------------------------------------------------------+
  set FieldWidth=%@filereadb[%hnd,1]
  screen %_row 33 %@format[3,%FieldWidth]
  set TotalWidth=%@eval[%TotalWidth+%FieldWidth]
  ::--------------------------------------------------------------------+
  :: Move to offset of decimal places                                   |
  ::--------------------------------------------------------------------+
  set ptr=%@fileseek[%hnd,%@eval[%FieldPtr+17],0]
  ::--------------------------------------------------------------------+
  :: Number of decimal places (Numeric fields only)                     |
  ::--------------------------------------------------------------------+
  iff %FieldType eq N then
    set DecimalPlaces=%@filereadb[%hnd,1]
    iff %DecimalPlaces gt 0 then
      screen %_row 40 %@format[2,%DecimalPlaces]
    endiff
  endiff
  ::--------------------------------------------------------------------+
  :: Increment FieldPtr to next field                                   |
  ::--------------------------------------------------------------------+
  set FieldPtr=%@eval[%FieldPtr+32]
  echo.
enddo
::--------------------------------------------------------------------+
:: Add 1 to account for the delete flag                               |
::--------------------------------------------------------------------+
set TotalWidth=%@inc[%TotalWidth]
screen %_row 0 ** Total **
screen %_row 31 %@format[5,%TotalWidth]
echo.
::--------------------------------------------------------------------+
:: Determine the offset of the first record in the database           |
::--------------------------------------------------------------------+
set Record1Off=%@eval[%BytesInHeader+1]
::--------------------------------------------------------------------+
:: Record Number to get                                               |
::--------------------------------------------------------------------+
set Record2Get=1
if %Record2Get gt %TRecords goto ROOR
echo.
echo Record Number: %Record2Get
::--------------------------------------------------------------------+
:: Calculate the offset of the record in the database                 |
::--------------------------------------------------------------------+
set RecOff=%@eval[((%Record2Get - 1) * %TotalWidth) + %Record1Off]
::--------------------------------------------------------------------+
:: Display the record                                                 |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,%RecOff,0]
::--------------------------------------------------------------------+
:: Move to the offset of the record in the database                   |
::--------------------------------------------------------------------+
set TheRecord=%@fileread[%hnd,%TotalWidth]
::--------------------------------------------------------------------+
:: Display the record                                                 |
::--------------------------------------------------------------------+
echo %TheRecord
::--------------------------------------------------------------------+
:: Close the file                                                     |
::--------------------------------------------------------------------+
set rc=%@fileclose[%hnd]
::if %rc eq 0 echo File Closed
::if %rc eq -1 echo Error Occured Closing File
goto eoj
::--------------------------------------------------------------------+
:: Problem opening the database                                       |
::--------------------------------------------------------------------+
:abort
echo Database is in use, or another error occured opening the database.
goto eoj
::--------------------------------------------------------------------+
:: File does not exist                                                |
::--------------------------------------------------------------------+
:nofile
echo File does not exist.
echo USAGE: dbstruct x:\data\mydb.dbf
goto eoj
::--------------------------------------------------------------------+
:: Record Out Of Range                                                |
::--------------------------------------------------------------------+
:ROOR
echo Record Out Of Range (Max. %TRecords)
::--------------------------------------------------------------------+
:: End Of Job                                                         |
::--------------------------------------------------------------------+
:eoj
endlocal
There could be more error checking added, and also to ensure that the .DBF file is indeed a dBASE database. The %dBASEVersion environment variable is declared, and contains the .DBF version, if you wish to do so.

Being able to open and read a dBASE database from TCC has allowed me to eliminate the conversion from .DBF to SDF, and directly access a dBASE database from a batch file.

I know that dBASE is old-school (like me), but hopefully others will find this batch file of use.

Microsoft Excel still allows one to save a spreadsheet as a dBASE .DBF, so there must be others out there who still use dBASE databases.

I was considering writing this as a plugin using Delphi 2, but instead chose to convert my Turbo Pascal code to batch, to show just how powerful the TCC batch language is.

Joe
 

Attachments