Post by Walt Decker on Dec 12, 2020 15:30:56 GMT -5
As I mentioned previously, to me a CSV file is not just a comma delimited file. It is a file containing defined field delimiters. These delimiters can be more than one byte in size, e. g. .<, .{{. The below code demonstrates this concept. The attached .zip file contains the same source code and the dll required to make it work. I had to re-write the functions contained therein to compensate for LB's limitations.
'
FETCH_FIELDS.ZIP (47.38 KB)
Path$ = DefaultDir$ + SPACE$(128) '<--- Path to data base
Exn$ = ".FBD" '<--- Comma delimited set of extensions
NumChrs = 0 '<--- Return number of characters in path
Hndl = 0 '<--- Handle of your window
NumRecs = 0 '<--- Number of records in data base
CurrRec = 0 '<--- Current record
RecLen = 0 '<--- # of characters in record
RetVal = 0
NumFlds = 0 '<--- # of fields in record
I = 0
Header$ = ""
Footer$ = ""
NameFld = 1
DateFld = 2
BookFld = 3
Txt$ = ""
MajDlm$ = "|"
SubDlm$ = ",,"
OPEN "E:\PBwin10\DLLS\DbFetch.dll" _ '<--- change to your path
FOR DLL AS #Fetch
'============================================================================/'
' FN_SELECTDB IS INCLUDED FOR CONVIENCE
'============================================================================/'
CALLDLL #Fetch, "FN_SelectDB", Hndl AS LONG, _ '<--- Handle of your window
Path$ AS PTR, _ '<--- Path to database file
Exn$ AS PTR, _ '<--- Comma delimited list of file
_ 'extensions with or without
_ 'a leading dot
NumChrs AS LONG '<--- Number of characters in new
'path or 0(zero) if no file
IF NumChrs > 0 then
Path$ = TRIM$(Path$)
PRINT Path$, NumChrs
NumChrs = LEN(Path$)
ELSE
CLOSE #Fetch
END
END IF '<--- is selected
'============================================================================/'
' INITIALIZE OR CREATE THE DATA BASE
'============================================================================/'
CALLDLL #Fetch, "FN_InitDB", Path$ AS PTR, _ '<--- Path and file name
NumChrs AS LONG, _ '<--- This is optional; however,
_ 'LB syntax does not allow optional
_ 'arguments so something has to got
_ 'there
NumRecs AS LONG '<--- Number of records in the file
PRINT "# of records in data base = "; NumRecs
CALLDLL #Fetch, "FN_FetchCurrentRecord", CurrRec AS LONG '<--- Get the current
'record
PRINT "The current record is "; CurrRec
'============================================================================/'
' CREATE A RECORD
' The data base file is automatically updated when a record is added,
' updated, or deleted
'============================================================================/'
Header$ = ".<SF AUTHORS>." '<--- This is a header or group identifer
'Headers and footers facilitate the search for
'data
CALLDLL #Fetch, "FN_AddRecord", _ '<--- Adds a record at the end of the data base
_ 'and updates the file
Header$ AS PTR, _ '<--- Data to be added to the file
NumRecs AS LONG '<--- # of records now in the file
PRINT "Number of records in the file = "; NumRecs
CurrRec = NumRecs
CALLDLL #Fetch, "FN_DeleteRecord", _ '<--- Delete a record
CurrRec AS LONG, _ '<--- Record to delete
NumRecs AS LONG '<--- Number of records in file.
'<--- May be 0(zero) or -1
PRINT "# NumRecs = "; NumRecs
CALLDLL #Fetch, "FN_AddRecord", Header$ AS PTR, CurRec AS LONG
'============================================================================/'
' ADD SOME MORE RECORDS USING DIFFERENT FIELDS
'============================================================================/'
Txt$ = "Anthony,,, Piers|1983|On a Pale Horse"
CALLDLL #Fetch, "FN_AddRecord", Txt$ AS PTR, CurRec AS LONG
Txt$ = "Asimov,,, Isac|1951 to 1953|Foundation Trilogy"
CALLDLL #Fetch, "FN_AddRecord", Txt$ AS PTR, CurRec AS LONG
Txt$ = "Brin,,, David|1980|Sundiver"
CALLDLL #Fetch, "FN_AddRecord", Txt$ AS PTR, CurRec AS LONG
Footer$ = ".<END SF AUTHORS>."
CALLDLL #Fetch, "FN_AddRecord", Footer$ AS PTR, CurRec AS LONG
'============================================================================/'
' SAVE THE FILE - RELEASE THE DLL
'============================================================================/'
CALLDLL #Fetch, "FN_SaveDbFile", NumRecs AS LONG
PRINT NumRecs
CLOSE #Fetch
'============================================================================/'
' OPEN THE DLL AND INITIALIZE THE DATA BASE
'============================================================================/'
OPEN "E:\PBwin10\DLLS\DbFetch.dll" _ '<--- change to your path
FOR DLL AS #Fetch
CALLDLL #Fetch, "FN_FetchNumRecords", _ '<--- This can be used to get the #
_ 'of records before opening the
_ 'data base fo;e
Path$ AS PTR, _ 'Path and file name
RetVal AS LONG '# of records
PRINT "# of records = ";RetVal
CALLDLL #Fetch, "FN_InitDB", Path$ AS PTR, NumChrs AS LONG, NumRecs AS LONG
PRINT "# of records = ";NumRecs
CALLDLL #Fetch, "FN_FetchMaxRecordLength", RecLen AS LONG '<--- Maximum number
'of characters for all
'records. Needed to
'size record output
PRINT "Max rec len = "; RecLen
'============================================================================/'
' LIST ALL RECORDS
' Can be used to set up arrays
'============================================================================/'
RetVal = 0
FOR I = 0 TO NumRecs - 1
Txt$ = SPACE$(RecLen)
CALLDLL #Fetch, "FN_FetchAllRecords", _ '<--- Retrieves all records
Txt$ AS PTR, _ '<--- Variable for records
RetVal AS LONG '<--- Record # or -1 when done
Txt$ = TRIM$(Txt$)
PRINT Txt$, RetVal
RetVal = 0
NEXT I
PRINT
'============================================================================/'
' FIND HEADER AND FOOTER
'============================================================================/'
CALLDLL #Fetch, "FN_FindHeader", _
4 AS LONG, _ '<--- Position to start search
Header$ AS PTR, _ '<--- Header text; NOT case sensitive
HeadStrt AS LONG '<--- Header record number or 0(zero) if
'not found
PRINT "Header found at Record "; HeadStrt
Strt = HeadStrt + 1
CALLDLL #Fetch, "FN_FindRange", _ '<--- Finds the footer
Strt AS LONG, _ '<--- Start search record
Footer$ AS PTR, _ '<--- Footer text; NOT case sensitive
HeadEnd AS LONG '<--- Record where footer is found
PRINT "End of group record = "; HeadEnd
'============================================================================/'
' FIND A RECORD AND INSERT RECORDS
'============================================================================/'
Txt$ = "Piers" '<--- Search IS case sensitive
EndPos = HeadEnd - 1
CALLDLL #Fetch, "FN_FindRecord", _
Strt AS LONG, _ '<--- Start record for search
EndPos AS LONG, _ '<--- Position to end search
Txt$ AS PTR, _ '<--- What to search for
RecNum AS LONG '<--- Record # or 0(zero) if not found
PRINT Txt$; " was found at record # "; RecNum
Txt$ = "Anthony,,, Piers|1977|A Spell for Chameleon"
RecPos = RecNum + 1
CALLDLL #Fetch, "FN_InsertRecord", _ '<--- Insert a record at record #
Txt$ AS PTR, _ '<--- What to insert
RecPos AS LONG, _ '<--- Where to insert the record
NumRecs AS LONG '<--- # of records now in the file
Txt$ = "Asimov" '<--- Search IS case sensitive
CALLDLL #Fetch, "FN_FindRecord", Strt AS LONG, EndPos AS LONG, _
Txt$ AS PTR, RecNum AS LONG
Txt$ = "Asimov,,, Isac|1989|Nemesis"
RecPos = RecNum + 1
CALLDLL #Fetch, "FN_InsertRecord", Txt$ AS PTR, RecPos AS LONG, NumRecs AS LONG
Txt$ = "Brin"
CALLDLL #Fetch, "FN_FindRecord", Strt AS LONG, EndPos AS LONG, _
Txt$ AS PTR, RecNum AS LONG
IF RecNum = 0 THEN
CALLDLL #Fetch, "FN_FindRange", Strt AS LONG, Footer$ AS PTR, HeadEnd AS LONG
EndPos = HeadEnd - 1
CALLDLL #Fetch, "FN_FindRecord", Strt AS LONG, EndPos AS LONG, _
Txt$ AS PTR, RecNum AS LONG
END IF
Txt$ = "Brin,,, David|1987|Uplift War"
RecPos = RecNum + 1
CALLDLL #Fetch, "FN_InsertRecord", Txt$ AS PTR, RecPos AS LONG, NumRecs AS LONG '<--- # of records now in the file
RetVal = 0
FOR I = 1 TO NumRecs
Txt$ = SPACE$(RecLen)
CALLDLL #Fetch, "FN_FetchAllRecords", _ '<--- Retrieves all records
Txt$ AS PTR, _ '<--- Variable for records
RetVal AS LONG '<--- Record # or -1 when done
Txt$ = TRIM$(Txt$)
PRINT Txt$, RetVal
RetVal = 0
NEXT I
'============================================================================/'
' GET A RECORD
'============================================================================/'
RetVal = 0
Txt$ = SPACE$(RecLen)
CALLDLL #Fetch, "FetchRecord", _
3 AS LONG, _ '<--- Record #
Txt$ AS PTR, _ '<--- Variable that will contain the record
RecLen AS LONG, _ '<# or characters in the above variable
RetVal AS VOID '<--- No return value
PRINT "Record #3 = "; Txt$
'============================================================================/'
' GET FIELDS FOR A RECORD
'============================================================================/'
CALLDLL #Fetch, "FN_FetchFieldsForRecord", _ '<--- Calculates the number of
_ 'fields in a record and sets up
_ 'a buffer to retrieve each field
3 AS LONG, _ '<--- Record number to retrieve
MajDlm$ AS PTR, _ '<--- Field delimiter
NumFlds AS LONG '<Number of fields
PRINT "Num fields = "; NumFlds
FOR I = 1 TO NumFlds
Txt$ = SPACE$(RecLen)' + CHR$(0)
RetVal = 0
CALLDLL #Fetch, "FN_FetchNextField", _ '<--- Get each field
Txt$ AS PTR, _ '<--- Variable to receive the field
RecLen AS LONG, _ '<--- # of characters in variable
RetVal AS LONG '<--- Field # retrieved; -1 when done
Txt$ = TRIM$(Txt$)
PRINT "Field # "; I; " = "; Txt$; " Record Field # = ";RetVal
NEXT I
'============================================================================/'
' GET ALL FIELDS IN A VARIABLE
'============================================================================/'
RecTxt$ = SPACE$(RecLen)
CALLDLL #Fetch, "FetchRecord", 5 AS LONG, RecTxt$ AS PTR, _
RecLen AS LONG, RetVal AS VOID
RecTxt$ = TRIM$(RecTxt$)
CALLDLL #Fetch, "FN_FetchAllFields", _ '<--- Get all the fields in the string
RecTxt$ AS PTR, _ '<--- Variabl containing string
0 AS LONG, _ '<--- Number of characters in string
MajDlm$ AS PTR, _ '<--- Delimter between fields
NumFlds AS LONG '<--- Number of fields ing the string
PRINT "# of fields = "; NumFlds; " Record text = "; RecTxt$
FOR I = 1 TO NumFlds '<--- Get all the fields
Txt$ = SPACE$(RecLen)
RetVal = 0
CALLDLL #Fetch, "FN_FetchNextField", Txt$ AS PTR, RecLen AS LONG, RetVal AS LONG
Txt$ = TRIM$(Txt$)
PRINT Txt$, RetVal
NEXT I
'============================================================================/'
' GET FIELD IN A VARIABLE
'============================================================================/'
FldTxt$ = SPACE$(RecLen)
CALLDLL #Fetch, "FN_FetchField", _
RecTxt$ AS PTR, _ '<--- Input variable
1 AS LONG, _ '<--- Field number
FldTxt$ AS PTR, _ '<--- Return variable
MajDlm$ AS PTR, _ '<--- Field delimiter
RetVal AS VOID '<--- No return value
FldTxt$ = TRIM$(FldTxt$)
PRINT "Input text = "; RecTxt$; " Return text = "; FldTxt$
Txt$ = SPACE$(RecLen)
CALLDLL #Fetch, "FN_FetchField", FldTxt$ AS PTR, 2 AS LONG, _
Txt$ AS PTR, SubDlm$ AS PTR, RetVal AS VOID
Txt$ = TRIM$(Txt$)
PRINT "Input text = "; FldTxt$; " Return text = "; Txt$
PRINT ""
'============================================================================/'
' ADD A FIELD TO A RECORD
'============================================================================/'
Txt$ = "Cham"
CALLDLL #Fetch, "FN_FindRecord", 1 AS LONG, 100 AS LONG, _
Txt$ AS PTR, RecNum AS LONG
RecTxt$ = SPACE$(RecLen)
CALLDLL #Fetch, "FetchRecord", RecNum AS LONG, RecTxt$ AS PTR, _
RecLen AS LONG, RetVal AS VOID
PRINT RecNum, RecTxt$
RecTxt$ = TRIM$(RecTxt$)
AddFld$ = "DOB"
RecTxt$ = RecTxt$ + SPACE$(LEN(AddFld$) + 1) '<--- New string placed in original
After = 1
CALLDLL #Fetch, "FN_InsertField", _
NameFld AS LONG, _ '<--- Base field
After AS LONG, _ '<--- Can be 1(one) or -1
_ ' 1 places the insertion after the
_ ' -1 places the insertion before the base field
RecTxt$ AS PTR, _ '<--- Original string
AddFld$ AS PTR, _ '<--- Text for the field
MajDlm$ AS PTR, _ '<--- RelimiterNum
Fields AS LONG '<Number of fields in the string
PRINT "New record = "; RecTxt$
'============================================================================/'
' UPDATE A FIELD
'============================================================================/'
AddFld$ = "20 June 1934"
RecTxt$ = RecTxt$ + SPACE$(LEN(AddFld$))
NewFld = NameFld + 1
CALLDLL #Fetch, "FN_UpdateField", _
NewFld AS LONG, _ '<--- Field to update
RecTxt$ AS PTR, _ '<--- Original recored
AddFld$ AS PTR, _ '<--- Field text
MajDlm$ AS PTR, _ '<--- Delimiter
NumChars AS LONG '<--- Number of characters in original record
PRINT "Updated string = "; RecTxt$; " Number of chars in string = "; NumChars
PRINT
'============================================================================/'
' UPDATE A RECORD
'============================================================================/'
CALLDLL #Fetch, "FN_UpDateRecord", _
RecTxt$ AS PTR, _ '<--- Updated record
RecNum AS LONG, _ '<--- Record to update
RetVal AS LONG '<--- Number of records in data base
PRINT "NumRecs = "; RetVal
'============================================================================/'
' DELETE A FIELD IN A STRING
'============================================================================/'
CALLDLL #Fetch, "FN_DeleteField", _
NewFld AS LONG, _ '<--- Field to delete
RecTxt$ AS PTR, _ '<--- Original string
_ ' The new string will be returned to the
_ ' original string
MajDlm$ AS PTR, _ '<--- Delimiter
NumFields AS LONG '<--- Number of fields in the string
PRINT RecTxt$, NumFields
PRINT
'============================================================================/'
' GET THE LENGTH OF A RECORD
'============================================================================/'
CALLDLL #Fetch, "FN_FetchCurrentRecord", CurrRec AS LONG '<--- Get the current
CALLDLL #Fetch, "FN_FetchRecordLength", CurrRec AS LONG, RecLen AS LONG
PRINT "The length of record "; CurrRec; " is "; RecLen
PRINT
'============================================================================/'
' GET THE NUMBER OF FIELDS IN A STRING
'============================================================================/'
RetVal = 0
Txt$ = "ab\cd\ef\g|h\ij\kl" + CHR$(0)
Dlm$ = "\" + CHR$(0)
RetStr$ = SPACE$(260)
PRINT "String = ";Txt$
CALLDLL #Fetch, "FN_FetchNumFields", _
Txt$ AS PTR, _ '<--- String to parse
Dlm$ AS PTR, _ '<--- Delimiter
RetVal AS LONG '<--- Number of fields
PRINT "Number of fields = "; RetVal
FieldNum = 3
CALLDLL #Fetch, "FN_FetchDelimPos", _ '<--- Get delimiter position in a string
FieldNum AS LONG, _ '<--- Field before the delimiter
Txt$ AS PTR, _ '<--- String to query
Dlm$ AS PTR, _ '<--- Delimiter to search for
DlmPosn AS LONG '<--- Where the delimiter is located
PRINT "Delimiter position between fields "; FieldNum; " and "; FieldNum + 1;
PRINT " is "; DlmPosn
CLOSE #Fetch
KILL Path$
WAIT
END
'
FETCH_FIELDS.ZIP (47.38 KB)