Solved

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

Posted on 2006-06-26
3
235 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now