Get Data dari file extensi .log

  1. 6 bulan yang lalu
    Di sunting 6 bulan yang lalu oleh Lexs194

    Bagaimana caranya untuk mengambil data atau mengcopy data dari file yg berektensi .log ke sheet baru dengan tampilan yang rapi tanpa pemisah simbol seperti "titik", "titik koma", "garis" dll.

    berikut saya coba lampirkan filenya dalam data sample saja.

    untuk file excel kira2 seperti dilampiran, ada button input untuk mencari file .log kemudian hasilnya masuk ke sheet2 dgn nama hasillog

    @Lexs194 ...

    ... berarti saya harus cetak dulu file .csv-nya ke sheet tertentu baru bisa menggunakan vlookup ...

    Tidak perlu. Script yang saya berikan di atas adalah contoh bagaimana memanfaatkan fungsi VLookup pada objek WorksheetFunction. Seandainya saat proses meloading data dari file CSV ingin sekaligus dilakukan proses pencarian data, tinggal diterapkan saja script di atas.

    Public Sub PilihFile()
        ...
        sAddress = "B2:C" & Sheet2.Columns(3).Rows(Sheet2.Rows.Count).End(xlUp).Row
        Set xlRange = Sheet2.Range(sAddress)
        ...
        If Len(sFileName) Then
            ...
            If UBound(xFiles) > -1 Then
                With Sheet1
                    For lIdx = 0 To UBound(xFiles)
                        ...
                        Do Until EOF(lFileNum)
                            ...
                            If InStr(1, sBuffer, TEXT_HEADER_A) = 0 Then
                                ...
                                If UBound(xArray) > -1 Then
                                    ...
                                    xValue = Mid$(xArray(8), 15, 6) + 0
                                    .Cells(lRow, 9) = Application.VLookup(xValue, xlRange, 2, 0)
                                    ...
                                End If
                                ...
                            End If
                        Loop
                        Close lFileNum
                    Next
                End With
            End If
        End If    
        ...
    End Sub

    ... kalo saya ambil sekaligus 2file atau lebih masing2 file kan punya header. Nah untuk ambil cuma header pertama saja bagaiman biar header yg lain ngak ikut atau kehitung dalam data nantinya...

    Khan bisa dibuat penanda (Flags) untuk menandai bahwa header sudah dibaca, misalkan:

    ...
    With Sheet1
        bHeaderOk = False
        ...
        Do Until EOF(lFileNum)
            ...
            If InStr(1, sBuffer, TEXT_HEADER_A) = 0 Then
                ...
            ElseIf Not bHeaderOk Then
                '+-- Susun Script untuk memproses header!
                xArray = Split(sBuffer, ",")
                ...
                bHeaderOk = True
            End If
        Loop
        ...
    End With
    ...

    ... misalnya file2 .csv sudah kecetak di sheet "Log" dan mau cetak data dari "Q2" sampai ke bawah dicopy ke sheet "Hasil" di cell ke "C5" bagaimana kayak perulangan yg kemarin? ...

    Khan bisa diseleksi area yang diinginkan kemudian disalin dan ditempelkan pada range tujuan. Misalkan, asumsikan CodeName sheet LOG adalah Sheet1, dan CodeName sheet HASIL adalah Sheet2, maka dapat disusun script seperti berikut:

    ...
    Dim lRow As Long
    ...    
    lRow = Sheet1.Columns(Sheet1.Range("A1").Column).Rows(Sheet1.Rows.Count).End(xlUp).Row
    Sheet1.Range("Q2:Q" & lRow).Copy
    Sheet2.Range("C5").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    ...

    Demikian.

  2. Caton

    14 Mar 2018 Terverifikasi Indonesia + 11.512 Poin

    Terlampir contoh solusi dari saya. Sesuaikan atau modifikasi hal-hal lainnya yang diperlukan. Semoga sesuai... ;)

  3. kalo misalnya file .log sudah punya header dan mau ngambil dari baris ke-2 bagaimana?

    dan kalo dari contoh diatas yg dikasi kan langsung ambil file .log kalo mau cari file .log difolder lain misalnya pilih manual apakah bisa dan apakah bisa multi select dari 2-6 file sekaligus?

  4. oiya, tambahan misalnya saya hanya mau ambil kolom tertentu misalnya kolom B dan kolom D itu bagaimana.
    maaf baru belajar jadi banyak tanya. hehe

  5. Caton

    14 Mar 2018 Terverifikasi Indonesia + 11.512 Poin

    Terlampir contoh solusi dari saya. Silahkan dipelajari dan modifikasikan sesuai kebutuhan.

  6. Di sunting 6 bulan yang lalu oleh Lexs194

    terima kasih om @Caton
    saya coba ambil 2 file sekaligus dan ternyata baris terakhir file pertama itu ketimpa sama file yg kedua jadi ada satu baris data yg hilang.

    kalo yang ambil kolom ini
    misalnya saya ambil kolom ke-2 berarti kolom ke-3, ke-4 dst ikut ya, sesuai berapa kolom yg ada didata.
    gmn cara kalo saya cuma mau ambil kolom ke-2 dan ke-4 saja

  7. filenya

  8. Caton

    15 Mar 2018 Terverifikasi Indonesia + 11.512 Poin

    @Lexs194 ...

    ... saya coba ambil 2 file sekaligus dan ternyata baris terakhir file pertama itu ketimpa sama file yg kedua jadi ada satu baris data yg hilang ...

    Temukan dan ubah script berikut pada prosedur Sub ReadLogFile2:

    ...
    With Sheet3
        For lIdx = 0 To UBound(xFiles)
            lRow = .Columns(1).Rows(.Rows.Count).End(xlUp).Row
            If lRow = 1 Then lRow = lRow + 1
                        
            lFileNum = FreeFile
            ...
        Next
    End With
    ...

    menjadi:

    ...
    With Sheet3
        For lIdx = 0 To UBound(xFiles)
            lRow = .Columns(1).Rows(.Rows.Count).End(xlUp).Row + 1
                        
            lFileNum = FreeFile
            ...
        Next
    End With
    ...

    ... gmn cara kalo saya cuma mau ambil kolom ke-2 dan ke-4 saja ...

    Pada prosedur yang sama dengan yang di atas, temukan dan ubah script berikut:

    ...
    On Error Resume Next
    If UBound(xArray) > -1 Then
        ReDim xTemp(UBound(xArray) - 1)
        For lX = 1 To UBound(xArray)
            xTemp(lX - 1) = xArray(lX)
        Next
        .Range(.Cells(lRow, 1), .Cells(lRow, UBound(xTemp) + 1)) = xTemp
        Set xArray = Nothing
        Set xTemp = Nothing
        lRow = lRow + 1
    End If
    Err.Clear
    On Error GoTo 0
    ...

    menjadi:

    ...
    On Error Resume Next
    If UBound(xArray) > -1 Then
        xTemp = Array(xArray(1), xArray(3))
        .Range(.Cells(lRow, 1), .Cells(lRow, UBound(xTemp) + 1)) = xTemp
        Set xArray = Nothing
        Set xTemp = Nothing
        lRow = lRow + 1
    End If
    Err.Clear
    On Error GoTo 0
    ...

    Demikian yang bisa @Lexs194 coba perbaiki.

  9. sip sudah saya perbaiki seperti diatas.
    oiya kalo file yg ektensinya .csv apakah caranya sama Om @Caton

    saya punya file .csv dgn data baris yg banyak bisa ribuan baris dan punya header yg cukup banyak pula apakah bisa cara diatas diterapkan?

  10. Caton

    16 Mar 2018 Terverifikasi Indonesia + 11.512 Poin

    @Lexs194 ...

    ... kalo file yg ektensinya .csv apakah caranya sama ...

    Sebenarnya, apapun ekstensi filenya tidaklah penting. Selama isi atau teks pada file data berupa Separated Text Formatting, yakni teks per barisnya dipisahkan dengan karakter khusus dan setiap baris diakhiri dengan tanda Carriage Return/Line Feed (CR/LF atau Enter), maka script pada file terlampir dapat digunakan dengan syarat pemisah antar teks ditentukan dengan tepat (misalkan koma, titik koma, pipe character dan sebagainya).

    Dalam script yang digunakan pada file terlampir di atas, bagian script:

    ...
    If InStr(1, sBuffer, "|") Then
        xArray = Split(sBuffer, "|")
    ElseIf InStr(1, sBuffer, ";") Then
        xArray = Split(sBuffer, ";")
    End If
    ...

    sebenarnya tidaklah penting, jika karakter pemisah (separator) diketahui dengan pasti. Saya buatkan pemeriksaan kondisi seperti di atas agar script VBA-nya dapat digunakan untuk memproses beberapa opsi Separated Text Formatting yang sering dijumpai, misalkan koma, titik koma atau pipe character. Seandainya tanda pemisah antar Text Field sudah dapat dipastikan (misalkan file data menggunakan karakter koma sebagai pemisah antar Text Field-nya), maka script tersebut dapat disederhanakan menjadi:

    ...
    xArray = Split(sBuffer, ",")
    ...

    Silahkan ditentukan mana yang lebih sesuai. Demikian juga untuk pemilihan Text Field-nya dapat menggunakan FOR ... NEXT, fungsi ARRAY atau penetapan langsung (Direct Assign). Misalkan untuk Direct Assign:

    ...
    '+-- Deklarasi Array.
    Dim xTemp()
    ...
    '+-- Redimensi Array.
    Redim xTemp(2)
    
    xTemp(0) = xArray(1)
    xTemp(1) = xArray(3)
    xTemp(2) = xArray(5)
    ...

    dimana kode di atas sama saja dengan:

    ...
    '+-- Deklarasi Array.
    Dim xTemp
    ...
    '+-- Mengisi Array.
    xTemp = Array(xArray(1), xArray(3), xArray(5))
    ...

    Tips. Jika jumlah baris data yang akan diproses sangat banyak, coba modifikasi baris pada prosedur Sub ReadLogFile2 dengan menambahkan script Application.ScreenUpdating = False menjadi:

    Public Sub ReadLogFile2()
        Dim lFileNum As Long, lRow As Long, lIdx As Long, lX As Long
        Dim sBuffer As String, sFileName As String
        Dim xFiles, xArray, xTemp
        
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        sFileName = OpenFiles
    
        ...
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    
    End Sub
    

    Dengan menambahkan script Application.ScreenUpdating = False dapat mempercepat proses eksekusi script oleh karena proses penulisan hasil ke Worksheet ditunda sampai script Application.ScreenUpdating = True dieksekusi.

    Demikian, semoga bermanfaat.

  11. Di sunting 6 bulan yang lalu oleh Lexs194

    mantap tipsnya

    saya lagi coba dgn buka 2file .csv dan kena error bisa dibantiu om @Caton

    oiya script untuk cetak data ke cell tertentu gmn?
    misalnya saya mau cetak data array ke-0 ditaruh ke cell C5. atau cetak data array ke-1 ditaruh ke E5

    sekalian om sama caranya buat index nomor otomatis sesuai jumlah data yang akan dicetak.

  12. Caton

    17 Mar 2018 Terverifikasi Indonesia + 11.512 Poin

    @Lexs194 ....

    ... saya lagi coba dgn buka 2file .csv dan kena error ...

    Pesan kesalahannya apa? Coba lampirkan contoh file CSV-nya.

    ... misalnya saya mau cetak data array ke-0 ditaruh ke cell C5. atau cetak data array ke-1 ditaruh ke E5 ...

    Langsung saja isi variabel xArray ditulis ke sel yang diinginkan, misalnya:

    ...
    Sheet1.Range("C5") = xArray(0)
    ...

    atau jika dibuat di dalam blok WITH ... END WITH:

    ...
    With Sheet1
        .Range("C5") = xArray(0)
    End With
    ...

    atau jika Sheet1 sudah diaktifkan dengan perintah Sheet1.Select, maka:

    ...
    Sheet1.Select
    ...
    [C5] = xArray(0)
    ...

    atau jika sifatnya dinamis, bisa juga diisi langsung melalui properti CELLS, misalkan:

    ...
    With Sheet1
       ...
       .Cells(lRow, 3) = xArray(0)
       .Cells(lRow, 5) = xArray(1)
       ...
    End With

    ... caranya buat index nomor otomatis sesuai jumlah data yang akan dicetak ...

    Buat variabel untuk menampung indeks nomor baris, misalnya:

    ...
    Dim lRowIndex As Long
    ...
    lRowIndex = 1
    If UBound(xFiles) > -1 Then
        ...
        On Error Resume Next
        If UBound(xArray) > -1 Then
            ...
            .Cells(lRow, 1) = lRowIndex
            .Cells(lRow, 2) = xArray(16)
            .Cells(lRow, 3) = xArray(7)
            .Cells(lRow, 4) = xArray(4)
            ...
            lRowIndex = lRowIndex +1
        End If 
        ...
    End If 
    ...

    Demikian.

  13. ini om saya lampirkan file .csv-Nya sama alarm errornya

  14. Di sunting 6 bulan yang lalu oleh Lexs194

    kalo nilai yg masih dalam array apa bisa dieksekusi seperti ini?
    soalnya nilainya beda hehe

  15. Caton

    17 Mar 2018 Terverifikasi Indonesia + 11.512 Poin
    Di sunting 6 bulan yang lalu oleh Caton

    @Lexs194 ...

    Kesalahan yang terjadi sebagaimana gambar berikut:

    -image-

    terjadi karena script ini:

    ...
    xFiles = Split(sFileName, ",")
    ...

    dimana script tersebut seharusnya adalah:

    ...
    xFiles = Split(sFileName, "|")
    ...

    Variabel xFiles pada tersebut adalah array untuk nama files yang akan diproses, bukan data yang akan diproses. Variabel xFiles sendiri merupakan hasil dari prosedur Function OpenFiles yang kemudian dipisahkan menggunakan fungsi SPLIT.

    ... kalo nilai yg masih dalam array apa bisa dieksekusi ...

    Pada dasarnya bisa jika memang nilainya dapat disesuaikan. Namun, sebelumnya untuk script berikut:

    ...
    .Cells(lRow, 3) = xTemp(0)
    .Cells(lRow, 4) = xTemp(1)
    .Cells(lRow, 5) = xTemp(2)
    ...

    sebenarnya sudah tidak diperlukan karena isi setiap sel bisa langsung diisi dari variabel xArray. Jadi coba modifikasi kembali scriptnya menjadi seperti berikut:

    ...
    On Error Resume Next
    If UBound(xArray) > -1 Then
        .Cells(lRow, 1) = lRowIndex
        .Cells(lRow, 3) = xArray(16)
        .Cells(lRow, 4) = xArray(7)
        .Cells(lRow, 5) = xArray(4)
        .Cells(lRow, 6) = timeShift
        .Cells(lRow, 7) = timeShift - DateValue(xArray(4))
        Set xArray = Nothing
        lRowIndex = lRowIndex + 1
        lRow = lRow + 1
    End If
    Err.Clear
    On Error GoTo 0
    ...

    Demikian.

  16. ok sudah saya ganti lagi jadi "|", saya kira itu buat delimated-nya haha.

    buat yg kemarin saya masih bingung di

    .Cells(lRow, 7) = timeShift - DateValue(xArray(4))


    ngak sama ya hasilnya dngn =day(F5-E5) saya coba ganti codenya jadi

    .Cells(lRow, 7) = Day(timeShift - DateValue(xArray(4)))


    ngak sama juga hasilnya apa perlu diubah dulu ya ke format waktu dan tanggal misalnya (mm/dd/yy h:mm:ss)

    sama saya mau nyari hasil durasinya juga dalam bentuk jam bisa dibantu sekalian om @Caton

    untuk rumus vlookup jadi macronya gmn ya kalo secara rumus excel saya dapat hasil

    =VLOOKUP(MID('log'!I2,15,6)+0,'BSC_RNC'!B:C,2,0)


    disini saya ambil primary key ditengah dgn jumlah karakter 6 huruf
    dimana "log" adalah file .csv yang saya buka dan "I2" merupakaan array ke-8, BSC_RNC target tabel

  17. Caton

    18 Mar 2018 Terverifikasi Indonesia + 11.512 Poin

    @Lexs194 ...

    Modifikasi script sebelumnya menjadi:

    ...
    On Error Resume Next
    If UBound(xArray) > -1 Then
        .Cells(lRow, 1) = lRowIndex
        .Cells(lRow, 3) = xArray(16)
        .Cells(lRow, 4) = xArray(7)
        .Cells(lRow, 5) = xArray(4)
        .Cells(lRow, 6) = TimeShift
        .Cells(lRow, 7) = Fix(TimeShift - CDate(xArray(4)))
        .Cells(lRow, 8) = Abs((TimeShift - CDate(xArray(4))) - Fix(TimeShift - CDate(xArray(4))))
        Set xArray = Nothing
        lRowIndex = lRowIndex + 1
        lRow = lRow + 1
    End If
    Err.Clear
    On Error GoTo 0
    ...

    ... untuk rumus vlookup jadi macronya gmn ...

    Excel VBA punya objek WorksheetFunction. Dan salah satu membernya adalah fungsi VLookup. Sintaksnya:

    WorksheetFunction.VLookup(Arg1, Arg2, Arg3, [Arg4])

    Argumen Arg1 merupakan nilai yang akan dicari, argumen Arg2 merupakan tabel pencarian, argumen Arg3 merupakan kolom pengambilan nilai, dan argumen Arg3 merupakan ketentuan pencarian secara eksak atau bukan. Lalu bagaimana implementasi formula:

    =VLOOKUP(MID('log'!I2,15,6)+0,'BSC_RNC'!B:C,2,0)

    ke dalam script VBA? Pertama, untuk argumen Lookup Value pada formula di atas sebaiknya dipisahkan ke dalam proses berbeda (khusus), dikarenakan nilainya diambil dari Worksheet berbeda. Kedua, untuk argumen Table Array sebaiknya menggunakan area terbatas (area yang ditentukan melalui notasi 'BSC_RNC'!B:C pada formula di atas terlalu luas, padahal tidak seluruh baris berisi data).

    Sebagai contoh, asumsikan data dari file CSV diimpor ke salah satu Worksheet (misalkan sheet LOG, CodeName = Sheet1), dan tabel pencarian ada pada sheet BSC_RNC (CodeName = Sheet2) dan hasil akan disusun pada sheet HASIL (CodeName = Sheet3). Maka dapat dibuatkan sebuah prosedur untuk menguji script yang diinginkan seperti berikut:

    Sub Test()
        Dim xValue  As Variant
        Dim sAddress As String
        Dim xlRange As Range
        Dim lIdx As Long
        
        sAddress = "B2:C" & Sheet2.Columns(3).Rows(Sheet2.Rows.Count).End(xlUp).Row
        Set xlRange = Sheet2.Range(sAddress)
        For lIdx = 2 To 10
            xValue = Mid$(Sheet1.Range("I" & lIdx), 15, 6) + 0
            Sheet3.Cells(lIdx - 1, 1) = WorksheetFunction.VLookup(xValue, xlRange, 2, 0)
        Next    
    End Sub

    Selain menggunakan fungsi WorksheetFunction.VLookup, juga bisa menggunakan fungsi Application.VLookup. Dengan fungsi WorksheetFunction.VLookup, apabila terjadi kesalahan (misalkan nilai tidak ditemukan) dan tidak ada script untuk menangkap kesalahan, maka VBA akan menampilkan pesan kesalahan seperti berikut:

    Shoot068.png

    sedangkan jika menggunakan fungsi Application.VLookup, hasilnya dapat berupa kesalahan #N/A. Demikian yang dapat saya jelaskan.

  18. Berarti saya harus cetak dulu file .csv-nya ke sheet tertentu baru bisa menggunakan vlookup seperti diatas ya om @Caton
    Oiya kalo saya ambil sekaligus 2file atau lebih masing2 file kan punya header. Nah untuk ambil cuma header pertama saja bagaiman biar header yg lain ngak ikut atau kehitung dalam data nantinya.

  19. saya lagi bingung om coba saya ulang dari awal. haha
    misalnya file2 .csv sudah kecetak di sheet "Log" dan mau cetak data dari "Q2" sampai ke bawah dicopy ke sheet "Hasil" di cell ke "C5" bagaimana kayak perulangan yg kemarin?

  20. Caton

    18 Mar 2018 Terverifikasi Jawaban Terpilih Indonesia + 11.512 Poin

    @Lexs194 ...

    ... berarti saya harus cetak dulu file .csv-nya ke sheet tertentu baru bisa menggunakan vlookup ...

    Tidak perlu. Script yang saya berikan di atas adalah contoh bagaimana memanfaatkan fungsi VLookup pada objek WorksheetFunction. Seandainya saat proses meloading data dari file CSV ingin sekaligus dilakukan proses pencarian data, tinggal diterapkan saja script di atas.

    Public Sub PilihFile()
        ...
        sAddress = "B2:C" & Sheet2.Columns(3).Rows(Sheet2.Rows.Count).End(xlUp).Row
        Set xlRange = Sheet2.Range(sAddress)
        ...
        If Len(sFileName) Then
            ...
            If UBound(xFiles) > -1 Then
                With Sheet1
                    For lIdx = 0 To UBound(xFiles)
                        ...
                        Do Until EOF(lFileNum)
                            ...
                            If InStr(1, sBuffer, TEXT_HEADER_A) = 0 Then
                                ...
                                If UBound(xArray) > -1 Then
                                    ...
                                    xValue = Mid$(xArray(8), 15, 6) + 0
                                    .Cells(lRow, 9) = Application.VLookup(xValue, xlRange, 2, 0)
                                    ...
                                End If
                                ...
                            End If
                        Loop
                        Close lFileNum
                    Next
                End With
            End If
        End If    
        ...
    End Sub

    ... kalo saya ambil sekaligus 2file atau lebih masing2 file kan punya header. Nah untuk ambil cuma header pertama saja bagaiman biar header yg lain ngak ikut atau kehitung dalam data nantinya...

    Khan bisa dibuat penanda (Flags) untuk menandai bahwa header sudah dibaca, misalkan:

    ...
    With Sheet1
        bHeaderOk = False
        ...
        Do Until EOF(lFileNum)
            ...
            If InStr(1, sBuffer, TEXT_HEADER_A) = 0 Then
                ...
            ElseIf Not bHeaderOk Then
                '+-- Susun Script untuk memproses header!
                xArray = Split(sBuffer, ",")
                ...
                bHeaderOk = True
            End If
        Loop
        ...
    End With
    ...

    ... misalnya file2 .csv sudah kecetak di sheet "Log" dan mau cetak data dari "Q2" sampai ke bawah dicopy ke sheet "Hasil" di cell ke "C5" bagaimana kayak perulangan yg kemarin? ...

    Khan bisa diseleksi area yang diinginkan kemudian disalin dan ditempelkan pada range tujuan. Misalkan, asumsikan CodeName sheet LOG adalah Sheet1, dan CodeName sheet HASIL adalah Sheet2, maka dapat disusun script seperti berikut:

    ...
    Dim lRow As Long
    ...    
    lRow = Sheet1.Columns(Sheet1.Range("A1").Column).Rows(Sheet1.Rows.Count).End(xlUp).Row
    Sheet1.Range("Q2:Q" & lRow).Copy
    Sheet2.Range("C5").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    ...

    Demikian.

  21. Newer ›
 

atau Mendaftar untuk ikut berdiskusi!