Below is the code to read from an excel file to a dataset. After retrieving into the datset you can read from it and insert into database :
Dim connectionString As String
connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=<<Path>>;Extended Properties=Excel 12.0;"
Dim objConn As New OleDbConnection(connectionString)
objConn.Open()
Dim strConString As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As New OleDbCommand(strConString, objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter()
' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ExcelData")
' Clean up objects.
objConn.Close()
mcr_subbu
Contributor
2316 Points
517 Posts
Re: How to read Excel file data and save into database?
Apr 02, 2009 01:02 PM|LINK
Below is the code to read from an excel file to a dataset. After retrieving into the datset you can read from it and insert into database :
Dim connectionString As String
connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=<<Path>>;Extended Properties=Excel 12.0;"
Dim objConn As New OleDbConnection(connectionString)
objConn.Open()
Dim strConString As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As New OleDbCommand(strConString, objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter()
' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ExcelData")
' Clean up objects.
objConn.Close()