• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to extract selected data out of a complex .txt file

pda8

New Member
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 output it to Excel. However the data i need is in separate lines, can someone help me? I need to populate all the MSPS1/2/3/4/5/7 data into the excel as shown in the output file.
 

Attachments

Marc L

Excel Ninja
Hi,​
explain at least what is your keyword to search, always the same ? Always matching the end of a text line ?​
Why repeating the same data in column A, weird, really necessary ?​
How any user will run the procedure, what he should do before ? …​
Elaborate all the necessary informations in order any helper has nothing to guess.​
Any chance to ask to Dumb or Dumber - the creator of the text file - to create a better text file format like a CSV for example ?​
More Excel compliant so easier to code, faster execution than the actual format …​
 

pda8

New Member
Hi Marc,
Keyword to search is "Mode V/I Monitor Test" and when this is found, i would to know it's coming from which DPS type (the "MSPS" as there is 6 in total.
Yes, column A is not necessary.
User should select the .txt file and VB should run the process and populate the data on the excel.

The .txt file is generated by a machine and i'm picking the voltage and current reading to gauge on repeatability and reproducibility of the machine.
 

Marc L

Excel Ninja
  • In column A keep only A1 & A2, A2 is the entry for the keyword to search and its validation runs the procedure …

  • Insert an empty column between columns A & B so the results are in columns C to G.

  • You must paste this event procedure to the Sheet1 worksheet module :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim V, R&, W, K$, B As Boolean, T$(4)
        If Target.Address <> "$A$2" Then Exit Sub
        Application.EnableEvents = False
        Me.UsedRange.Columns("C:G").Offset(1).Clear
        If IsEmpty(Target) Then Application.EnableEvents = True: Exit Sub
        V = ThisWorkbook.Path & Application.PathSeparator & "Raw file.txt"
    If Dir(V) = "" Then
        V = Application.GetOpenFilename("Text Files (*.txt), *.txt")
        If V = False Then Application.EnableEvents = True: Exit Sub
    End If
        R = FreeFile
        Open V For Input As #R
        W = Split(Input(LOF(R), #R), vbLf)
        Close #R
        Application.ScreenUpdating = False
        K = "*" & Target
        R = 1
    For Each V In W
        If B Then
               V = Trim(V)
            If V Like "[IV]monitor Test" Then
                T(1) = V
            Else
                   B = V Like "ch#*"
                If B Then
                    R = R + 1
                    V = Split(Application.Trim(Replace(Replace(Replace(V, ": exp=", ""), "/ measure=", ""), " A", "A")))
                    T(2) = V(0)
                    T(3) = V(1)
                    T(4) = V(UBound(V))
                    Rows(R).Columns("C:G") = T
                End If
            End If
        ElseIf V Like K Then
            B = True
            T(0) = V
        End If
    Next
    With Range("C2:G" & Me.UsedRange.Rows.Count).Columns
        .Borders.Weight = xlThin
        .Font.Size = 9
        .Item("A:C").HorizontalAlignment = xlCenter
        .Item("D:E").HorizontalAlignment = xlRight
        .Item("D:E").IndentLevel = 1
    End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

pda8

New Member
Hi Marc,
Not able to run. Can i start the code with user choosing the .txt file as below?

Code:
Sub openfile()
    Dim fn As String
    fn = Application.GetOpenFilename("TExtFiles,*.txt")
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open
        .LoadFromFile fn
        txt = .ReadText
        .Close
    End With
End Sub
Thanks.
 
Last edited by a moderator:

p45cal

Well-Known Member
In the attached is a Power Query offering. I'm using your request as an exercise for myself to learn Power Query with real life examples. This means that my way of doing it is likely inelegant since I don't know the range of commands available in Power Query. That said, there is a solution which appears to work. You can click the button at cell W1 which runs a small macro to get you to pick the file that you want processing. It puts the file's full file path in the Parameters table (cell Z2). It then refreshes the query behind the Output table (cell K1) which looks at cell Z2 to find the file. You don't have to click the button, you can just refresh the query by right-clicking in the Output table and choose Refresh.

It looks for lines in the text file between those containing Mode V/I and those containing Daughter, gets rid of the others then splits and manipulates the remaining rows. This means it expects to find a line containing Mode V/I before a line containing Daughter and they should exisit in pairs in the text file.
 

Attachments

Marc L

Excel Ninja
Not able to run.
As it well works on my side on several computers with different Excel & Windows versions (needs around 2s in the slowest),​
as it's just about to well read the directions and apply them …​
Can i start the code with user choosing the .txt file as below?
As the same already exists in my event procedure !​
Maybe with an explanation and a complete expected result attachment, both smart enough …​
 

pda8

New Member
Hi Marc,
I'm attaching the excel i created using the code, can you check if what i'm doing is correct?
 

Attachments

Last edited by a moderator:

Marc L

Excel Ninja
As the event procedure is located in the worksheet class module as expected​
when I edit the cell A2 - via the F2 key - and valid it - via the Enter key - the event is well fired then as​
the expected file does not exist in the workbook folder an 'Explorer window like' appears in order to select a text file​
and once I validate the file choice the result needs around two seconds to be displayed on my slowest Windows tests computer​
so no issue on my side with your last .xlsm attachment !​
The only weird thing is the sheet layout with the erased headers​
- I just wrote to « insert an empty column between columns A & B so the results are in columns C to G » so with the headers ! -
and the cells under the cell A2 should be blank like the columns B, H to N …​
 

pda8

New Member
I did as what you said and he worked but it didn't prompt me to choose the file. It is because the .txt file at my laptop drive?
 
Last edited by a moderator:

Marc L

Excel Ninja
Maybe as it well works on my side, check the workbook folder …​
Thanks to not quote all the previous post each time as that's just clutter the thread.​
 

pda8

New Member
Marc,
For the Imonitor Test, the extracted value i would want is 1.50A (as highlighted in the image below) but i'm getting it as 10A. what need to be changed at this code?
 

Attachments

Top