Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

DBF to SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
20 Nov 2019CPOL1 min read 18.8K   1.1K   10   9
Application lets you copy tables from folder with DBF files to SQL Server

Introduction

I developed this application to help me migrate DBFs to SQL Server. I hope someone else will also find this code useful.

Background

This application is fairly simple: you select the folder where DBF database files reside, select the SQL Server database you want to copy the tables, select the tables you want to copy and click "Copy tables". The application will create tables in the SQL server database and copy the data. It will also try to copy files locally in case they are located on a network drive.

Image 1

Using the Code

The application uses Microsoft Jet OLEDB provider to read data in 32 bit mode. The provider will not work in 64 bit. This is why it is compiled in 32 bit mode.

Image 2

DBF files can be password protected. The application uses JET OLEDB connection string property "Jet OLEDB:Database Password" for the password.

VB.NET
Function GetDbfConnectionString(ByVal sFolderPath As String, ByVal sPassword As String) _
    As String
    If sFolderPath = "" Then
        Return ""
    End If

    If sPassword <> "" Then
        Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFolderPath & ";_
        Extended Properties=dbase IV;Jet OLEDB:Database Password=" & sPassword & ";"
    Else
        Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFolderPath & ";_
        Extended Properties=dbase IV;"
    End If
End Function

Function "GetDbfRecCount" gets quick record count.

VB.NET
Private Function GetDbfRecCount(ByVal sFolderPath As String, _
                 ByVal sTableName As String) As Integer

    Dim sFilePath As String = IO.Path.Combine(sFolderPath, sTableName & ".dbf")

    If IO.File.Exists(sFilePath) = False Then
        Return 0
    End If

    Try
        Dim oBinaryReader As IO.BinaryReader = _
                    New IO.BinaryReader(IO.File.OpenRead(sFilePath))
        Dim buffer As Byte() = oBinaryReader.ReadBytes(Marshal.SizeOf(GetType(DBFHeader)))
        Dim handle As GCHandle = GCHandle.Alloc(buffer, GCHandleType.Pinned)
        Dim header As DBFHeader = CType(Marshal.PtrToStructure(handle.AddrOfPinnedObject(),_
                                  GetType(DBFHeader)), DBFHeader)
        handle.Free()
        oBinaryReader.Close()
        Return header.numRecords
    Catch ex As Exception
        'MsgBox(ex.Message)
    End Try

    Return 0
End Function

<StructLayout(LayoutKind.Sequential, CharSet:=CharSet.Ansi, Pack:=1)>
Private Structure DBFHeader
    Public version As Byte
    Public updateYear As Byte
    Public updateMonth As Byte
    Public updateDay As Byte
    Public numRecords As Int32
    Public headerLen As Int16
    Public recordLen As Int16
    Public reserved1 As Int16
    Public incompleteTrans As Byte
    Public encryptionFlag As Byte
    Public reserved2 As Int32
    Public reserved3 As Int64
    Public MDX As Byte
    Public language As Byte
    Public reserved4 As Int16
End Structure

CopyTableJet function does the actual work of copying data. It will insert 1000 records at a time if you select "SQL Ser 2008+".

VB.NET
Private Sub CopyTableJet(ByVal sTableName As String, dr As OleDbDataReader, _
                         ByRef cnDst As OleDbConnection)

        Dim oSchemaRows As Data.DataRowCollection = dr.GetSchemaTable.Rows
        Dim sRow As String
        Dim i As Integer
        Dim iRow As Integer = 0
        Dim iRowCount As Integer = 0

        'Get Header
        Dim sHeader As String = ""
        For i = 0 To oSchemaRows.Count - 1
            Dim sColumn As String = oSchemaRows(i)("ColumnName")
            If i <> 0 Then
                sHeader += ", "
            End If
            sHeader += PadSqlColumnName(sColumn)
        Next

        Dim sValues As String = ""

        While dr.Read()
            iRowCount += 1
            sRow = ""

            For i = 0 To oSchemaRows.Count - 1
                If sRow <> "" Then
                    sRow += ", "
                End If

                sRow += GetValueString(dr.GetValue(i))
            Next

            If chkSQL2008.Checked Then
                If sValues <> "" Then sValues += ", "
                sValues += "(" & sRow & ")"

                If iRowCount >= 1000 Then
                    Dim sSql1 As String = "INSERT INTO " & _
                    PadSqlColumnName(sTableName) & " (" & sHeader & ") VALUES " & sValues
                    OpenConnections(cnDst)
                    ExecuteSql(sSql1, cnDst)
                    iRowCount = 0
                    sValues = ""
                End If
            Else
                Dim sSql1 As String = "INSERT INTO " & PadSqlColumnName(sTableName) & _
                                      " (" & sHeader & ") VALUES (" & sRow & ")"
                OpenConnections(cnDst)
                ExecuteSql(sSql1, cnDst)
            End If

            iRow += 1
            ProgressBar1.Value = Math.Min(ProgressBar1.Maximum, iRow)
            lbCount.Text = iRow.ToString()
            lbCount.Refresh()

            'Listen for the user to press Cancel button
            Windows.Forms.Application.DoEvents()
            If bStop Then
                Log("Copied table " & sTableName & " stopped. ")
                Exit While
            End If

        End While

        If chkSQL2008.Checked And sValues <> "" Then
            Dim sSql1 As String = "INSERT INTO " & PadSqlColumnName(sTableName) & _
                                  " (" & sHeader & ") VALUES " & sValues
            ExecuteSql(sSql1, cnDst)
        End If

    End Sub

GetCreateTableSqlFromDbf function will create the table in SQL server if it does not exist.

VB.NET
Private Function GetCreateTableSqlFromDbf_
    (ByVal sTableName As String, dr As OleDbDataReader) As String

    Dim sb As New System.Text.StringBuilder()
    Dim oSchemaRows As Data.DataRowCollection = dr.GetSchemaTable.Rows
    Dim sKeyColumns As String = ""
    Dim i As Integer = 0

    sb.Append("CREATE TABLE " & PadSqlColumnName(sTableName) & " (" & vbCrLf)

    For iCol As Integer = 0 To oSchemaRows.Count - 1
        Dim sColumn As String = oSchemaRows(iCol).Item("ColumnName").ToString() & ""
        Dim sColumnSize As String = oSchemaRows(iCol).Item("ColumnSize").ToString() & ""
        Dim sDataType As String = oSchemaRows(iCol).Item("DATATYPE").FullName.ToString()
        Dim bAllowDBNull As Boolean = _
                  oSchemaRows(iCol).Item("AllowDBNull")    'Does not always work

        If i > 0 Then
            sb.Append(",")
            sb.Append(vbCrLf)
        End If

        sb.Append(PadSqlColumnName(sColumn))
        sb.Append(" " & PadAccessDataType(sDataType, sColumnSize))

        If bAllowDBNull Then
            sb.Append(" NULL")
        Else
            sb.Append(" NOT NULL")
        End If

        i += 1
    Next

    sb.Append(")")

    If i = 0 Then
        Return ""
    Else
        Return sb.ToString()
    End If

End Function

History

  • 20th November, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

Comments and Discussions

 
QuestionError encontered Pin
Jhunn13-Feb-24 16:18
Jhunn13-Feb-24 16:18 
QuestionUnable to copy table(s) from DBF Pin
Aswartha Vamsi24-May-23 4:19
Aswartha Vamsi24-May-23 4:19 
PraiseNice :) Pin
Jonas Hammarberg9-May-23 6:30
professionalJonas Hammarberg9-May-23 6:30 
QuestionBad coding, errors, etc. Pin
freezer279-Sep-22 9:47
freezer279-Sep-22 9:47 
QuestionDBF to SQL Server Pin
Roger Crossman26-Nov-19 3:12
Roger Crossman26-Nov-19 3:12 
AnswerRe: DBF to SQL Server Pin
Igor Krupitsky26-Nov-19 10:37
mvaIgor Krupitsky26-Nov-19 10:37 
QuestionInterfaces Pin
tlford6525-Nov-19 3:40
professionaltlford6525-Nov-19 3:40 
Praisevery nice Pin
kst123-Nov-19 4:36
kst123-Nov-19 4:36 
Very helpful and useful to recycle older databases. Thank you very much.
PraiseNice ! Pin
RickZeeland20-Nov-19 21:50
mveRickZeeland20-Nov-19 21:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.