Import csv into database datatable via openfiledialog-VBForums
Results 1 to 5 of 5

Thread: Import csv into database datatable via openfiledialog

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2013
    Posts
    7

    Import csv into database datatable via openfiledialog

    I am using Visual Basic Express 2010. I have added a SQL Server Compact 3.5 Database, a Dataset and a Datatable with 11 columns. While setting this up, VBE 2010 posted a DataTableBindingSource, DataTableAdapter, DataTableBindingNavigator, and TableAdapterManager. I added a Windows Form2 and dragged the DataTable onto Form2.

    From a button click on Form1, how can I openfiledialog, select a csv file with 11 columns, and import the values into the Database DataTable on Form2? The csv file will always have 11 columns but will vary in the number of rows from 1 to several hundred thousand. Any assistance would be appreciated.

    I believe I have the correct code for Button1 on Form1:

    Code:
    Private Sub ToolStripButton1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
    
            Dim openFileDialog1 As New OpenFileDialog()
            openFileDialog1.InitialDirectory = "c:\"
            openFileDialog1.Filter = "txt files (*.csv)|*.csv|All files (*.*)|*.*"
            openFileDialog1.FilterIndex = 1
            openFileDialog1.RestoreDirectory = True
    
            If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
    
                Dim frm2 As New Form2(openFileDialog1.FileName)
                frm2.Show()

    This is what I have so far on Form2 and where I am struggling to complete the code to load the datatable:

    Code:
    Imports System.IO
    Imports System.Data.OleDb
    
    Public Class Form2
    
        Public Sub New(ByVal fileName As String)
    
            InitializeComponent()
    
            Dim file As String = IO.Path.GetFileName(fileName)
            Dim path As String = IO.Directory.GetParent(fileName).FullName
    
            Try
    
                If IO.File.Exists(IO.Path.Combine(path, file)) Then
                    Dim ConStr As String = _
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                    path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
                    Dim conn As New OleDb.OleDbConnection(ConStr)
                    Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
                    file, conn)

  2. #2

  3. #3
    Frenzied Member circuits2's Avatar
    Join Date
    Sep 2006
    Location
    Kansas City, MO
    Posts
    1,027

    Re: Import csv into database datatable via openfiledialog

    The article provided by kevininstructor is going to be the fastest if the file is very large. I ran into the same situation in this thread:

    http://www.vbforums.com/showthread.p...ter&highlight=
    Show the love! Click (rate this post) under my name if I was helpful.

    My CodeBank Submissions: How to create a User Control | Move a form between Multiple Monitors (Screens) | Remove the MDI Client Border | Using Report Viewer with Visual Studio 2012 Express

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,079

    Re: Import csv into database datatable via openfiledialog

    SqlBulkCopy is only for SQL Server, not SQL Server CE.

    If you've already got a Data Source then what you need to do is create an instance of the appropriate DataTable, populate it from your CSV file and then use the corresponding table adapter to save all the data from the DataTable to the database. That's going to look something like this:
    Code:
    Dim table As New ThingDataTable
    
    Using connection As New OleDbConnection("connection string here"),
          inputAdapter As New OleDbDataAdapter("SQL query here", connection)
        'Keep the RowState of each DataRow as Added so they are ready to insert into the database.
        inputAdapter.AcceptChangesOnFill = False
    
        'Get the data.
        inputAdapter.Fill(table)
    End Using
    
    Dim outputAdapter As New ThingTableAdapter
    
    'Save the data.
    outputAdapter.Update(table)
    You need to change the type names as required for your Data Source and add the appropriate connection string and SQL code. You'll also need some exception handling on the data retrieval portion unless you are absolutely guaranteed that the data conforms to your table schema.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2013
    Posts
    7

    Re: Import csv into database datatable via openfiledialog

    Thank you, jmcilhinney, for letting me know that SQLBulkCopy is not available for my situation and providing me with the code example and explanation. That should get me on the right path. Thanks!

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

Survey posted by VBForums.