How can creating dbf file, and define encoding in Notepad, or VBA

Question

what is DBF4 (dBase IV)(*.dbf) file fundamental format? And how can create these file in a same word editor as Notepad with typing?(Update:, or excel VBA?)

What is that's format specifications as:

• Delimiter (Same as: , or tab or etc)
• Separator (may Same as above!) (If these two are not synonymy)
• Row End character: (Same as vbCrLf)
• Defining headers of columns(fields).
• Code-Page of encoding: (same as: Unicode - 1256 or etc)
• and others...

Please present an algorithm for creating this DB file format that made us able to create a same file easily by a VBA method which creates a text file. (Update Or using built-in VBA or its references methods.)

I using below for creating text file.

Sub CsvExportRange(rngRange As Object, strFileName As String, strCharset, strSeparator As String, strRowEnd As String, NVC As Boolean) 'NVC: _
Null Value Control (If cell contain Null value, suppose reached end of range), d: delimiter

Dim rngRow As Range
Dim objStream As Object
Dim i, lngFR, lngLR As Long 'lngFR: First Row, lngLR: Last Row

lngFR = rngRange.SpecialCells(xlCellTypeVisible).Rows(1).row - rngRange.Rows(1).row + 1
lngLR = rngRange.End(xlDown).row - rngRange.Rows(1).row + 1

Set objStream = CreateObject("ADODB.Stream")
objStream.Type = 2
objStream.Charset = strCharset
objStream.Open

For i = lngFR To lngLR
    If Not (rngRange.Rows(i).EntireRow.Hidden) Then
        If IIf(NVC, (Cells(i + rngRange.Rows(1).row - 1, _
            rngRange.SpecialCells(xlCellTypeVisible).Columns(1).column).Value = vbNullString), False) Then Exit For
        objStream.WriteText CsvFormatRow(rngRange.Rows(i), strSeparator, strRowEnd)
    End If
Next i

objStream.SaveToFile strFileName, 2
objStream.Close
End Sub
Function CsvFormatRow(rngRow As Variant, strSeparator As String, strRowEnd As String) As String

Dim arrCsvRow() As String

ReDim arrCsvRow(rngRow.SpecialCells(xlCellTypeVisible).Cells.Count - 1)
Dim rngCell As Range
Dim lngIndex As Long

lngIndex = 0

For Each rngCell In rngRow.SpecialCells(xlCellTypeVisible).Cells
    arrCsvRow(lngIndex) = CsvFormatString(rngCell.Value, strSeparator)
    lngIndex = lngIndex + 1
Next rngCell

CsvFormatRow = Join(arrCsvRow, strSeparator) & strRowEnd

End Function
Function CsvFormatString(strRaw, strSeparator As String) As String

Dim boolNeedsDelimiting As Boolean

Dim strDelimiter, strDelimiterEscaped As String

strDelimiter = """"
strDelimiterEscaped = strDelimiter & strDelimiter

boolNeedsDelimiting = InStr(1, strRaw, strDelimiter) > 0 _
    Or InStr(1, strRaw, chr(10)) > 0 _
    Or InStr(1, strRaw, strSeparator) > 0

CsvFormatString = strRaw

If boolNeedsDelimiting Then
    CsvFormatString = strDelimiter & _
        Replace(strRaw, strDelimiter, strDelimiterEscaped) & _
        strDelimiter
End If

End Function

(Forgotten source)

Because I reached this: I should create a dbf file from my Excel Range by hand! After searching founded web sources.

Updated:

How can declare encoding of DBF?

About encoding that needed, considerable ones is Commonplace in this issue is Iran System encoding.

How can I store data with suitable encoding as Iran System in DB table records?

Answer 1

we have joy .... lol

this test code creates a dbf file from data in excel worksheet

creates a table and inserts one record

Sub dbfTest()

' NOTE:  put this test data at top of worksheet (A1:F2)

' Name    Date        Code    Date2       Description    Amount
' frank  11/12/2017  234.00  11/20/2018   paint          $1.34



'   ref: microsoft activex data objects

    Dim path As String
    Dim fileName As String

    filePath = "C:\database\"
    fileName = "test"


    Dim dc As Range
    Dim typ As String
    Dim fieldName As String
    Dim createSql As String

    createSql = "create table " + fileName + " ("          ' the create table query produces the file in directory

    Dim a As Variant

    For Each dc In Range("a1:e1")

        fieldName = dc.Value
        a = dc.offset(1).Value

        Select Case VarType(a)
            Case vbString:   typ = "varchar(100)"
            Case vbBoolean:  typ = "varchar(10)"
            Case vbInteger:  typ = "int"
            Case vbLong:     typ = "Double"
            Case vbDate:     typ = "TimeStamp"
            Case Else:       typ = "varchar(5)"            ' default for undefined types
        End Select

        createSql = createSql + " [" + fieldName + "]" + " " + typ + ","

    Next dc

    createSql = Left(createSql, Len(createSql) - 1) + ")"

    Debug.Print createSql

    Dim conn As ADODB.connection
    Set conn = CreateObject("ADODB.Connection")

    conn.Open "DRIVER={Microsoft dBase Driver (*.dbf)};" & "DBQ=" & filePath                                    ' both work
'   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties=dBASE IV"

    Dim cmd As ADODB.Command
    Set cmd = CreateObject("ADODB.Command")

    cmd.ActiveConnection = conn

    cmd.CommandText = createSql
    cmd.Execute

    Dim insertSql As String
    insertSql = "insert into " + fileName + " values("

    For Each dc In Range("a2:e2")
        insertSql = insertSql + "'" + CStr(dc.Value) + "',"
    Next dc

    insertSql = Left(insertSql, Len(insertSql) - 1) + ")"

    Debug.Print insertSql

    cmd.CommandText = insertSql

    cmd.Execute

    conn.Close
    Set conn = Nothing

End Sub