Importing Excel to a SQL DB table visual basic

[droyce]

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

 

[droyce]

Alternative method

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

 

[Hack]

What errors are you getting?

 

[Tom]

I think your first example is preferable for reading the Excel worksheet into a dataset since it doesnt require the need to automate Excel or even require the user to have Excel.

As for inserting from the dataset to the database, yes you can connect the source as a parameter for each of the source columns but you still need to associate a insert statement or connect it to a stored procedure that includes the insert statement which I dont see.