import excel to sql db table

Question

I am trying to write data from an excel spread sheet to a SQL Database. I have been able to connect to the Excel Spreadsheet and read the data but I am unable to get the data to insert into the SQL DB table.

the current code is as follows

any help most appreciated.

    Dim plmExcelCon As System.Data.OleDb.OleDbConnection
    Dim ldExcelDS As System.Data.DataSet
    Dim cmdLoadExcel As System.Data.OleDb.OleDbDataAdapter
    Dim PrmPathExcelFile As String
    PrmPathExcelFile = txtImportFileLocation.Text.ToString

    plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + ";Extended Properties=Excel 12.0;")
    cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [" + txtImportSheetName.Text + "$]", plmExcelCon)
    ldExcelDS = New System.Data.DataSet
    cmdLoadExcel.Fill(ldExcelDS)

    dgvImportData.DataSource = ldExcelDS.Tables(0)

    plmExcelCon.Close()


   cmdINSERT.Parameters("@[SQL COLUMN NAME]").Value = [Not sure how to set value from datagrid view]


  cnLD.Open()
  cmdINSERT.ExecuteNonQuery()
  cnLD.Close()

Answer 1

I'm not sure if the code you have is able to work. But I think there is a better way to go about this.

When I have done stuff like this I have used ACCESS as a data container - it is possible to link ACCESS to both EXCEL data and to ODBC sources. If you have created the required links in ACCESS it is then a simple matter to run an INSERT INTO SQL query

But I think you could probably do it without ACCESS - as you can make VBA think EXCEL is an ODBC source - so you can probably make all the connections direct from VBA.

There are documents on the microsoft support site that explain the syntax and methods.

Answer 2

I was tasked with pulling some data from Excel, modifying it, and putting it into a SQL database as well. Below is something similar to what I did:

    Dim MyConnection As New System.Data.OleDb.OleDbConnection

    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    MyConnection = New System.Data.OleDb.OleDbConnection ("provider=Microsoft.Jet.OLEDB.4.0; data source='" + fileLocation + " ';Extended Properties=Excel 8.0;")

    ' Select the data from the 'Samples' sheet of the workbook.
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Samples$]", MyConnection)
    Dim ds as new DataSet
    MyCommand.Fill(ds)

    MyConnection.Close()

This is pretty much what you have already, and you said that works to pull the data from Excel. From there I would just iterate through the DataSet and add it to the database this way:

    Dim dt as DataTable = ds.Tables(0)
    For each row as DataRow in dt.Rows
        dim value1 as String = row("columnName")
        dim value2 as Double = row("columnName2")

        'Write a function that connects to the database with your parameters
        insertIntoDatabase(value1, value2)
    Next

Hopefully that is the bridge you needed (acessing a DataTable's data). Good luck!

Answer 3

I have finally got it to work with the following code

    Dim plmExcelCon As New System.Data.OleDb.OleDbConnection
    Dim cmdLoadExcel As System.Data.OleDb.OleDbDataAdapter
    Dim PrmPathExcelFile As String

    PrmPathExcelFile = txtImportFileLocation.Text.ToString

    plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + ";Extended Properties=Excel 12.0;")

    cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [" + txtImportSheetName.Text + "$]", plmExcelCon)

    Dim dt As System.Data.DataTable

    dt = New System.Data.DataTable

    cmdLoadExcel.Fill(dt)

    plmExcelCon.Close()

    For Each row As DataRow In dt.Rows
        Dim impEID As String = row(txtImportEID.Text)
        Dim impVID As String = row(txtImportVID.Text)

Try

            cmdINSERT.Parameters("@pldLifeDataEID").Value = impEID
            cmdINSERT.Parameters("@pldLifeDataVID").Value = impVID
            cmdINSERT.Parameters("@pldLifeDataDateEntry").Value = Date.Now

            cnLD.Open()
            cmdINSERT.ExecuteNonQuery()
            cnLD.Close()

        Catch ex As Exception
            MessageBox.Show(ErrorToString)
        End Try
    Next

thanks for all your help