dcsimg
Results 1 to 12 of 12

Thread: How do I export a datatable to a .dbf file?

  1. #1

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    100

    How do I export a datatable to a .dbf file?

    I have a datatable that I want to export to a .dbf file type. Everything I've found on the internet has not been helpful at all

  2. #2

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    100

    Re: How do I export a datatable to a .dbf file?

    Figured it out, this is my code:

    Code:
    Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & path & “;” & “Extended Properties=dBase IV”)
    
                dBaseConnection.Open()
    
                'New table
                Dim SQLCreateCommand As String
                'SQLCreateCommand = “CREATE TABLE NewTable ” & “(subdir TEXT(50))”
                Dim NewString As String = dt.ToString
                SQLCreateCommand = "SELECT * INTO NewTable From " & TableName
                Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(SQLCreateCommand, dBaseConnection)
    
                dBaseCommand.ExecuteNonQuery()
                dBaseConnection.Close()

  3. #3

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    100

    Re: How do I export a datatable to a .dbf file?

    Several years later, I have found a better way of doing this using the FastDBF lib reference found here: https://github.com/SocialExplorer/FastDBF

    My previous solution required that a dbf with the same column structure already exits. The solution below creates a dbf with the columns that exist in the DataTable:

    Code:
    Private Sub ExportListButton_Click(sender As Object, e As EventArgs) Handles ExportListButton.Click
    
            Dim ExportedDbf = New DbfFile(Encoding.GetEncoding(1252))
            ExportedDbf.Open(Path.Combine("C:\WORK\A_TEST", "Combined.dbf"), FileMode.Create)
    
            For Each col In CombinedDataTable.Columns
                ExportedDbf.Header.AddColumn(New DbfColumn(col.ToString, DbfColumn.DbfColumnType.Character, 50, 0))
            Next
    
            Dim Counter As Integer = 0
    
            For Each row In CombinedDataTable.Rows
    
                Dim ColumnCounter As Integer = 0
                Dim NewRec = New DbfRecord(ExportedDbf.Header)
    
                For Each col In CombinedDataTable.Columns
    
                    NewRec(ColumnCounter) = CombinedDataTable.Rows(Counter)(col).ToString
                    ColumnCounter = ColumnCounter + 1
                Next
    
                ExportedDbf.Write(NewRec, True)
                Counter = Counter + 1
    
            Next
    
            ExportedDbf.Close()
    
        End Sub

  4. #4
    New Member fabiopoles's Avatar
    Join Date
    Feb 2020
    Posts
    2

    Re: How do I export a datatable to a .dbf file?

    I just red your interesting post.
    I have following situation: an Access 97 machine needs to take data from an Access2019 db. Unfortunately there is no way to upgrade the A97 machine.
    I would like to write a VB code to create from A2019 a .dbf file to link then inside A97.
    First question: do you see any better solution for the A97 machine?
    If "No" how can I implement your above solution? I am not a programmer and I am neither aware how to install the FastDBF lib in Access2019.
    Thanks for your kind help in advance.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,918

    Re: How do I export a datatable to a .dbf file?

    I think you would be better off starting a new thread with this question worded a bit differently. The solution shown above is an explicit programming solution. Installing a library likely isn't going to help you any if you aren't a programmer, as it likely won't do the work on its own. That particular library gives programmers tools to use for working with a database, and doesn't appear to have any utility for non-programmers. If you aren't looking for a programming solution, you might start a thread in Office Development or General PC, and lead off with not looking for a programming solution. On the other hand, if you are comfortable with a bit of coding in something like VBA, then Office Development would be the right place.
    Last edited by Shaggy Hiker; Feb 26th, 2020 at 11:11 AM.
    My usual boring signature: Nothing

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,439

    Re: How do I export a datatable to a .dbf file?

    here 2 Sql for Import/Export to a AccessDB

    Code:
    'Export from Access table to .Dbf
    Dim sSQL As String
     sSQL = "Select * Into [Test.dbf] In 'c:\test' 'dBase III;' From TableXYZ"
    Code:
    'Import
    
    sSQL = "Select * Into Table1 From [Test.dbf] In 'c:\test' 'dBase III;' "
    try dbase III or IV

    hth
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,439

    Re: How do I export a datatable to a .dbf file?

    Hi fabio,

    no need to send a PM, you can ask here

    here a way to execute the Sql

    Code:
     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim sSql As String
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=D:\Db2010.accdb")
            con.Open()
    
            'Import from .dbf to Access table:
            'sSql = "Select * Into Table1 From [Kunden.dbf] In 'D:\Testfolder' 'dBase III;' "
            'ExecuteSQL(con, sSql)
    
    
            'Export table from Access to .dbf:
            sSql = "Select * Into [Test.dbf] In 'D:\Testfolder' 'dBase III;' From tbl_Artikel"
            ExecuteSQL(con, sSql)
    
            con.Close()
            con = Nothing
        End Sub
    
      Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                    ByVal sSQL As String, _
                                    Optional ByRef ErrMessage As String = Nothing, _
                                    Optional ByVal TransAction As  _
                                    OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    hth
    Last edited by ChrisE; Feb 27th, 2020 at 06:31 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8
    Addicted Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    165

    Re: How do I export a datatable to a .dbf file?

    Hi ChrisE, your code runs very well for accdb to dbf, but not viceversa, I don't receive error messages, but no accdb file.
    Maybe you have to change the connection string ?

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,439

    Re: How do I export a datatable to a .dbf file?

    Quote Originally Posted by patel45 View Post
    Hi ChrisE, your code runs very well for accdb to dbf, but not viceversa, I don't receive error messages, but no accdb file.
    Maybe you have to change the connection string ?
    I did just check and it works fine

    what .dbf File are you trying to Import
    a note .. the Dbase file should not have >8 characters
    try it and Export a Accesstable to dbase with the name 'myTestDbasefile'
    Code:
            sSql = "Select * Into [myTestDbasefile.dbf] In 'D:\Testfolder' 'dBase III;' From tbl_Artikel"
    if you look in the TestFolder folder you will see the dbase file but it will look like this 'MYTESTDB.DBF'


    or try in the Sql dbase IV
    Code:
      sSql = "Select * Into [myTestDbasefile.dbf] In 'D:\Testfolder' 'dBase IV;' From

    hth
    Last edited by ChrisE; Feb 28th, 2020 at 03:35 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10
    Addicted Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    165

    Re: How do I export a datatable to a .dbf file?

    OK CrisE, I did not understand the following:
    when I convert accdb to dbf I create the dbf file, when I convert dbf to accdb the accdb file must already exist, I add a table, I don't create a new file.

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,439

    Re: How do I export a datatable to a .dbf file?

    Quote Originally Posted by patel45 View Post
    OK CrisE, I did not understand the following:
    when I convert accdb to dbf I create the dbf file, when I convert dbf to accdb the accdb file must already exist, I add a table, I don't create a new file.
    the Access DB must exist, with the Sql you create a new Table in the Access DB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12
    Addicted Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    165

    Re: How do I export a datatable to a .dbf file?

    Thank You for your time and your teachings

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width