DBF File Parsing with PHP - low-level file reading

[Otto]

...


Sometimes one forgets that DBF is just like an EXCEL table and that it could also be conveniently edited with a "TEXT EDITOR".

[Otto]

Hello friends,

here is a working sample.
Best regards,
Otto

for n := 1 to 50 like Charly uses in his tests!

Code: Select all | Expand

//prg
#include "FiveWin.ch"

static nStartTime, nDuration

PROCEDURE Main
    LOCAL cFilePath := "c:\fwh\samples\Data\database.dbf"
    LOCAL cName := "clark"
    LOCAL aResult

    aResult := FindNameInDbf(cFilePath, cName)

RETURN

FUNCTION FindNameInDbf(cFilePath, cName)
    LOCAL nHandle := FOPEN(cFilePath)
    LOCAL cHeader := SPACE(32)
    LOCAL nHeaderSize, nRecordSize, nNumRecords
    LOCAL aFieldDescriptors := {}
    LOCAL aFieldOffsets := {}
    LOCAL nOffset := 0
    LOCAL cFieldDescriptor, cFieldName
    LOCAL nFieldLength
    LOCAL nNameOffset, nNameLength
    LOCAL aMatchingRecords := {}
    LOCAL cRecord, cExtractedName 
    LOCAL hField, hRecordData
    LOCAL i, j
    LOCAL cFieldValue
    LOCAL hFieldDescriptor := { => }
    LOCAL nFound := 0
    LOCAL cFileData

    Msginfo("Start Suche")
    nStartTime := SECONDS()

    IF nHandle == -1
        ? "Konnte die Datei nicht öffnen."
        RETURN {}
    ENDIF

    // Read entire file into memory
    cFileData := MEMOREAD(cFilePath)

    // Header lesen
    cHeader := LEFT(cFileData, 32)

    // Byte-Interpretation der Header-Daten
    nNumRecords := (ASC(SUBSTR(cHeader, 5, 1)) + (ASC(SUBSTR(cHeader, 6, 1)) * 256) + (ASC(SUBSTR(cHeader, 7, 1)) * 65536) + (ASC(SUBSTR(cHeader, 8, 1)) * 16777216))
    nHeaderSize := (ASC(SUBSTR(cHeader, 9, 1)) + (ASC(SUBSTR(cHeader, 10, 1)) * 256))
    nRecordSize := (ASC(SUBSTR(cHeader, 11, 1)) + (ASC(SUBSTR(cHeader, 12, 1)) * 256))

    // Felddeskriptoren lesen
    FOR i := 33 TO nHeaderSize STEP 32
        cFieldDescriptor := SUBSTR(cFileData, i, 32)
        IF ASC(LEFT(cFieldDescriptor, 1)) == 13
            EXIT
        ENDIF
        cFieldName := RTRIM(SUBSTR(cFieldDescriptor, 1, 11))
        nFieldLength := ASC(SUBSTR(cFieldDescriptor, 17, 1))
        AADD(aFieldDescriptors, { "name" => cFieldName, "length" => nFieldLength })
    NEXT

    // Feld-Offsets berechnen
    FOR i := 1 TO LEN(aFieldDescriptors)
        hFieldDescriptor := aFieldDescriptors[i]
        AADD(aFieldOffsets, { hFieldDescriptor["name"], nOffset, hFieldDescriptor["length"] })
        nOffset += hFieldDescriptor["length"]
    NEXT
   
    nNameOffset := AScan(aFieldOffsets, { |a| LEFT(a[1], 10) = "LAST" })
    nNameLength := aFieldOffsets[nNameOffset, 3]

    // Process records
    FOR i := 1 TO nNumRecords
         cRecord := SUBSTR(cFileData, nHeaderSize + (i - 1) * nRecordSize + 1, nRecordSize)
       cExtractedName :=   ALLTRIM(LOWER( SUBSTR(cRecord, aFieldOffsets[nNameOffset, 2] + 1, nNameLength) ))
       IF cExtractedName = cName 
        
        
         
            nFound += 1

            hRecordData := { "recno" => i }
            nOffset := 0

            FOR j := 1 TO LEN(aFieldDescriptors)
                hField := aFieldDescriptors[j]
                cFieldValue := RTRIM(SUBSTR(cRecord, nOffset + 2, hField["length"]))
                hRecordData[hField["name"]] := cFieldValue
                nOffset += hField["length"]
            NEXT

            AADD(aMatchingRecords, hRecordData)
        ENDIF
    NEXT

    nDuration := (SECONDS() - nStartTime) * 1000 // Dauer in Millisekunden
    xbrowse(aMatchingRecords, "NumRecords"+ str(nNumRecords) + "   Searchtime: " + STR(nDuration, 10, 2) + " ms  Records found: " + STR(nFound))

    RETURN NIL
 


 

[Otto]

Dear Charly,

To compare the tests, I need to show new values again.

The previous test searched the entire database for the name "Clark" and displayed the hits.

If I read only 50 data entries as you posted in the test example, the time is 14 ms, making it the shortest in this test series.

Best regards,
Otto

Code: Select all | Expand

//prg
#include "FiveWin.ch"

static nStartTime, nDuration

PROCEDURE Main
    LOCAL cFilePath := "c:\fwh\samples\Data\database.dbf"
    LOCAL cName := "clark"
    LOCAL aResult

    aResult := FindNameInDbf(cFilePath, cName)

RETURN

FUNCTION FindNameInDbf(cFilePath, cName)
    LOCAL nHandle := FOPEN(cFilePath)
    LOCAL cHeader := SPACE(32)
    LOCAL nHeaderSize, nRecordSize, nNumRecords
    LOCAL aFieldDescriptors := {}
    LOCAL aFieldOffsets := {}
    LOCAL nOffset := 0
    LOCAL cFieldDescriptor, cFieldName
    LOCAL nFieldLength
    LOCAL nNameOffset, nNameLength
    LOCAL aMatchingRecords := {}
    LOCAL cRecord, cExtractedName 
    LOCAL hField, hRecordData
    LOCAL i, j
    LOCAL cFieldValue
    LOCAL hFieldDescriptor := { => }
    LOCAL nFound := 0
    LOCAL cFileData

    Msginfo("Start Suche")
    nStartTime := SECONDS()

    IF nHandle == -1
        ? "Konnte die Datei nicht öffnen."
        RETURN {}
    ENDIF

    // Read entire file into memory
    cFileData := MEMOREAD(cFilePath)

    // Header lesen
    cHeader := LEFT(cFileData, 32)

    // Byte-Interpretation der Header-Daten
    nNumRecords := (ASC(SUBSTR(cHeader, 5, 1)) + (ASC(SUBSTR(cHeader, 6, 1)) * 256) + (ASC(SUBSTR(cHeader, 7, 1)) * 65536) + (ASC(SUBSTR(cHeader, 8, 1)) * 16777216))
    nHeaderSize := (ASC(SUBSTR(cHeader, 9, 1)) + (ASC(SUBSTR(cHeader, 10, 1)) * 256))
    nRecordSize := (ASC(SUBSTR(cHeader, 11, 1)) + (ASC(SUBSTR(cHeader, 12, 1)) * 256))

    // Felddeskriptoren lesen
    FOR i := 33 TO nHeaderSize STEP 32
        cFieldDescriptor := SUBSTR(cFileData, i, 32)
        IF ASC(LEFT(cFieldDescriptor, 1)) == 13
            EXIT
        ENDIF
        cFieldName := RTRIM(SUBSTR(cFieldDescriptor, 1, 11))
        nFieldLength := ASC(SUBSTR(cFieldDescriptor, 17, 1))
        AADD(aFieldDescriptors, { "name" => cFieldName, "length" => nFieldLength })
    NEXT

    // Feld-Offsets berechnen
    FOR i := 1 TO LEN(aFieldDescriptors)
        hFieldDescriptor := aFieldDescriptors[i]
        AADD(aFieldOffsets, { hFieldDescriptor["name"], nOffset, hFieldDescriptor["length"] })
        nOffset += hFieldDescriptor["length"]
    NEXT
   
    nNameOffset := AScan(aFieldOffsets, { |a| LEFT(a[1], 10) = "LAST" })
    nNameLength := aFieldOffsets[nNameOffset, 3]

    // Process records
    FOR i := 1 TO nNumRecords
         cRecord := SUBSTR(cFileData, nHeaderSize + (i - 1) * nRecordSize + 1, nRecordSize)
       cExtractedName :=   ALLTRIM(LOWER( SUBSTR(cRecord, aFieldOffsets[nNameOffset, 2] + 1, nNameLength) ))
      
      
      
    //   IF cExtractedName = cName 
            nFound += 1

            hRecordData := { "recno" => i }
            nOffset := 0

            FOR j := 1 TO LEN(aFieldDescriptors)
                hField := aFieldDescriptors[j]
                cFieldValue := RTRIM(SUBSTR(cRecord, nOffset + 2, hField["length"]))
                hRecordData[hField["name"]] := cFieldValue
                nOffset += hField["length"]
            NEXT

            AADD(aMatchingRecords, hRecordData)
            
            if nFound > 50
            EXIT
            ENDIF
   //     ENDIF
    NEXT

    nDuration := (SECONDS() - nStartTime) * 1000 // Dauer in Millisekunden
    xbrowse(aMatchingRecords, "NumRecords"+ str(nNumRecords) + "   Searchtime: " + STR(nDuration, 10, 2) + " ms  Records found: " + STR(nFound))

    RETURN NIL
 

 

[Carles]

Otto,

Lapsus Otto solution: 11ms.
Lapsus RDD System: 1ms.

I will not answer this thread anymore...

C.