1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Read dBASE database (.DBF)

Discussion in 'T&T - Scripting' started by Joe Caverly, Mar 14, 2010.

  1. Joe Caverly

    Joined:
    Aug 28, 2009
    Messages:
    638
    Likes Received:
    6
    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
     

    Attached Files:

Share This Page