• 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.

VBA to extract specific data from text file

rlv01 said:
FWIW, if I remove Option Explicit, when I run your code unmodified, I get the same error @Dash666 got in post #15. When I changed all instances of UsedRange to ActiveSheet.UsedRange , it worked.
Click to expand...
As Option Explicit is very not a concern here so you obviously felt into the bad reader trap ‼​
As it works whatever the Excel / Windows versions if only the VBA procedure is located where it must be as written in post #14 !​
So for good enough readers only …​
​
 
@Marc L

You are a bad reader yourself and did not pay attention to the problem statement! I clearly noted and even colored what are the search terms and what are the results associated with those search terms in post #1 and #3

Your code is correctly looking only at the first search term which is "BATCH_RECIPE NAME"

But does not look for the correct search parameters after that.

2nd search term is "O_AGIT_ACTIVATE"

3rd search term is "O_AGIT_MIX_TM"

Marc L said:
W = Evaluate("""FORMULA_PARAMETER NAME=""""""&{""" & _
Join(Application.Index(UsedRange, 1, [{1,2,3}]), """,""") & """}&"""""" TYPE=""")
UsedRange.Offset(1).Clear
​
Click to expand...

Intent is to be able to have a dynamic search terms and I would be able to replace it with other text to extract similar info from other files.
 
You badly misread - or not able to well undertand - my code as it does exactly what you exposed in your initial post ! :rolleyes:​
With the same result as your post #12 so well read post #14 for how it 'dynamically' works, you should understand …​
Even if you did not explain it clearly as it must be in your initial post !​
If you are not able to follow this VBA procedure then you must quit VBA and go with Power Query (Get & Transform).​
​
 
You're correct that I don't know VBA and don't understand it, that's why I came here for help.

Does your code look at the search terms that was stated?? The code does not contain 2nd and 3rd search terms.

Only the following terms are in the code:

"FORMULA_PARAMETER NAME="
"TYPE="

So it may get a result, but that is not what was stated in the original post.
Result should be specifically based on the search terms specified and not detecting the "TYPE="
 
Dash666 said:
Does your code look at the search terms that was stated??
Click to expand...
Obviously yes as - again - yet explained in post #14, a must read ‼​
​
Dash666 said:
The code does not contain 2nd and 3rd search terms.
Click to expand...
Neither the first if only you well read it ‼​
As for a 'dynamic search' like you lately explained only in post #27 instead of the initial post​
so obviously nothing must be hardcoded !​
See at least what the variable W contains …​
​
But maybe you are confusing 'dynamic' and a 'static search' where you can see the hardcoded criterion within the code​
so if it's the case for such confusion I can hardcode the criterion for such static search …​

Dash666 said:
Only the following terms are in the code:
"FORMULA_PARAMETER NAME="
"TYPE="
Click to expand...
That's clear : you not undertand VBA code neither Excel basics, like you must study the text file structure …​
​
 
Obviously did not! What is the point of search terms that was stated in the post #1 if you're not using them in thecode?

This is similar to what a lookup table does. Look for the search terms that are colored in post #1 and return the value associated with them. You wrote a code that does something different are insisting that it is correct.
 
Last edited by a moderator:
Dash666 said:
Only the following terms are in the code:

"FORMULA_PARAMETER NAME="
"TYPE="
Click to expand...
For good enough reader only :​
this is the static part only, the dynamic part is between if only you well read the code and as explained again in post #14 !​
​
 
Marc L said:
How could it be different as it produces exactly the expected result ?‼ :rolleyes:​
​
Click to expand...
If you read post #1 you will see what what was problem statement.

Dash666 said:
I want specify what I'm looking for (it is in Blue and then it gives me the results that are in Green.

BATCH_RECIPE NAME="OP_ALUM_BH_SUPPLY" TYPE=OPERATION CATEGORY="Recipes/Operations/Alum_Skid"

FORMULA_PARAMETER NAME="O_AGIT_ACTIVATE" TYPE=ENUMERATION_VALUE

FORMULA_PARAMETER NAME="O_AGIT_MIX_TM" TYPE=BATCH_PARAMETER_INTEGER
Click to expand...
 
Marc L said:
Obviously yes as - again - yet explained in post #14, a must read ‼​
​

Neither the first if only you well read it ‼​
As for a 'dynamic search' like you lately explained only in post #27 instead of the initial post​
so obviously nothing must be hardcoded !​
See at least what the variable W contains …​
​
But maybe you are confusing 'dynamic' and a 'static search' where you can see the hardcoded criterion within the code​
so if it's the case for such confusion I can hardcode the criterion for such static search …​


That's clear : you not undertand VBA code neither Excel basics, like you must study the text file structure …​
​
Click to expand...
would you revise your code to static search?
 
LoL you was confusing 'dynamic' and 'static' ! :DD​
​
Why wasting any second as you wrote my state-of-the-art post #14 demonstration does not work on your side ?‼​
As if again you are not able to well follow the easy directions explained so my new demonstration won't work neither on your side.​
At least just well read & follow my post #22 proposal …​

Then once my 'dynamic' post #14 works on your side I could write a kid 'static' beginner level slower demonstration​
like any beginner can achieve just replacing the 'dynamic' criterion with such 'static' …​
​
 
p45cal said:
In the attached, a Power Query solution (work in progress) at table at cell A10. Right-click this table and choose Refresh to update the query.
It reads the Source.txt file directly and expects to see it at C:\Users\Public\Document\Source.txt, obviously it won't be the same location or name on your system, so this will need editing:
The first step of the Source query shows the wrong location:

View attachment 83430

so click on Edit Settings and browse to the correct file:

View attachment 83431

then Close & Load:

View attachment 83432

Note that this is work in progress because
1. in the text file the entry for:
OP_ALUM_BH_SUPPLYB
the MIX part reads:
FORMULA_PARAMETER NAME="O_AGIT_MIX_TMB" TYPE=BATCH_PARAMETER_INTEGERB
where the red B is extra and I don't know if it should be counted.

2. the choice of which file to process needs to be streamlined.
Click to expand...
This actually list all the cells in column A! which is grate. but the other columns are empty for some reason. I think it is because of the filters you have in the query?

To answer your question , if it doesn't match it then it should be a blank cell. there are different text that are similar but slightly different.

Thanks! This is promising!
 
Last edited:
Dash666 said:
I just can't upload files at work.... but here is what you were asking me in #2
Click to expand...

Using your source file, this should match what you have in results.xlsx - with the exception that for recipe OP_ALUM_BH_SUPPLYB it returns no value for O_AGIT_MIX_TM because that formula parameter name does not exist in that recipe (only O_AGIT_MIX_TMX)


Code: 
Sub LoadRecipeFile()
    Dim ColIndx As Long, I As Long, RowIndx As Long
    Dim BatchMarker As String, FormulaList As String, FormulaMarker As String, FormulaType As String
    Dim Infile As String, Line As String, PrmName As String, RecipeName As String, TextLine As String
    Dim X As Variant
    Dim WB As Workbook
    Dim WS As Worksheet

    BatchMarker = "BATCH_RECIPE NAME="
    FormulaMarker = "FORMULA_PARAMETER NAME="
    FormulaList = "O_AGIT_ACTIVATE$O_AGIT_MIX_TM$"

    Set WB = ActiveWorkbook

    X = Application.GetOpenFilename("Text files,*.txt")

    If X = "False" Then Exit Sub
    Infile = X

    WB.Sheets.Add After:=Worksheets(Worksheets.Count)
    Set WS = ActiveSheet

    With WS
        'Header row
        .Cells(1, 1).Value = Left(BatchMarker, Len(BatchMarker) - 1)
        For I = 0 To UBound(Split(FormulaList, "$"))
            .Cells(1, I + 2) = Split(FormulaList, "$")(I)
        Next I
        Range("A1:C1").Font.Bold = True
        With Range("A1:C1").Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
        WS.Columns.AutoFit
    End With

    Open Infile For Input Access Read As #1           ' Open text file for read only.
    RowIndx = 1
    ColIndx = 0
    Do While Not EOF(1)                               ' Loop until end of file.
        Line Input #1, TextLine                       ' Read line into variable.
        Line = Trim(TextLine)

        If InStr(Line, BatchMarker) > 0 Then
            RowIndx = RowIndx + 1
            ColIndx = 0
            RecipeName = Split(Line, Chr(34))(1)
        End If

        If InStr(Line, FormulaMarker) = 1 Then
            PrmName = Split(Line, Chr(34))(1) & "$"
            If InStr(FormulaList, PrmName) > 0 Then
                If ColIndx = 0 Then
                    ColIndx = ColIndx + 1
                    Cells(RowIndx, ColIndx) = RecipeName
                End If
                ColIndx = ColIndx + 1
                FormulaType = Split(Line, "=")(UBound(Split(Line, "=")))

                If Left(PrmName, Len(PrmName) - 1) <> Cells(1, ColIndx) Then
                    ColIndx = ColIndx + 1
                End If
                Cells(RowIndx, ColIndx) = FormulaType
            Else
                Cells(RowIndx, 1) = RecipeName
            End If
        End If
    Loop
    Close #1                                          ' Close file.
    WS.Columns.AutoFit
End Sub
 
rlv01 said:
Using your source file, this should match what you have in results.xlsx - with the exception that for recipe OP_ALUM_BH_SUPPLYB it returns no value for O_AGIT_MIX_TM because that formula parameter name does not exist in that recipe (only O_AGIT_MIX_TMX)


Code: 
Sub LoadRecipeFile()
    Dim ColIndx As Long, I As Long, RowIndx As Long
    Dim BatchMarker As String, FormulaList As String, FormulaMarker As String, FormulaType As String
    Dim Infile As String, Line As String, PrmName As String, RecipeName As String, TextLine As String
    Dim X As Variant
    Dim WB As Workbook
    Dim WS As Worksheet

    BatchMarker = "BATCH_RECIPE NAME="
    FormulaMarker = "FORMULA_PARAMETER NAME="
    FormulaList = "O_AGIT_ACTIVATE$O_AGIT_MIX_TM$"

    Set WB = ActiveWorkbook

    X = Application.GetOpenFilename("Text files,*.txt")

    If X = "False" Then Exit Sub
    Infile = X

    WB.Sheets.Add After:=Worksheets(Worksheets.Count)
    Set WS = ActiveSheet

    With WS
        'Header row
        .Cells(1, 1).Value = Left(BatchMarker, Len(BatchMarker) - 1)
        For I = 0 To UBound(Split(FormulaList, "$"))
            .Cells(1, I + 2) = Split(FormulaList, "$")(I)
        Next I
        Range("A1:C1").Font.Bold = True
        With Range("A1:C1").Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
        WS.Columns.AutoFit
    End With

    Open Infile For Input Access Read As #1           ' Open text file for read only.
    RowIndx = 1
    ColIndx = 0
    Do While Not EOF(1)                               ' Loop until end of file.
        Line Input #1, TextLine                       ' Read line into variable.
        Line = Trim(TextLine)

        If InStr(Line, BatchMarker) > 0 Then
            RowIndx = RowIndx + 1
            ColIndx = 0
            RecipeName = Split(Line, Chr(34))(1)
        End If

        If InStr(Line, FormulaMarker) = 1 Then
            PrmName = Split(Line, Chr(34))(1) & "$"
            If InStr(FormulaList, PrmName) > 0 Then
                If ColIndx = 0 Then
                    ColIndx = ColIndx + 1
                    Cells(RowIndx, ColIndx) = RecipeName
                End If
                ColIndx = ColIndx + 1
                FormulaType = Split(Line, "=")(UBound(Split(Line, "=")))

                If Left(PrmName, Len(PrmName) - 1) <> Cells(1, ColIndx) Then
                    ColIndx = ColIndx + 1
                End If
                Cells(RowIndx, ColIndx) = FormulaType
            Else
                Cells(RowIndx, 1) = RecipeName
            End If
        End If
    Loop
    Close #1                                          ' Close file.
    WS.Columns.AutoFit
End Sub
Click to expand...

This works beautifully! Thank you!!
 
You must log in or register to reply here.
Back
Top