Wanna import data from MS-Excel to SQL SERVER?

Import data from Ms-Excel to SQL SERVER in windows application using VB.NET:


Step 1: Add Microsoft Excel reference to the project
(From COM Tab: Microsoft Excel 9.0/11.0 object library)

We get the reference Microsoft.office.core into our project


step 2: Create a form & in the code-behind file write the following:

Imports Microsoft.Office.Interop


Step 3: In form1.vb create Excel.Application instance
Dim obj As Excel.Application

Step 4: Let the user upload excel file containing data. In 'import' click write the following code:

obj = New Excel.Application

If File1.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim intNoofRecords As Int32
Dim dt As New DataTable
Dim dr As DataRow
dt.Columns.Add(New DataColumn("ItemName", GetType(System.String)))

Dim theWorkbook As Excel.Workbook =
obj.Workbooks.Open(File1.FileName, 0, True, 5, "", "", True, Excel.XlPlatform.xlWindows, "\t", False, False, 0, True)

Dim sheet As Excel.Sheets = theWorkbook.Worksheets

Dim worksheet As Excel.Worksheet = CType(sheet.Item(1), Excel.Worksheet)

Dim val As String

Dim R As Excel.Range

R = worksheet.Range("A2")

val = R.Cells.Value()

Dim i As Int32 = 2

While (Not val Is Nothing)
If val.Trim = "" Then
Exit While
End If

dr = dt.NewRow
dr.Item(0) = val
dt.Rows.Add(dr)

intNoofRecords = intNoofRecords + 1
i = i + 1

R = worksheet.Range("A" + i.ToString)
val = R.Cells.Value

End While


theWorkbook.Close() 'Dont forget to close the workbook. Otherwise EXCEL application remains in memory & we can see this in Task Manager.


--------------------------------------------------------------------------------------------
Now using ADO.NET data adapter object bulk insert the data into database.


Dim da As New System.Data.SqlClient.SqlDataAdapter

da.TableMappings.Add("Table", "ItemMst")

Dim MyConnection As New System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))

Dim MyCommand As New SqlClient.SqlCommand()
MyCommand.Connection = MyConnection
MyCommand.CommandText = "CreateItems_ForImport"
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ItemName", SqlDbType.VarChar, 50))

MyCommand.Parameters.Item("@ItemName").SourceColumn = "ItemName"


da.InsertCommand = MyCommand

Dim intdbRecordsEffected As Int32

Try

MyConnection.Open()
intdbRecordsEffected = da.Update(dt)

If intNoofRecords <> intdbRecordsEffected Then

MsgBox("Some of the records in the excel are already existing in the database. They are not imported. ")

End If

MsgBox("Successfully imported the item(s) to database.", MsgBoxStyle.Information, "Operation complete")

Catch ex As Exception
MsgBox("Failed to Import! Please try later.", MsgBoxStyle.Critical)
Finally
MyConnection.Close()
MyCommand = Nothing
da = Nothing
End Try