DBF to CSV

Here's a sample Visual Objects function to take a DBF file and output it to a CSV that can then be imported into a SQLite table.

FUNCTION DBF2CSV(cCSVFile AS STRING, cDBFFile AS STRING) AS VOID STRICT
// cCSVFile is the name of the output file; cDBFFile is the name of the DBF
LOCAL phandle AS PTR
LOCAL oOutput AS DBServer
LOCAL aResAry AS ARRAY
LOCAL nLen AS DWORD
LOCAL nTemCnt AS DWORD
LOCAL cString AS STRING
LOCAL cVar AS STRING
LOCAL cSeparator AS STRING
LOCAL CRLF AS STRING

CRLF := CHR( 13 ) + CHR(10) // line terminator

cSeparator := "|" // SQLite uses pipe characters as CSV separators

oOutput := DbServer{ cDBFFile }

// make an array of the dbf structure
aResAry := oOutput:DbStruct

// initialize the output CSV file
phandle := FCreate( cCSVFile, 0)

nLen := ALen( aResAry )

oOutput:GoTop()
WHILE !oOutput:EOF

cString := Space(0)
FOR nTemCnt :=1 UPTO nLen

// loop the file structure array; one element for each field

DO CASE
CASE aResAry[nTemCnt, DBS_TYPE] = "C" // if it's a character field
/*
// THIS IS IMPORTANT. Most tables will require a method for going to a specific record.
Difficult to do that in SQL without a unique identifier (something like a guid) for
each record. Here is where we check. If the field is FQ (our chosen name for the
unique identifier field), then we check to ensure it contains a unique identifier.

IF oOutput:FieldName( nTemCnt ) = "FQ" .AND. Empty( oOutput:FIELDGET( nTemCnt ) )
oOutput:XReplQ()
ENDIF
*/
// here we strip characters that will get in the way of the import
cVar := Trim( StrTran( oOutput:FIELDGET( nTemCnt ), ["]) ) // no quotes
cVar := StrTran( cVar, CRLF, "<CR>" ) // no returns
cVar := StrTran( cVar, CHR( 13 ) ) // found this in a notepad file
cVar := StrTran( cVar, CHR( 10 ) ) // found this in a notepad file
cVar := StrTran( cVar, CHR( 2 ) ) // found this in a notepad file

CASE aResAry[nTemCnt, DBS_TYPE ] = "N" // if it's a numeric field
IF oOutput:FIELDGET(nTemCnt) = 0
cVar := Space(0)
ELSE
cVar := LTrim(Str( oOutput:FIELDGET( nTemCnt ), aResAry[nTemCnt, 3], aResAry[nTemCnt, 4] ))
ENDIF

CASE aResAry[nTemCnt, DBS_TYPE ] = "D" // if it's a date, make it DToS()
cVar := DToS( oOutput:FIELDGET( nTemCnt ) )

CASE aResAry[nTemCnt, DBS_TYPE ] = "L" // if it's a logic field, make it Y or N
cVar := IIF( oOutput:FIELDGET( nTemCnt ) = TRUE, "Y", "N" )

OTHERWISE
cVar := Space(0)
ENDCASE
// remove any pipe characters that might be contained in the field
cVar := StrTran( cVar, cSeparator )

IF nTemCnt = 1
// if it's the first field in the record, create a new string
cString := cVar
ELSE
cString += cSeparator + cVar
ENDIF

NEXT

FWrite( phandle, cString + CRLF ) // write the string and terminate with CR/LF
oOutput:Skip( 1 )
ENDDO
oOutput:Close()
FClose(phandle)

RETURN