I'm trying to extract some data from a .txt file. The data looks messy with stacked headings:
Form 1
First Name Last Name Age
Occupation Organisation Residency
------------------------------------------------------------
Jonna Karly 53
Instructor NYU Los Angeles
Hsiao-Ran Liu 22
Student NYU Kaohsiung
Form 2
First Name Last Name Age
Occupation Organisation Residency
------------------------------------------------------------
Jonna Karly 53
Instructor NYU Los Angeles
Hsiao-Ran Liu 22
Student NYU Kaohsiung
...
(Disclaimer: All data presented are fictional, no personal data is leaked.)
As can be seen, the headings are stacked on top of each other so each record has two rows of data. Note that the dashed line under the headings is also included in the text file. Also there're multiple forms sharing the same layout stacked on top of each other in the text file as shown here.
For the Jonna Karly record, First Name matches Jonna, Last Name matches Karly, Age matches 53, Occupation matches instructor, Organisation matches NYU, and Residency matches Los Angeles and so on.
Suppose that I'm trying to extract all the ages in the Age column (53, 22, etc.) for each form, how do I achieve this? I know regular expression can help, but I have absolutely no idea how to deal with this type of structure.
FYI, I've tried using delimiters when importing the text file into Alteryx Designer, but for whatever reason, the error "too many values in record #" continues to pop up, so I got to use no delimiter, and now everything's squeezed in one column. I believe RegEx is the only solution.
