Importing Excel to a SQL DB Table in Visual Basic

• • Reply

  11 years ago

  by droyce

  David Edwards

  Newcastle, Australia

  Joined 11 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()

  Report abuse
• • Reply

  11 years ago

  by droyce

  David Edwards

  Newcastle, Australia

  Joined 11 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()
  

  Report abuse