[VB.NET] Import Excel Spreadsheet to Data Table

[1UP]

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

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

 

[Lindows]

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

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

What can i exactly do with this?

 

[Irving]

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

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

Nice post very helpful.

 

[1UP]

What can i exactly do with this?

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

Already stated what it is for. Maybe not a good example but let's say you just created a new database and had some data already and you wanted a quick way to import it. Well this could be that solution.

 

[Lindows]

Already stated what it is for. Maybe not a good example but let's say you just created a new database and had some data already and you wanted a quick way to import it. Well this could be that solution.

oh ok, i get it now , i just needed it noob terms