Extract Specific Data out of a .txt file by VBA

[Alan Wong]

Hello everyone,

I am newbie in VBA and Marco. I would like to extract specific data from a txt file (log_sample.txt) into an Excel Spreadsheet. There have a lot of data in the txt file (log_sample.txt), and I need to extract 5 data from every data string started with "id":36.

The required output excel file (output.xls) and explanation (ex1.jpg) have been attached.

I really appreciate your help!!!!

(P.S. both of the txt file and output excel file contain Chinese characters, thanks!)

 

[jindon]

This should dp.

Sub test()
    Dim fn As String, txt As String, m As Object, i As Long, ii As Long, a()
    fn = Application.GetOpenFilename("TExtFiles,*.txt")
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open
        .LoadFromFile fn
        txt = .ReadText
        .Close
    End With
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\{""id"":(36.*?),""user_name"":""(.*?)"",""time"":""(.*?)"",""status"":""(.*?)"",""type"":(.*?)\}"
        If .test(txt) Then
            ReDim a(1 To .Execute(txt).Count, 1 To 5)
            For i = 0 To .Execute(txt).Count - 1
                Set m = .Execute(txt)(i).submatches
                For ii = 0 To m.Count - 1
                    a(i + 1, ii + 1) = m(ii)
                Next
            Next
        End If
    End With
    Cells(1).Resize(UBound(a, 1), 5) = a
End Sub

 

[Alan Wong]

This should dp.

Sub test()
    Dim fn As String, txt As String, m As Object, i As Long, ii As Long, a()
    fn = Application.GetOpenFilename("TExtFiles,*.txt")
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open
        .LoadFromFile fn
        txt = .ReadText
        .Close
    End With
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\{""id"":(36.*?),""user_name"":""(.*?)"",""time"":""(.*?)"",""status"":""(.*?)"",""type"":(.*?)\}"
        If .test(txt) Then
            ReDim a(1 To .Execute(txt).Count, 1 To 5)
            For i = 0 To .Execute(txt).Count - 1
                Set m = .Execute(txt)(i).submatches
                For ii = 0 To m.Count - 1
                    a(i + 1, ii + 1) = m(ii)
                Next
            Next
        End If
    End With
    Cells(1).Resize(UBound(a, 1), 5) = a
End Sub

Many thanks! It works great!

 

[pda8]

Hello All,
I've just started picking on VBA and i would like to extract specific data from a .txt file (zip to .rar due to file size is big) and populate it to Excel. However the data i need is in separate lines, can someone help me? i tried to modify from the above shared code but there is an error at UBound. I need to populate all the MSPS1/2/3/4/5/7 data into the excel. […]

 

[Luke M]

@pda8

Please start your own thread for a question, rather than appending (hijacking) and existing thread.