[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2006-06-26
3
Medium Priority
?
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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