VB 2010 : Membaca Data Excel dan Menyimpannya ke Database MDB

Setelah agak lama tidak update blog karena kesibukan, alhamdulillah hari ini saya bisa mengunjungi dan mengupdate kembali blog saya.

Setelah sebelumnya saya share coding untuk menyimpan data ke Excel dengan VB 2010, kali ini saya akan share coding tentang bagaimana membaca data dari file Excel, mengolah datanya dan  kemudian menyimpannya ke dalam database. Dalam hal ini saya menggunakan Microsoft Access Database (MDB).

File Excel yang akan saya simpan adalah file tsjob.xlsx seperti tampak di bawah ini.

Sedangkan Tabel yang akan saya update adalah table tsjob yang ada pada database PM.MDB. Struktur tabel tsjob adalah sebagai beikut :

Field Name       Field Type            Field Size
brandnm              text                       35
projectcat            text                       50
jobgroupno          text                       11
jobgroupnm         text                       50
jobno                   text                      50
oldjobno              text                      50
oldjobgroupno     text                      11
oldjobgroupnm    text                      50
jobid                   text                      10
jobnm                 text                     150
customernm        text                       40
jobmgr               text                       40
begin                  date/time
status                 text                       30
billable               Yes/No
description         memo
projectcode       text                        61
company           text                        25 


Selanjutnya lakukan langkah-langkah berikut :
1. Buka VB 2010 kemudian create project baru.
2. Tambahkan form dan beri name frmExcel2mdb.
3. Tambahkan 2 buah datagrid pada form yang telah dibuat.
4. Beri nama salah satu Datagrid yang baru saja ditambahkan dengan grdData. Datagrid ini digunakan untuk menampilkan data hasil import dari file excel.
5. Beri nama datagrid yang lainnya dengan grdJob. Datagrid ini digunakan untuk menampilkan data dari file mdb.
6. Tambahkan 2 button pada form. Satu button diberi nama cmdReadExcel. Dan yang satunya diberi nama cmdSavetoMDB.
7. Selanjutnya copy code di bawah ini.
8. Semoga bermanfaat.

 

Imports System.Data
Imports System.Data.OleDb

Public Class frmExcel2mdb
    Dim ds As DataSet
    Dim da As OleDbDataAdapter
    Dim ds2 As DataSet
    Dim da2 As OleDbDataAdapter

    Private Sub cmdReadExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdReadExcel.Click
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\tsjob.xlsx;Extended Properties=Excel 12.0;"
        Dim myConnection As OleDbConnection = New OleDbConnection
       
        Dim strSQL As String

        Try
          
            myConnection.ConnectionString = connString
            strSQL = "Select * from [Sheet1$]"
            da = New OleDbDataAdapter(strSQL, myConnection)
            ds = New DataSet
            da.Fill(ds, "dataTable")
            grdData.DataSource = ds.Tables(0)
            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub cmdSavetoMDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSavetoMDB.Click
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\PM.mdb;Jet OLEDB:Database Password=;"
        Dim MyCon = New OleDbConnection()
        Dim strSql As String
        Dim dgRow As DataGridViewRow
        Dim strSQLi As String
        Dim strCmd As OleDbCommand

        Try
            MyCon.ConnectionString = strConn

            MyCon = New OleDbConnection(strConn)
            If MyCon.State <> ConnectionState.Closed Then MyCon.Close()
            MyCon.Open()

            For Each dgRow In Me.grdData.Rows

                Dim cmdJob As New OleDbCommand
                Dim daJob As New OleDbDataAdapter
                Dim dsJob As New DataSet
                Dim dtJob As New DataTable

                strSql = "Select count(*) from tbJob"
                cmdJob = MyCon.CreateCommand
                cmdJob.CommandText = "SELECT * FROM tsJob where Trim(JobNo) = '" & Trim(dgRow.Cells(3).Value) & "'"
                daJob.SelectCommand = cmdJob
                daJob.Fill(dsJob, "Job")
                dtJob = dsJob.Tables("Job")
                               

                If dtJob.Rows.Count > 0 Then

                    'TO DO : NOTHING
                Else
                    strSQLi = "Insert into tsjob (BRANDNM,PROJECTCAT,JOBGROUPNO,JOBGROUPNM,JOBNO,OLDJOBNO,OLDJOBGROUPNO,OLDJOBGROUPNM," & _
                              "JOBID,JOBNM,CUSTOMERNM,STATUS,DESCRIPTION,COMPANY) " & _
                              "VALUES('" & RTrim(dgRow.Cells(0).Value) & _
                                      "','" & RTrim(dgRow.Cells(2).Value) & "2012" & _
                                      "','" & RTrim(dgRow.Cells(1).Value) & _
                                      "','" & RTrim(dgRow.Cells(2).Value) & _
                                      "','" & RTrim(dgRow.Cells(3).Value) & _
                                      "','" & RTrim(dgRow.Cells(3).Value) & _
                                      "','" & RTrim(dgRow.Cells(1).Value) & _
                                      "','" & RTrim(dgRow.Cells(2).Value) & _
                                      "','" & RTrim(Mid(dgRow.Cells(3).Value, 4)) & _
                                      "','" & RTrim(dgRow.Cells(4).Value) & _
                                      "','" & RTrim(dgRow.Cells(5).Value) & _
                                      "','" & RTrim(dgRow.Cells(6).Value) & _
                                      "','" & "" & _
                                      "','" & "PUBLICIS INDONESIA" & "')"
                    strCmd = New OleDbCommand(strSQLi, MyCon)
                    strCmd.ExecuteNonQuery()
                    strSQLi = ""

                End If
            Next

            Call DisplayJob()
            MyCon.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

    Private Sub DisplayJob()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\MYBLOG\VB\EXCEL2MDB\PM.mdb;Jet OLEDB:Database Password=;"
        Dim MyCon = New OleDbConnection()

        Try
            MyCon.ConnectionString = strConn

            MyCon = New OleDbConnection(strConn)
            If MyCon.State <> ConnectionState.Closed Then MyCon.Close()
            MyCon.Open()

            Dim cmdJob As New OleDbCommand
            Dim daJob As New OleDbDataAdapter
            Dim dsJob As New DataSet
            Dim dtJob As New DataTable

            cmdJob = MyCon.CreateCommand
            cmdJob.CommandText = "SELECT * FROM tsJob"
            daJob.SelectCommand = cmdJob
            daJob.Fill(dsJob, "Job")
            dtJob = dsJob.Tables("Job")

            Me.grdJob.DataSource = dsJob.Tables(0)

            MyCon.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmExcel2mdb_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.grdData.AllowUserToAddRows = False
        Me.grdJob.AllowUserToAddRows = False

        Call DisplayJob()
    End Sub
End Class

Read more »