How would you load a connection string from Ole DB Excel into a SQL Server table?

I am looking to load information from an Excel spreadsheet into a SQL Server table.  Using the OLE DB connection string with Excel(we'll call this Excelsheet1.xls), I would like to insert information into tblINFO1 in SQL Server from a database called BIZ.  If the record is already in the table, I would like it to update that record.  Please let me know how this would be done.  Thank you!
VBBRettAsked:
Who is Participating?
 
YZlatConnect With a Mentor Commented:
This function will update your database with that dataset

Sub UpdateDatabase(ByVal dsTemp as Dataset)
     Dim conn As SqlConnection
     Try
            conn = New SqlConnection("your connection string here")
            conn.Open()
        Catch ex As SqlException
           
            Console.Write("SQL ERROR: " & ex.Message)
        Catch ex As Exception
           
            Console.Write("ERROR: " & ex.Message)
        End Try
     Dim ds as New Dataset
     Dim dsTemp as New Dataset
      Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Table1", conn)
     Dim cmdBuilder as SqlCommandBuilder
     try
             ''fill dataset
              da.Fill(ds, "Table1")

               ''merge two datasets together
               ds.Merge(dsTemp)
               ''setup the InsertCommand, UpdateCommand, and DeleteCommand to reconcile the changes to the database
               cmdBuilder = new SqlCommandBuilder(da)

               da.Update(ds, "Table1")
     Catch ex As Exception
           
            Console.Write("ERROR: " & ex.Message)
     Finally
          If conn.State = ConnectionState.Open Then
                     conn.Close()
                 End If
        End Try
End Sub
0
 
YZlatCommented:
Here is a function that reads data from Excel spreadsheet into a dataset. You need to pass to it the path to the excel file to read from anf Sheet name of the sheet containing your data

Function ReadDataFromExcel(ByVal path As String, Optional ByVal sheet As String = "Sheet1") As DataSet
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection
        Try

            conn = New OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & path & "; " & _
                  "Extended Properties=Excel 8.0;")

            da = New OleDbDataAdapter("SELECT * FROM [" & sheet & "$]", conn)

            conn.Open()

            da.Fill(ds)
            ''get the name of the last column
            Dim lastCol As DataColumn
            lastCol = ds.Tables(0).Columns(ds.Tables(0).Columns.Count - 1)
            ''remove the last column from a DataTable
            ds.Tables(0).Columns.Remove(lastCol)

        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
        ReadDataFromExcel = ds
    End Function

After you'' retrieve the dataset you can update database with that dataset
0
 
VBBRettAuthor Commented:
Just a question.  How would you pass information about what the sheet name is that holds your data in your spreadsheet.  I'm not sure how to pass the sheet information as a reference to the function.  I guess what I'm asking is this...

Function ReadDataFromExcel(ByVal path As String, Optional ByVal sheet As String = "Sheet1") As DataSet

Not sure how Optional ByVal sheet as String = "Sheet1" gets it's information to process from where and I'm not sure how to send the information from the main program for this to receive sheet info.  What if the sheet had a different name say "sheetbroker1"?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.