• 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

D

Dash666

New Member
Can someone help me on this please?

I have exported a control system data into a text file. now I have this file with huge blocks of data. they have similar structure, but inside the block some have more data lines (the start and end is similar). I want specify what I'm looking for (it is in Blue and then it gives me the results that are in Green.
next block of data may not have the 2nd blue parameter I'm looking for so it should skip it and not list it.

1st Green would be in cell A1 and then 2nd Green would be in B1, C1, D1, etc.

and then the it goes to the next block.


BATCH_RECIPE NAME="OP_ALUM_BH_SUPPLY" TYPE=OPERATION CATEGORY="Recipes/Operations/Alum_Skid"
user="HATHOMA2" time=1677106708/* "22-Feb-2023 17:58:28" */
{
DESCRIPTION="Alum Buffer Hold Tank Supply"
USE_EQUIPMENT_TRAINS=F
EQUIPMENT_UNIT_CLASS="ALUM_BUFF_HOLD_UC"
ENFORCED_FORMULA_SELECTION_ENABLED=F
DEFAULT_SELECTED_FORMULA=""
PARAMETERS_LOCKED_BY_DEFAULT=F
ABSTRACT="Alum Buffer Hold Tank Supply operation"
BATCH_UNITS="gal"
BATCH_LENGTH="1"
DEFAULT_BATCH_SIZE=1
MINIMUM_BATCH_SIZE=1
MAXIMUM_BATCH_SIZE=10
PRODUCT_CODE="ALUM"
PRODUCT_NAME="Alum Buffer Hold"
FORMULA_PARAMETER NAME="O_OUT_RES01" TYPE=UNICODE_STRING
{
CONNECTION=INPUT
RECTANGLE= { X=-50 Y=-50 H=1 W=1 }
IS_PARAMETER_LOCKED=F
}
FORMULA_PARAMETER NAME="O_AGIT_ACTIVATE" TYPE=ENUMERATION_VALUE
{
CONNECTION=INPUT
RECTANGLE= { X=-50 Y=-50 H=1 W=1 }
IS_PARAMETER_LOCKED=F
}
FORMULA_PARAMETER NAME="O_AGIT_FINAL_ST" TYPE=ENUMERATION_VALUE
{
CONNECTION=INPUT
RECTANGLE= { X=-50 Y=-50 H=1 W=1 }
IS_PARAMETER_LOCKED=F
}
FORMULA_PARAMETER NAME="O_AGIT_MIX_TM" TYPE=BATCH_PARAMETER_INTEGER
{
CONNECTION=INPUT
RECTANGLE= { X=-50 Y=-50 H=1 W=1 }
IS_PARAMETER_LOCKED=F
}
 
Dash666 said:
Can someone help me on this please?
Click to expand...
You can use the VBA basics like text functions as explained in VBA help.​
For more help, attach at least a source text file and accordingly its exact expected result workbook …​
​
 
Marc L said:
You can use the VBA basics like text functions as explained in VBA help.​
For more help, attach at least a source text file and accordingly its exact expected result workbook …​
​
Click to expand...
Hello Marc! thanks for responding :)

I have a section of a block of text from that file at the end of my original post. There are more than 400 of those right after each other in that text file.
I would like the VBA to search the text file based on the highlighted text in blue and return the associated value (in green) and put them in a row in excel.
 
Can't upload file as it is work related and has company data. but I posted the source code! it doesn't show up for you?
 
Dash666 said:
Can't upload file
Click to expand...
So it's a « no arm, no chocolate » situation !​
​
Dash666 said:
but I posted the source code! it doesn't show up for you?
Click to expand...
Source code ?‼ There is no code but some text and obviously as it's half of the request​
so without the mandatory expected I won't waste time for any guessing challenge, too many questions !​
As a reminder creating a sample text file well reflecting a real source text file is at kid level​
like accordingly building the exact expected result workbook.​
As so many samples on web how to read a text file under VBA …​
But maybe another helper - a mind reader with VBA skills or a VBA coder with mind reader abilities ? - may give it a try.​
​
 
I just can't upload files at work.... but here is what you were asking me in #2
 

Attachments

  • Source.txt
    6.9 KB · Views: 9
  • Results.xlsx
    5 KB · Views: 10
Last edited by a moderator:
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:

83430

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

83431

then Close & Load:

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.
 

Attachments

  • Chandoo51826Results.xlsx
    17.1 KB · Views: 2
According to the attachment a starter VBA demonstration to paste only to the result Sheet1 worksheet module​
where its first row is used as the headers row keys to extract the data from a text file :​
Code: 
Sub Demo1()
    Dim V, C%, W, T$(), R&, S$()
        V = Application.GetOpenFilename("Text files,*.txt"):  If V = False Then Exit Sub
        C = FreeFile
        W = Evaluate("""FORMULA_PARAMETER NAME=""""""&{""" & _
                    Join(Application.Index(UsedRange, 1, [{1,2,3}]), """,""") & """}&"""""" TYPE=""")
        UsedRange.Offset(1).Clear
        Open V For Binary As #C
        V = Split(Input(LOF(C), #C), [A1] & "=""")
        Close #C
        If UBound(V) < 1 Then Beep: Exit Sub
        ReDim T(1 To UBound(V), 1 To 3)
    For R = 1 To UBound(V)
        T(R, 1) = Split(V(R), """")(0)
    For C = 2 To 3
        S = Split(V(R), W(C))
        If UBound(S) > 0 Then T(R, C) = Split(S(1), vbCrLf)(0)
    Next C, R
        [A2:C2].Resize(R - 1) = T
        UsedRange.Columns.AutoFit
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
Appreciate your time! I get this error sadly.
 

Attachments

  • Error.JPG
    Error.JPG
    87.9 KB · Views: 6
Last edited by a moderator:
As it well works on my side with your attachment so the bad is on yours !​
As it depends on which codeline raises the error but first did you try at least with exactly the same attachment ?​
​
 
I'm using 365
it I used debug and it gives me that error right after it opens the text file.


would you also explain your code please?

I see you have listed "FORMULA_PARAMETER NAME=" so your code is looking for that, but I don't see the other parameter being listed. so how does it know what to look for and what to return?
 
Last edited by a moderator:
Pasted the code in the excel sheet I uploaded here. I don't get the error anymore :), but there is no result, just header:(
 
Well maybe if you explained it differently for someone who clearly has no experience with VBA and is asking for help/clarification rather than talking like they are a total idiot.

Can you upload your excel file that had this code in it and works please.
 
Last edited by a moderator:
As explained in post #14 just open your post #12 attachment workbook AS IT IS then well read & follow the dark red direction.​
If it still not works as expected then attach your updated workbook so including the VBA demonstration in order to see where it / you failed …​
​
 
Marc L said:
As explained in post #14 just open your post #12 attachment workbook AS IT IS then well read & follow the dark red direction.​
If it still not works as expected then attach your updated workbook so including the VBA demonstration in order to see where it / you failed …​
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top