Kamis, 03 September 2020

Vb6 Ms. Access: Import Dari Csv File - Part 2

Diterbitkan September 03, 2020

*.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 dikerjakan di event command2_click, untuk mempermudah 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, lalu klik Open.

Isi data CSV akan ditampilkan di datagrid pertama. Klik tombol Begin Import untuk memulai proses import.

Setelah proses final akan muncul message box mirip berikut:

Data hasil import akan ditampikan pada datagrid kedua.

Kode selengkapnya di part 3 yah....

Click here if you like this article.

Sumber http://rani-irsan.blogspot.com