TXT File filter

[Hi2023]

Hi,

I have a TXT file that I need to extract certain columns only that have a W. I was doing a sample then parse to filter it isnt working correctly. 

Any suggestions on best way to do this

thanks

The TXT file has about 25 lines but I only need lines 20-24 that contain W  in the front

[ChrisTX]

Try a Filter tool with a formula like this         StartsWith([my field],"W")

or you may need a RegEx tool.

Posting sample data would help.

Chris

[Hi2023]

ok, 

so I actually need a lil more then those lines. I need name, acct,  & Y address of that section ONLY IF it has a Y in front of address. I can provide a brief overview of what it looks like:

DATE                                  Title

AREA                               location

*******************************************************

ACCT# 123456

name 1

name 2

Y  address1

adress 2

Y City -State

Y Zip 

Cell 

Phone

______________________________

DATE                                  Title

AREA                               location

*******************************************************

ACCT# 123456

 name 1

name 2

Y  address1

address 2

Y City -State

Y Zip 

Cell 

Phone

thanks

[Hi2023]

the output should be

name 

acct#

Y address

------in an excel file

thanks

[ChrisTX]

What have you tried so far?

You'll need one or more Filter tools, and likely a Multi-Row Formula tool to link each "record" since a record spans multiple rows.

[Hi2023]

I have tried to do a sample then a parse, filter but it didnt work.

I think I need to first format it correctly, then filter but trying to see what the best way to do it

[ChrisTX]

These first few steps may help:

Use a Formula tool to create a new field like "New Record".  Set the value to "x" or 1 or True (boolean) if the string StartsWith ACCT.

Then use a Multi-Row Formula tool to create a new field like "Record Number".  If the field "New Record" value is x, then increment the "Record Number", else copy the number from the prior row.

Then use a CrossTab tool to convert rows to columns, based on the Record Number field.

Chris

[Rafael_Caixeta]

Hey @Hi2023, if I understand what you want, in this simple workflow that is attached here you could execute your task. Please, take a look at it, and if works, don't forget to return here and give us feedback.

[Hi2023]

What if I need to output Name 1 as well?

[Rafael_Caixeta · Accepted solution]

You could insert a line with: OR Contains([Field1], "name 1") after the "Contains([Field1], "Y Zip")" in the filter tool and then modify the nearest formula tool by adding new lines on the if clause and get the result. I attached the updated workflow here for your better understanding.