none
saving imported excel data to database

    Question

  • Hi. Can some please assist me with a code that will save the excel data that i imported into the datagridview. I got this code on the internet and is able to view the excel document but cannot save it to the database that i use. the data is a microsoft access database. The code is as mentioned below:
    Imports System.Data.SqlClient
    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
     
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "Net-informations.com")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0)
            MyConnection.Close()
     
        End Sub
    

    Thanks in anticipation
    Tuesday, March 14, 2017 3:57 PM

Answers

  • Hi kgotua,

    You import your data into a datatable da1 from excel worksheet and create another datatable da2 which is populated by the dataadapter from Access database. Then modify the data in da2. Finally we can use dataadapter.update method to save the data change to database.

    I do a simple sample you can refer to.

    Code sample:

    Public Class Form11
    
        Dim da1 As New DataTable
    
        Dim da2 As New DataTable
    
        Dim da As OleDbDataAdapter
    
        Dim conn As OleDbConnection
    
        Dim cb As OleDbCommandBuilder
    
        Private Sub Form11_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    
        End Sub
    
        Private Sub LoadStart()
    
            'Dim workCol As DataColumn = ds1.Columns.Add("ColumnID", GetType(Int32))
    
            'workCol.AllowDBNull = False
    
            'workCol.Unique = True
    
    
            'ds1.Columns.Add("ColumnName", GetType(String))
    
            'ds1.Columns.Add("ColumnSex", GetType(String))
    
            'ds1.Columns.Add("ColumnNumber", GetType(String))
    
    
    
        End Sub
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim _filename As String = "d:\new.xlsx"
    
            Dim _conn As String
    
            _conn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _filename & ";" & "Extended Properties='Excel 12.0 Xml;HDR=YES';"
    
            Dim _connection As OleDbConnection = New OleDbConnection(_conn)
    
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
    
            Dim _command As OleDbCommand = New OleDbCommand()
    
            _command.Connection = _connection
    
            _command.CommandText = "SELECT * FROM [Sheet1$]"
    
            da.SelectCommand = _command
    
            Try
    
    
                da.Fill(da1)
    
                MessageBox.Show("The import is complete!")
    
                Me.DataGridView1.DataSource = da1
    
                'Me.DataGridView1.DataMember = "sheet1"
    
            Catch e1 As Exception
    
                MessageBox.Show("Import Failed, correct Column name in the sheet!")
    
            End Try
    
        End Sub
    
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Database1.accdb; Persist Security Info=False;"
    
            Dim sel As String = "SELECT * FROM Table1"
    
            conn = New OleDbConnection(ConnectionString)
    
            da = New OleDbDataAdapter(sel, conn)
    
            cb = New OleDbCommandBuilder(da)
    
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
            da.Fill(da2)
    
            Me.DataGridView1.DataSource = da2
    
            'Me.DataGridView1.DataMember = "test"
    
        End Sub
    
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    
            For Each dr As DataRow In da1.Rows
    
                Dim expression As String
    
                expression = "ColumnID =" + CType(dr.Item(0), Integer).ToString
    
                Dim drs() As DataRow = da2.Select(expression)
    
                If (drs.Length = 1) Then
    
                    For i As Integer = 1 To da2.Columns.Count - 1
    
                        drs(0).Item(i) = dr.Item(i)
    
                    Next
    
                Else
    
                    Dim drnew As DataRow = da2.NewRow
    
                    For i As Integer = 0 To da2.Columns.Count - 1
    
                        drnew.Item(i) = dr.Item(i)
    
                    Next
    
                    da2.Rows.Add(drnew)
    
                End If
    
            Next
    
            Me.DataGridView1.DataSource = da2
    
    
            da.Update(da2)
    
        End Sub
    
    End Class
    

    Best Regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 16, 2017 1:23 AM
    Moderator

All replies

  • Since your connection string does not use HDR your column names I'm assuming are F1, F2, F3 etc. if so you need to alias your column names to match your column names in your database table.

    Once the aliasing is done use a OleDb connection and command (with an SQL INSERT statement), add parameters to the command object then using a for/each loop through the DataRow objects in the DataTable you loaded from Excel, set the parameters, execute ExecuteNonQuery.

    If you are just starting out then there are many pieces to place together, give it a shot, run into issues, show your code an indicate the problem.

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, March 14, 2017 5:55 PM
    Moderator
  • Okay, I had sometime to give you code that goes by the first reply I posted.

    This code modules has extension methods used later

    Imports System.Data.OleDb
    Public Class Connections
        Public Sub New()
        End Sub
        ''' <summary>
        ''' Create a connection where first row contains column names
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="IMEX"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <DebuggerStepThrough()>
        Public Function HeaderConnectionString(ByVal FileName As String, Optional ByVal IMEX As Integer = 1) As String
            Dim Builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR=Yes;", IMEX))
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR=Yes;", IMEX))
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ToString
    
        End Function
        ''' <summary>
        ''' Create a connection where first row contains data
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="IMEX"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <DebuggerStepThrough()>
        Public Function NoHeaderConnectionString(ByVal FileName As String, Optional ByVal IMEX As Integer = 1) As String
            Dim Builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR=No;", IMEX))
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR=No;", IMEX))
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ToString
    
        End Function
        ''' <summary>
        ''' Open a ACCDB file in the application folder
        ''' </summary>
        ''' <param name="FileName">Filename w/o path</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <DebuggerStepThrough()>
        Public Function AccessConnectionString(ByVal FileName As String) As String
            Dim Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FileName)
            }
    
            Return Builder.ConnectionString
    
        End Function
    
    End Class
    

    Core operations, ignore the last method.

    Imports System.Data.OleDb
    
    Public Class DataOperations
        Private Builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        Private Connection As Connections = New Connections
        Public Property ExcelFileName As String
        Public Property CsvFileName As String
        Public Property DatabaseName As String
        Public Property SheetName As String
        Public Property DataTable As DataTable
    
        Public Sub New()
    
        End Sub
        Public Function GetSheetData() As Boolean
            Dim dt As New DataTable
    
            Dim ConnectionString As String = Connection.HeaderConnectionString(ExcelFileName)
    
            Try
                Using cn As New OleDb.OleDbConnection With {.ConnectionString = ConnectionString}
                    Using cmd As New OleDb.OleDbCommand With {.CommandText = "SELECT id, FirstName, LastName FROM [" & SheetName & "$]", .Connection = cn}
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                    End Using
                End Using
            Catch ex As Exception
                Return False
            End Try
    
            Me.DataTable = dt
    
            Return True
    
        End Function
        Public Function ExportToAccess(ByVal dt As DataTable) As Boolean
            Dim success As Boolean = True
            Builder.DataSource = DatabaseName
    
            Using cn As New OleDbConnection(Builder.ConnectionString)
                Using cmd As New OleDbCommand("", cn)
                    cmd.CommandText = "INSERT INTO Customers (FirstName, LastName) VALUES (@FirstName, @LastName)"
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@FirstName", .DbType = DbType.String})
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@LastName", .DbType = DbType.String})
    
                    Try
                        cn.Open()
    
                        For Each row As DataRow In dt.Rows
                            cmd.Parameters(0).Value = row.Field(Of String)("FirstName")
                            cmd.Parameters(1).Value = row.Field(Of String)("LastName")
                            cmd.ExecuteNonQuery()
                        Next
    
                    Catch ex As Exception
                        success = False
                    End Try
                End Using
            End Using
    
            Return success
    
        End Function
        Public Function ExportToCsv() As Boolean
            Dim sb As New System.Text.StringBuilder
    
            Try
                Dim headerText = String.Join(",", Me.DataTable.Columns.Cast(Of DataColumn).Select(Function(c) c.ColumnName).ToArray)
                sb.AppendLine(headerText)
                For Each row As DataRow In Me.DataTable.Rows
                    sb.AppendLine(String.Join(",", row.ItemArray))
                Next
                IO.File.WriteAllText(Me.ExcelFileName, sb.ToString)
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function
    End Class
    

    Form code

    Public Class Form1
        Private dops As New DataOperations With
                {
                    .ExcelFileName = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "People.xlsx"),
                    .CsvFileName = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "People.csv"),
                    .DatabaseName = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb"),
                    .SheetName = "Customers"
                }
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            dops.ExportToAccess(CType(DataGridView1.DataSource, DataTable))
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If dops.GetSheetData Then
                DataGridView1.DataSource = dops.DataTable
            Else
                MessageBox.Show("Failed to get sheet data")
            End If
        End Sub
    End Class
    

    I used a ms-access and a excel file

    The excel file I exported from SQL Server

    ms-access table

    Running the above code where customers in the ms-access table was empty, now has rows from excel

    The above was easy as all fields are strings, in a real world app there will be dates, numbers, strings and booleans so you would need to type the OleParameters accordingly.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, March 14, 2017 10:58 PM
    Moderator
  • Thanx very much Karen. Ill try this code as Im not experienced and will seek further guidance where necessary
    Wednesday, March 15, 2017 4:43 PM
  • Hi kgotua,

    You import your data into a datatable da1 from excel worksheet and create another datatable da2 which is populated by the dataadapter from Access database. Then modify the data in da2. Finally we can use dataadapter.update method to save the data change to database.

    I do a simple sample you can refer to.

    Code sample:

    Public Class Form11
    
        Dim da1 As New DataTable
    
        Dim da2 As New DataTable
    
        Dim da As OleDbDataAdapter
    
        Dim conn As OleDbConnection
    
        Dim cb As OleDbCommandBuilder
    
        Private Sub Form11_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    
        End Sub
    
        Private Sub LoadStart()
    
            'Dim workCol As DataColumn = ds1.Columns.Add("ColumnID", GetType(Int32))
    
            'workCol.AllowDBNull = False
    
            'workCol.Unique = True
    
    
            'ds1.Columns.Add("ColumnName", GetType(String))
    
            'ds1.Columns.Add("ColumnSex", GetType(String))
    
            'ds1.Columns.Add("ColumnNumber", GetType(String))
    
    
    
        End Sub
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim _filename As String = "d:\new.xlsx"
    
            Dim _conn As String
    
            _conn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _filename & ";" & "Extended Properties='Excel 12.0 Xml;HDR=YES';"
    
            Dim _connection As OleDbConnection = New OleDbConnection(_conn)
    
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
    
            Dim _command As OleDbCommand = New OleDbCommand()
    
            _command.Connection = _connection
    
            _command.CommandText = "SELECT * FROM [Sheet1$]"
    
            da.SelectCommand = _command
    
            Try
    
    
                da.Fill(da1)
    
                MessageBox.Show("The import is complete!")
    
                Me.DataGridView1.DataSource = da1
    
                'Me.DataGridView1.DataMember = "sheet1"
    
            Catch e1 As Exception
    
                MessageBox.Show("Import Failed, correct Column name in the sheet!")
    
            End Try
    
        End Sub
    
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\Database1.accdb; Persist Security Info=False;"
    
            Dim sel As String = "SELECT * FROM Table1"
    
            conn = New OleDbConnection(ConnectionString)
    
            da = New OleDbDataAdapter(sel, conn)
    
            cb = New OleDbCommandBuilder(da)
    
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
            da.Fill(da2)
    
            Me.DataGridView1.DataSource = da2
    
            'Me.DataGridView1.DataMember = "test"
    
        End Sub
    
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    
            For Each dr As DataRow In da1.Rows
    
                Dim expression As String
    
                expression = "ColumnID =" + CType(dr.Item(0), Integer).ToString
    
                Dim drs() As DataRow = da2.Select(expression)
    
                If (drs.Length = 1) Then
    
                    For i As Integer = 1 To da2.Columns.Count - 1
    
                        drs(0).Item(i) = dr.Item(i)
    
                    Next
    
                Else
    
                    Dim drnew As DataRow = da2.NewRow
    
                    For i As Integer = 0 To da2.Columns.Count - 1
    
                        drnew.Item(i) = dr.Item(i)
    
                    Next
    
                    da2.Rows.Add(drnew)
    
                End If
    
            Next
    
            Me.DataGridView1.DataSource = da2
    
    
            da.Update(da2)
    
        End Sub
    
    End Class
    

    Best Regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 16, 2017 1:23 AM
    Moderator