This was something I came up with when I had to update a table from a spreadsheet.
This does not take into account of parameterizing your inputs or sanitizing them. This was just a quick and dirty way of importing data from a spreadsheet to a SQL database
I dumped the data into a table originally so I could get the column names. In my case they were F1 and F2
This does not take into account of parameterizing your inputs or sanitizing them. This was just a quick and dirty way of importing data from a spreadsheet to a SQL database
I dumped the data into a table originally so I could get the column names. In my case they were F1 and F2
Code:
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class frm_Main
Private Sub frm_Main_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim excelDataTable As New DataTable
excelDataTable = ImportExcelToDataTable("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\spread.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1""")
grdview_Excel.DataSource = excelDataTable
End Sub
Private Function ImportExcelToDataTable(excelConnString As String) As DataTable
ImportExcelToDataTable = New DataTable
Dim excelConn As OleDbConnection = New OleDbConnection(excelConnString)
Dim oldbDataAdaptor As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn)
excelConn.Open()
oldbDataAdaptor.Fill(ImportExcelToDataTable)
excelConn.Close()
End Function
Private Sub importDataToSQL(excelDataTabe As DataTable)
Dim ConnectionString As String = "Data Source=sqlServer;Initial Catalog=HUB;User ID=USENAME;Password=PASSWORD providerName=System.Data.SqlClient"
Dim sql As String = ""
Dim dbConn As SqlConnection
dbConn = New SqlConnection(ConnectionString)
dbConn.Open()
For Each row In excelDataTabe.Rows
'F1 and F2 just happen to be the column names that we got from excel. This can be seen in the Datagrid that is on the form
SQL = "INSERT INTO TABLE VALUES('" & row.Item("F1") & "')"
Dim sqlcommand As SqlCommand = New SqlCommand(SQL, dbConn)
sqlcommand.CommandText = SQL
sqlcommand.ExecuteNonQuery()
Next
dbConn.Close()
End Sub
End Class
