Importing excel workbook into sql server in vb.net

EDN Admin · Oct 29, 2012

<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:green First Attempt:
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Imports<span style="font-family:Times New Roman,serif System.Data.OleDb
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Imports<span style="font-family:Times New Roman,serif System.Data.SqlClient
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Public<span style="font-family:Times New Roman,serif
<span style="color:blue Class <span style="color:#2B91AF Form1
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Protected <span style="color:blue Function RetrieveData(<span style="color:blue ByVal strConn
<span style="color:blue As <span style="color:blue String) <span style="color:blue
As 
<span style="color:blue Dim dtExcel <span style="color:blue As
<span style="color:blue New <span style="color:#2B91AF DataTable()
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Using conn <span style="color:blue As
<span style="color:blue New 
<span style="color:green Initialize an OleDbDataAdapter object.

<span style="color:blue Dim da <span style="color:blue As <span style="color:blue
New <span style="color:#2B91AF OleDbDataAdapter(<span style="color:#A31515 "select * from [Sheet1$]", conn)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:green Fill the DataTable with data from the Excel spreadsheet.

da.Fill(dtExcel)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none



<span style="color:blue End <span style="color:blue Function
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Protected <span style="color:blue Sub SqlBulkCopyImport(<span style="color:blue ByVal dtExcel
<span style="color:blue As 
<span style="color:blue Using conn <span style="color:blue As
<span style="color:blue New <span style="color:#2B91AF SqlConnection(<span style="color:#A31515 "Server=Excel-PC;Database=Northwind.MDB;Trusted_Connection=True;")


conn.Open()
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Using bulkCopy <span style="color:blue As
<span style="color:blue New 
<span style="color:green Specify the destination table name.
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

bulkCopy.DestinationTableName = 

<span style="color:green bulkCopy.ColumnMappings.Clear()
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none


<span style="color:green bulkCopy.ColumnMappings.Add("Field1", "Field1")

<span style="color:green bulkCopy.ColumnMappings.Add("Field2", "Field2")

<span style="color:green ....And so on
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

bulkCopy.WriteToServer(dtExcel)



<span style="color:blue End <span style="color:blue Sub
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Private <span style="color:blue Sub Button2_Click(sender
<span style="color:blue As System.<span style="color:#2B91AF Object, e
<span style="color:blue As System.<span style="color:#2B91AF EventArgs)
<span style="color:blue Handles Button2.Click
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Dim strExcelConn <span style="color:blue
As <span style="color:blue String = <span style="color:#A31515 "C:UsersExcelDesktopBook1.xls"
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Dim dtExcel <span style="color:blue As
<span style="color:#2B91AF DataTable = RetrieveData(strExcelConn)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none


SqlBulkCopyImport(dtExcel)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue End <span style="color:blue Sub
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue End<span style="font-family:Times New Roman,serif
<span style="color:blue Class
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt <span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:green Second Attempt:
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Imports<span style="font-family:Times New Roman,serif System.Data.OleDb
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Imports<span style="font-family:Times New Roman,serif System.Data.SqlClient
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Imports<span style="font-family:Times New Roman,serif System.Data.Common
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Public<span style="font-family:Times New Roman,serif
<span style="color:blue Class <span style="color:#2B91AF Form1
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Private <span style="color:blue Sub Button1_Click(sender
<span style="color:blue As System.<span style="color:#2B91AF Object, e
<span style="color:blue As System.<span style="color:#2B91AF EventArgs)
<span style="color:blue Handles Button1.Click
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Dim constring <span style="color:blue As
<span style="color:blue String = <span style="color:#A31515 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:UsersExcelDesktopBook1.xls;Extended Properties="

<span style="color:blue Dim con <span style="color:blue As
<span style="color:#2B91AF OleDbConnection = <span style="color:blue New

<span style="color:blue Dim da <span style="color:blue As <span style="color:#2B91AF
OleDbDataAdapter = <span style="color:blue New <span style="color:#2B91AF
OleDbDataAdapter(<span style="color:#A31515 "select * from sheet1$", con)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:green string constring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:UsersExcelDesktopBook1.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""

<span style="color:green Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:UsersExcelDesktopBook1.xls;Extended Properties="

<span style="color:blue Dim excelConnectionString <span style="color:blue
As <span style="color:blue String = <span style="color:#A31515 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:UsersExcelDesktopBook1.xls;Extended Properties=""""Excel 8.0;HDR=YES;"""""

<span style="color:green Create Connection to Excel Workbook
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Dim connection <span style="color:blue As
<span style="color:#2B91AF OleDbConnection = <span style="color:blue New
<span style="color:#2B91AF OleDbConnection(excelConnectionString)

connection.Open()

<span style="color:green Create DbDataReader to Data Worksheet
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Dim dr <span style="color:blue As <span style="color:#2B91AF
DbDataReader = Command.ExecuteReader

<span style="color:green Bulk Copy to SQL Server
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Dim bulkCopy <span style="color:blue As
<span style="color:#2B91AF SqlBulkCopy = <span style="color:blue New

bulkCopy.WriteToServer(dr)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue End <span style="color:blue Sub
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue End<span style="font-family:Times New Roman,serif
<span style="color:blue Class
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt <span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:green Third Attempt:
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Imports<span style="font-family:Times New Roman,serif System.Data.OleDb
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Imports<span style="font-family:Times New Roman,serif System.Data.SqlClient
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue Public<span style="font-family:Times New Roman,serif
<span style="color:blue Class <span style="color:#2B91AF Form1
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Private <span style="color:blue Sub Button1_Click(sender
<span style="color:blue As System.<span style="color:#2B91AF Object, e
<span style="color:blue As System.<span style="color:#2B91AF EventArgs)

<span style="color:blue Dim excelConnection <span style="color:blue
As System.Data.OleDb.<span style="color:#2B91AF OleDbConnection = 
System.Data.OleDb.OleDbConnection(<span style="color:#A31515 "Provider=Micros oft.Jet.OLEDB.4.0;DataSource=C:UsersExcelDesktopBook1.xls;Extended Properties=Excel 8.0;")

<span style="color:#2B91AF SqlConnection.Open()
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue Dim excelCommand <span style="color:blue
As <span style="color:blue New System.Data.OleDb.<span style="color:#2B91AF OleDbCommand(<span style="color:#A31515 "INSERT INTO [OBDC; Driver={SQLServer};Server=(Excel-PC);Database=FullDate;Trusted_Connection=yes].[Population]SELECT
* FROM [Sheet1$];", SqlConnection)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:green Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQLServer};Server=(Excel-PC);Database=Northwind.MDB;Trusted_Connection=yes].[Population] FROM [Sheet1$];", excelConnection)
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

excelCommand.ExecuteNonQuery()

<span style="color:#2B91AF SqlConnection.Close()
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

<span style="color:blue End <span style="color:blue Sub
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif; color:blue End<span style="font-family:Times New Roman,serif
<span style="color:blue Class
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif
<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none
<span style="font-family:Times New Roman,serif


<p style="margin-bottom:0in; margin-bottom:.0001pt; line-height:normal; text-autospace:none

I don’t have many errors with any of these solutions, but I do get a couple errors which I can’t seem to get my head around.
 Maybe it’s as simple as adding a reference; not sure.
Can someone please help me get each of these solutions working?
I know there is always more than one way to skin a cat, and I always love to see different means to the same end.
<p style="margin-bottom:0in; margin-bottom:.0001pt <hr class="sig Ryan Shuell

View the full article

Importing excel workbook into sql server in vb.net

EDN Admin

Well-known member

Similar threads