Cara Import Excel ke SQL

Masih banyak yang bingung gimana sih masukkin data yang ada di excel ke SQL server, masa kita harus insert satu persatu data yang di excel ke SQL, iya kalo data nya cuma dikit, tapi gimana kalo ratusan atau ribuan?seperti migrasi data karyawan. Ini solusinya, ane coba kasih source code untuk import dari excel ke SQL server.

Button [...] digunakan untuk membuka file excel nya, berikut codingnya

Private Sub Command1_Click()

Dim OFName As OPENFILENAME

    Dim XLS As Object

    Dim WRK As Object

    Dim SHT As Object

    On Error GoTo ex

    OFName.lStructSize = Len(OFName)

    'Set the parent window

    OFName.hwndOwner = Me.hWnd

    'Set the application's instance

    OFName.hInstance = App.hInstance

    'Select a filter

    OFName.lpstrFilter = "Excel Files (*.xls)" + Chr$(0) + "*.xls" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)

    'create a buffer for the file

    OFName.lpstrFile = Space$(254)

    'set the maximum length of a returned file

    OFName.nMaxFile = 255

    'Create a buffer for the file title

    OFName.lpstrFileTitle = Space$(254)

    'Set the maximum length of a returned file title

    OFName.nMaxFileTitle = 255

    'Set the initial directory

    OFName.lpstrInitialDir = "C:\"

    'Set the title

    OFName.lpstrTitle = "Open XLS File"

    'No flags

    OFName.flags = 0

    'Show the 'Open File'-dialog

    If GetOpenFileName(OFName) Then

        Text1.Text = Trim$(OFName.lpstrFile)

        

        Combo1.Clear

        'Create a new instance of Excel

        Set XLS = CreateObject("Excel.Application")

        

        'Open the XLS file. The two parameters representes, UpdateLink = False and ReadOnly = True. These parameters have this setting to dont occur any error on broken links and allready opened XLS file.

        Set WRK = XLS.Workbooks.Open(Text1.Text, False, True)

        'Read all worksheets in xls file

        For Each SHT In WRK.Worksheets

            'Put the name of worksheet in combo

            Combo1.AddItem SHT.Name

        Next

        'Close the XLS file and dont save

        WRK.Close False

        'Quit the MS Excel

        XLS.Quit

        

        'Release variables

        Set XLS = Nothing

        Set WRK = Nothing

        Set SHT = Nothing

    Else

        MsgBox "Cancel was pressed"

    End If

    Exit Sub

ex:

MsgBox Err.Description

End Sub

setelah file excel dipilih maka combobox akan terisi dengan judul sheet file excel, pilih sheet yang akan diimport dan Button [LOAD] digunakan untuk menampilkan data sheet Excel ke MSFlexGrid, nah data yang tampil ini lah yang akan masuk ke SQL. Setelah data dimuat dalam Grid maka Button [LOAD] akan berubah menjadi [IMPORT]. Berikut source nya :

On Error GoTo step_error

    Dim XLS As Object  'New Excel.Application

    Dim WRK As Object  'Excel.Workbook

    Dim SHT As Object 'Excel.Worksheet

    Dim RNG As Object ' Excel.Range

    

    Dim ArrayCells() As Variant

    

If Command2.Caption = "LOAD" Then

    

    If Combo1.ListIndex <> -1 Then

        'Create a new instance of Excel

        Set XLS = CreateObject("Excel.Application")

        'Open the XLS file. The two parameters representes, UpdateLink = False and ReadOnly = True. These parameters have this setting to dont occur any error on broken links and allready opened XLS file.

        Set WRK = XLS.Workbooks.Open(Text1.Text, False, True)

        'Set the SHT variable to selected worksheet

        Set SHT = WRK.Worksheets(Combo1.List(Combo1.ListIndex))

        

        'Get the used range of current worksheet

        Set RNG = SHT.UsedRange

        

        'Change the dimensions of array to fit the used range of worksheet

        ReDim ArrayCells(1 To RNG.Rows.Count, 1 To RNG.Columns.Count)

        

        'Transfer values of the used range to new array

'        If Option1.Value Then

            ArrayCells = RNG.Value

'        ElseIf Option2.Value Then

'            ArrayCells = RNG.Formula

'        End If

        

        'Close worksheet

        WRK.Close False

        'Quit the MS Excel

        XLS.Quit

        

        'Release variables

        Set XLS = Nothing

        Set WRK = Nothing

        Set SHT = Nothing

        Set RNG = Nothing

        

        'Configure the flexgrid to display data

        MSFlexGrid1.Redraw = False

        MSFlexGrid1.FixedCols = 0

        MSFlexGrid1.FixedRows = 0

        MSFlexGrid1.Rows = UBound(ArrayCells, 1)

        MSFlexGrid1.Cols = UBound(ArrayCells, 2)

        

        For r = 0 To UBound(ArrayCells, 1) - 1

            For c = 0 To UBound(ArrayCells, 2) - 1

                MSFlexGrid1.TextMatrix(r, c) = CStr(ArrayCells(r + 1, c + 1))

            Next

        Next

        MSFlexGrid1.Redraw = True

    Else

        MsgBox "DATA BELOM ADA OM!", vbCritical, "ERROR"

        Combo1.SetFocus

    End If

    MSFlexGrid1.AllowUserResizing = flexResizeBoth

  Command2.Caption = "IMPORT"

Exit Sub

step_error:

MsgBox Err.Number & " - " & Err.Description

Resume Next

Else

Dim rs As New ADODB.Recordset

Dim i As Double

'BUAT KONEKSI KE DATABASE

    Set cn = New ADODB.Connection

    cn.CursorLocation = adUseClient

    strkon = "Provider=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=sa;Data Source=isi server sql ente disini;Initial Catalog=isi nama database ente"

    cn.ConnectionString = strkon

    cn.Open

    

With MSFlexGrid1 'sesuaikan nama flexgrid ente

For i = 1 To MSFlexGrid1.Rows - 1

rs.Open "select * from tblhrtambahan where fperiode = '" & .TextMatrix(i, 0) & "'", cn, adOpenDynamic, adLockOptimistic ' masukkan query table yang akan menerima data excel 

If rs.EOF = True Then 'sesuaikan logika dengan kebutuhan ente

rs.AddNew 'insert data baru ke table yang ente query sebelumnya

rs!fperiode = .TextMatrix(i, 0) 'sebutkan field yang akan ente masukkan datanya (.textMatrix itu adalah data excel yang berada dalam grid ente, i disini adalah perolehan hasil looping untuk baris, sedangkan 0 nya adalah kolom pertama di grid)

rs!fnik = .TextMatrix(i, 1)

rs!fothercode = .TextMatrix(i, 2)

rs!fotherjumlah = .TextMatrix(i, 3)

rs!fkettambahan = .TextMatrix(i, 4)

rs.Update 'mengisi field table yang ente query sebelumnya dengan data di grid yang dijanarkan code di atas ini

Else

MsgBox ("Periode " & .TextMatrix(1, 0) & " sudah ada "), vbCritical, "ERROR" 'sesuaikan logika dengan kebutuhan ente

End If

rs.Close 'menutup recordset yang dipakai

Next i 'perulangan/looping baris

End With

MsgBox ("Tambahan Berhasil Diimport"), vbInformation, "INFO"

End If

Command2.Caption = "LOAD" 'mengembalikan button menjadi LOAD

MSFlexGrid1.Clear 'membersihkan data yang ada di grid