Importing Excel to a SQL DB Table in Visual Basic

ado.net , sql , visual basic , excel Newcastle, Australia
  • 10 years ago

    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=Micros oft.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()

  • 10 years ago

    I have tried another method but am getting connection errors on the excel connection

    'Excel Connection Dim plmExcelCon As System.Data.OleDb.OleDbConnection 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;")

        'Excel Settings
        Dim exWorkBook As Workbook = excelapp.Workbooks.Open(PrmPathExcelFile)
        Dim Sheet As Workbook = exWorkBook.Sheets(1)
        Dim excelRange As Range = Sheet.UsedRange
        excelValues = excelRange.Value(XlRangeValueDataType.xlRangeValueDefault)
    
        'Excel Sheet Values
        Dim RowCount As Integer
        Dim Temp As Integer
        Dim ex[Value] As String
    
        'Excel Connection Open
        plmExcelCon.Open()
    
        Temp = excelValues.GetLength(0)
        For RowCount = 2 To Temp Step 1
    
            If (excelValues(RowCount, 2 = Nothing)) Then
                ex[Value] = "Null"
            Else
                ex[Value] = excelValues(RowCount, 2).ToString
            End If
    
            cmdINSERT.Parameters("@[ColumnName]").Value = ex[Value]
    
            cnLD.Open()
            cmdINSERT.ExecuteNonQuery()
            cnLD.Close()
            plmExcelCon.Close()
    

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Before software should be reusable, it should be usable.” - Ralph Johnson