29 June 2015

VB6 Ms. Access: Import dari CSV File - Part 2


Artikel ini lanjutan dari Part 1

File CSV dipilih dan ditampilkan menggunakan event Command1_Click.
Private Sub Command1_Click()

CommonDialog1.Filter = "Comma Delimited (*.csv)|*.csv"
CommonDialog1.ShowOpen


Text1.Text = CommonDialog1.FileName

If Me.Text1.Text = "" Then Exit Sub

strFileName = Text1.Text

' mengambil isi file
fnum = FreeFile
Open strFileName For Input As fnum
strFileValue = Input$(LOF(fnum), #fnum)
Close fnum

' memecah baris ke variable lines.
lines = Split(strFileValue, vbCrLf)

' jumlah baris
num_rows = UBound(lines)

'header/nama kolom
one_line = Split(lines(0), Me.Combo1.Text)
num_cols = UBound(one_line)
Data_Temp one_line

' input nilai ke recordset
For r = 1 To num_rows - 1
    rsTemp.AddNew
    one_line = Split(lines(r), Combo1.Text)
    For c = 0 To num_cols
        rsTemp.Fields(c).Value = one_line(c)
    Next
    rsTemp.Update
Next
rsTemp.MoveFirst
Command2.Enabled = True
End Sub


Selanjutnya proses saving ke database dilakukan di event command2_click, untuk memudahkan proses insert kita buat sub procedure tersendiri:
Sub RunQuery(sSQL As String)
Dim cmd As New ADODB.Command
Set cmd = New ADODB.Command
  With cmd
    .ActiveConnection = strConn
    .CommandType = adCmdText
    .CommandText = sSQL
    .Execute
  End With
End Sub

Kemudian sub procedure tersebut di looping di event command2_click
Private Sub Command2_Click()
On Error GoTo errHandler
rsTemp.MoveFirst
For i = 0 To rsTemp.RecordCount - 1
    RunQuery "INSERT INTO category " & _
             "(categorycode, categoryname) VALUES " & _
             "('" & rsTemp.Fields(0).Value & "', " & _
             "'" & rsTemp.Fields(1).Value & "')"
    rsTemp.MoveNext
Next

rsTemp.MoveFirst
MsgBox "Proses Import Selesai"

Load_Data

Exit Sub
errHandler:
MsgBox "Error on Import: " & Err.Description
End Sub

Mari kita coba running.
Klik tombol Browse...

 Pilih file csv untuk import data, kemudian klik Open.
 Isi data CSV akan ditampilkan di datagrid pertama. Klik tombol Begin Import untuk memulai proses import.
 Setelah proses selesai akan muncul message box seperti berikut:
 Data hasil import akan ditampikan pada datagrid kedua.
Kode selengkapnya di part 3 yah....



Click here if you like this article.


0 comments: