Importing text file with no delimiters

[tww]

Hi all,

I got a fairly complex text file that I would need to extract data from. See sample input and output files attached.

I am fairly new to Alteryx and not even sure where to begin. I would appreciate any help or suggestions from the community.

Thank you

[Yoshiro_Fujimori · Accepted solution]

Hi  @tww ,

Your data contains reports of multiple formats.

So you have to deal with each case with trial and error basis.

I parsed several fields as samples to show you the approach.

I hope you find it useful. Good luck.

Workflow

Step

1. Find the row where a new page starts.

2. Add Report No. (Multi-Row Formula tool)

3. Filter the row where your desired data is contained. (Filter tool)

4. Extract the data using the pattern. (Formula tool, often RegEx function)

5. Join all the fields you extracted using Record ID

Sample formula in Step 4

Report Name = Trim(Substring([Field_1],34,70)," ")

Date = Right([Field_1], 8)

Point = REGEX_Replace([Field_1], ".*POINT: ([\u\d]+) .*", "$1")

APPL ID = REGEX_Replace([Field_1], ".*APPL ID: ([\u\d]+) .*", "$1")

Output

[tww]

Thank you so much @Yoshiro_Fujimori  for the solution. It was great, By the way, could you point me to a good resource to learn more about RegEx function)? Thank you

[Yoshiro_Fujimori]

Hi @tww ,

In general for each tool, Tool Mastery series is a good start point to learn.

Post for Regex is here. https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Tool-Mastery-RegEx/ta-p/376...

I usually test my RegEx formula on https://regex101.com/ .

Good luck.