Solved

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

Posted on 2006-06-26
3
239 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 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
location of a form 2 32
Create a datatable in vb.net dynamically 1 39
Get month and date in a format 4 45
how to remove error in database 6 37
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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