WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
Miscellaneous

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

dbfFormatToTextConverter.wbt 

Keywords:  dbfFormatToTextConverter.wbt  file format

;Detlev Dalitz
;DD.20020510.1752

If ItemLocate("udfisdbf3",IntControl(77,103,0,0,0),@tab)>0 Then Goto skip_udfisdbf3
#DefineFunction udfIsDBF3 (Filename)
; make sure that the DBF Filename is not locked by another transaction
; read some header bytes
bb = BinaryAlloc(12)
result = (12 == BinaryReadex(bb,0,Filename,0,12))
If !result Then Goto label

; check version III/plus
result = (3 == (BinaryPeek(bb,0) & 3))
If !result Then Goto label

; check record count
dbfreccount = BinaryPeek4(bb,4)
result = (0 <= dbfreccount)
If !result Then Goto label

; check record length
dbfreclen = BinaryPeek2(bb,10)
result = (1 < dbfreclen)
If !result Then Goto label
result = (4000 >= dbfreclen)
If !result Then Goto label

; check file size
dbfheadlen = BinaryPeek2(bb,8)
result = (1 == BinaryReadex(bb,0,Filename,dbfheadlen+(dbfreccount*dbfreclen),1))
If !result Then Goto label
; check eof marker = 1Ah
result = (26 == BinaryPeek(bb,0))
If !result Then Goto label

; check field count
dbffldmax = 255 ; max field count
dbffldlen = 32  ; length of field description
dbffldeot = 13  ; end of table marker = 0Dh
bbfld = BinaryAlloc(dbffldlen)
offset = 32
fldcount = 0
While 1
   BinaryReadex(bbfld,0,Filename,offset,dbffldlen)
   If (dbffldeot == BinaryPeek(bbfld,0)) Then Break ; end of table
   fldcount = fldcount + 1
   If (dbffldmax < fldcount) Then Break ; too much fields
   offset = offset + dbffldlen
EndWhile
BinaryFree(bbfld)
result = (1 <= fldcount)
If !result Then Goto label
result = (dbffldmax >= fldcount)
If !result Then Goto label

:label
BinaryFree(bb)
Return (result)
; udfIsDBF3 checks DBF file version and format integrity.
; Returns @true if Filename seems to be a valid DBF III file, otherwise @false.
; Detlev Dalitz.20020510
#EndFunction
:skip_udfisdbf3


If ItemLocate("udfdbf3tocsv",IntControl(77,103,0,0,0),@tab)>0 Then Goto skip_udfdbf3tocsv
#DefineFunction udfDBF3toCSV (InFilename, OutFilename, DelMode, TrimMode)
If !FileExist(InFilename) Then Return (@false) ; no input file
If (OutFilename == "") Then Return (@false) ; no output file
TrimMode = Min(2,Max(0,TrimMode))
DelMode = Min(1,Max(0,DelMode))

; read dbf field table
fldnamelist = ""
fldtypelist = ""
fldlenlist = ""
dbffldlen = 32  ; length of field description
dbffldeot = 13  ; end of table marker = 0Dh
bbfld = BinaryAlloc(dbffldlen)
offset = 32
While 1
   BinaryReadex(bbfld,0,InFilename,offset,dbffldlen)
   If (dbffldeot == BinaryPeek(bbfld,0)) Then Break ; end of table
   fldname = BinaryPeekstr(bbfld,0,10)
   fldtype = BinaryPeekstr(bbfld,11,1)
   fldlen  = BinaryPeek(bbfld,16)
   fldnamelist = ItemInsert(fldname,-1,fldnamelist,@tab)
   fldtypelist = ItemInsert(fldtype,-1,fldtypelist,@tab)
   fldlenlist  = ItemInsert(fldlen,-1,fldlenlist,@tab)
   offset = offset + dbffldlen
EndWhile
BinaryFree(bbfld)
fldcount = ItemCount(fldnamelist,@tab)

; read dbf info
bbinfo = BinaryAlloc(12)
BinaryReadex(bbinfo,0,InFilename,0,12)
dbfreccount = BinaryPeek4(bbinfo,4)
dbfheadlen  = BinaryPeek2(bbinfo,8)
dbfreclen   = BinaryPeek2(bbinfo,10)
BinaryFree(bbinfo)

; create output file
fout = FileOpen(OutFilename, "WRITE")
; write header record
headerlist = ""
For fcount=1 To fldcount
   fldname = ItemExtract(fcount,fldnamelist,@tab)
   fldname = StrCat('"',fldname,'"')
   headerlist = ItemInsert(fldname,-1,headerlist,",")
Next
FileWrite(fout,headerlist)

; read dbf records, write out to textfile
bbrec = BinaryAlloc(dbfreclen)
offset = dbfheadlen
For icount=1 To dbfreccount
   If (dbfreclen <> BinaryReadex(bbrec,0,InFilename,offset,dbfreclen)) Then Break
   offset = offset + dbfreclen ; calculate next record

   ; leading byte = 2Ah if record is deleted, = 20h if record is not deleted
   If (0 == DelMode) Then If (42 == BinaryPeek(bbrec,0)) Then Continue ; skip over deleted record

   datalist = ""
   recoffset = 1
   For fcount=1 To fldcount
      fldtype = ItemExtract(fcount, fldtypelist,@tab)
      fldlen = ItemExtract(fcount, fldlenlist,@tab)
      data = BinaryPeekstr(bbrec,recoffset,fldlen)

      ; trim data
      Select TrimMode
      Case 2
         data = StrTrim(data)
         Break
      Case 1
         tmp = StrTrim(data)
         data = StrSub(data,1,StrIndex(data,tmp,0,@fwdscan)+StrLen(tmp)-1)
         Break
      Case 0
         Break
      EndSelect

      ; quote data
      Select 1
      Case (fldtype == 'N')
         Break
      Case (fldtype == 'M')
         data = '"MEMO"'
         Break
      Case 1 ; 'C', 'D', 'L'
         data = StrCat('"',data,'"')
         Break
      EndSelect

      datalist = ItemInsert(data,-1,datalist,",")
      recoffset = recoffset + fldlen
   Next
   FileWrite(fout,datalist)
Next

BinaryFree(bbrec)
FileClose(fout)

Return (FileExist(OutFilename))
; udfDBF3toCSV reads a DBF III plus file and creates a text file with comma separated fields
; TrimMode = 2 = trims leading and trailing blanks
; TrimMode = 1 = trims trailing blanks
; TrimMode = 0 = skip trimming blanks
; DelMode  = 1 = show deleted records too
; DelMode  = 0 = skip deleted records
; Detlev Dalitz.20020510
#EndFunction
:skip_udfdbf3tocsv


If ItemLocate("udfdbf3totab",IntControl(77,103,0,0,0),@tab)>0 Then Goto skip_udfdbf3totab
#DefineFunction udfDBF3toTAB (InFilename, OutFilename, DelMode)
If !FileExist(InFilename) Then Return (@false) ; no input file
If (OutFilename == "") Then Return (@false) ; no output file
DelMode = Min(1,Max(0,DelMode))

; read dbf field table
fldnamelist = ""
fldlenlist = ""
dbffldlen = 32  ; length of field description
dbffldeot = 13  ; end of table marker = 0Dh
bbfld = BinaryAlloc(dbffldlen)
offset = 32
While 1
   BinaryReadex(bbfld,0,InFilename,offset,dbffldlen)
   If (dbffldeot == BinaryPeek(bbfld,0)) Then Break ; end of table
   fldname = BinaryPeekstr(bbfld,0,10)
   fldlen  = BinaryPeek(bbfld,16)
   fldnamelist = ItemInsert(fldname,-1, fldnamelist,@tab)
   fldlenlist  = ItemInsert(fldlen,-1, fldlenlist,@tab)
   offset = offset + dbffldlen
EndWhile
BinaryFree(bbfld)
fldcount = ItemCount(fldnamelist,@tab)

; read dbf info
bbinfo = BinaryAlloc(12)
BinaryReadex(bbinfo,0,InFilename,0,12)
dbfreccount = BinaryPeek4(bbinfo,4)
dbfheadlen  = BinaryPeek2(bbinfo,8)
dbfreclen   = BinaryPeek2(bbinfo,10)
BinaryFree(bbinfo)

; create output file
fout = FileOpen(OutFilename, "WRITE")
; write header record
FileWrite(fout,fldnamelist)

; read dbf records, write out to textfile
bbrec = BinaryAlloc(dbfreclen)
offset = dbfheadlen
For icount=1 To dbfreccount
   If (dbfreclen <> BinaryReadex(bbrec,0,InFilename,offset,dbfreclen)) Then Break
   offset = offset + dbfreclen ; calculate next record
   ; leading byte = 2Ah if the record is deleted, = 20h if not deleted

   If (0 == DelMode) Then If (42 == BinaryPeek(bbrec,0)) Then Continue ; skip over deleted record

   datalist = ""
   recoffset = 1
   For fcount=1 To fldcount
      fldlen = ItemExtract(fcount, fldlenlist,@tab)
      data = BinaryPeekstr(bbrec,recoffset,fldlen)
      datalist = ItemInsert(data,-1,datalist,@tab)
      recoffset = recoffset + fldlen
   Next
   FileWrite(fout,datalist)
Next

BinaryFree(bbrec)
FileClose(fout)

Return (FileExist(OutFilename))
; udfDBF3toTAB reads a DBF III file and creates a text file with database fields separated by tab char.
; Detlev Dalitz.20020510
#EndFunction
:skip_udfdbf3totab


;--- test ---
InFilename = "\\DD-01\BEISPIEL\ANGEST.DBF" ; choose your own DBF III/plus file

If udfIsDBF3 (InFilename)
   TmpFilename = FileCreateTemp("TMP") ; temporary file for test output

   ; do not trim leading or trailing blanks
   ; do not show deleted records
   If udfDBF3toCSV (InFilename, TmpFilename, 0, 0) ; no trim
      RunWait("notepad", TmpFilename) ; wait for closing notepad
   EndIf

   ; trim trailing blanks
   ; do not show deleted records
   If udfDBF3toCSV (InFilename, TmpFilename, 0, 1)
      RunWait("notepad", TmpFilename) ; wait for closing notepad
   EndIf

   ; trim leading and trailing blanks
   ; show deleted records too
   If udfDBF3toCSV (InFilename, TmpFilename, 1, 2)
      RunWait("notepad", TmpFilename) ; wait for closing notepad
   EndIf

   ; do not show deleted records
   If udfDBF3toTAB (InFilename, TmpFilename, 0)
      RunWait("notepad", TmpFilename) ; wait for closing notepad
   EndIf

   ; show deleted records too
   If udfDBF3toTAB (InFilename, TmpFilename, 1)
      RunWait("notepad", TmpFilename) ; wait for closing notepad
   EndIf

   FileDelete(TmpFilename) ; cleaning
EndIf

Exit



Article ID:   W15536

File Created: 2003:05:13:11:28:36
Last Updated: 2003:05:13:11:28:36