Solved

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

Posted on 2006-06-26
3
238 Views
Last Modified: 2010-04-23
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!
0
Comment
Question by:VBBRett
  • 2
3 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 16987265
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
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 16987312
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
 

Author Comment

by:VBBRett
ID: 16993017
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question