HOW TO IMPORT DATA FROM EXCEL TO SQL USING VB.NET

dear vb.net developers,

i am post the very simple to import data from excel to sql server.

first you need to import the following files.

step---1 

Imports Excel = Microsoft.Office.Interop.Excel

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.IO

step--2

declare following variables.

    Dim rs As DataSet

    Dim cmd As SqlCommand = myconnection.CreateCommand()

    Dim myTrans As SqlTransaction

    Dim xlApp As New Excel.Application

    Dim xlWorkBook As Excel.Workbook

    Dim xlWorkSheet As New Excel.Worksheet

dim path as string

step --3

store the path of excel file in variable path

like :- 

                path=C:\myfile.xls

                xlWorkBook = xlApp.Workbooks.Open(path)

                xlWorkSheet = xlWorkBook.Sheets.Item(1)

Me.ListView1.Items.Clear()

Me.ListView1.Columns.Clear()

With Me.ListView1

                    .Items.Clear()

                    .Columns.Add("a", 70)

                    .Columns.Add("b", 300)

                    .Columns.Add("c", 100)

                    .Columns.Add("d", 150)

                    .Columns.Add("e", 150)

                End With

'item 1 is sheet one

'like the same pattern you can select your excel sheet.

first of you need to store value in list view or datagrid its not necessary.

method to store value in list view.

you can start loop from 0 to n value. 

Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click

 For i = 0 To 9000

                    If CStr(xlWorkSheet.Cells(i + 3, 1).value) = "" And CStr(xlWorkSheet.Cells(i + 3, 2).value) = "" And CStr(xlWorkSheet.Cells(i + 3, 3).value) = "" Then

                        i = 9000

                    Else

                        With Me.ListView1

                            .Items.Add(xlWorkSheet.Cells(i + 3, 1).value)

                            .Items(i).SubItems.Add(xlWorkSheet.Cells(i + 3, 2).value)

                            .Items(i).SubItems.Add(xlWorkSheet.Cells(i + 3, 3).value)

                            If CStr(xlWorkSheet.Cells(i + 3, 6).value) = "" Then

                                .Items(i).SubItems.Add("")

                            Else

                                .Items(i).SubItems.Add(Trim$(Replace(xlWorkSheet.Cells(i + 3, 6).value, "'", " ")))

                            End If

                            If CStr(xlWorkSheet.Cells(i + 4, 7).value) = "" Then

                                .Items(i).SubItems.Add("")

                            Else

                                .Items(i).SubItems.Add(Trim$(Replace(xlWorkSheet.Cells(i + 3, 7).value, "'", " ")))

                            End If

                        End With

                    End If

                Next

'''''' this is used to dispose excel file which one you import.

 releaseObject(xlApp)

                releaseObject(xlWorkBook)

                releaseObject(xlWorkSheet)

'myconnection is your connection which one you used in your project.

  If myconnection.State = ConnectionState.Closed Then

                    myconnection.Open()

                    myTrans = myconnection.BeginTransaction(IsolationLevel.ReadCommitted, "MultipleTransaction")

                    cmd_new.Connection = myconnection

                    cmd_new.Transaction = myTrans

                End If

       For i = 0 To Me.ListView1.Items.Count - 1

 cmd.CommandText = ("insert into table_name(a,b,c,d,e) values(@a,@b,@c,@d,@e")

cmd.Parameters.Add("@a", SqlDbType.bigint).Value = Me.ListView1.Items(i).SubItems(0).Text

cmd.Parameters.Add("@b", SqlDbType.varchar,100).Value = Me.ListView1.Items(i).SubItems(1).Text

cmd.Parameters.Add("@c", SqlDbType.datetime).Value = Me.ListView1.Items(i).SubItems(2).Text

cmd.Parameters.Add("@d", SqlDbType.varchar,100).Value = Me.ListView1.Items(i).SubItems(3).Text

cmd.Parameters.Add("@e", SqlDbType.decimal,19,2).Value = Me.ListView1.Items(i).SubItems(4).Text

cmd.ExecuteNonQuery()

'this is used to clear declare parameter in cmd test file so that we can used it again and again within the loop

                    cmd.Parameters.Clear()

next 

    myTrans.Commit()

end sub

' procedure to dispose excel file.

Private Sub releaseObject(ByVal obj As Object)

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing

        Catch ex As Exception

            obj = Nothing

        Finally

            GC.Collect()

        End Try

    End Sub

'this is the simple procedure to store value in data base from excel file.


'any wants any code for vb.net then write me i will try to resolve your problem.