Looping through lines in text file and extract specific data to new columns

Hello,

I have an input text file that I need to loop through and split into structured columns. So far, the input file is setup without headers and delimiter set "\0" and filtered out Null separator rows. I'm having trouble writing regex to define the datasets and range to be identified and extracted. I'm not sure of what the best approach to a solution is.

The input text file layouts data by Company (302, 303, 304, etc) and Instrument Type (DEP, LON, etc). The start of each company's dataset is identifiable by the Company code row and the range ends with the row containing "Company Totals:". I need to capture every company's entire dataset.

Next step is to split the dataset into columns and add columns for the respective Company and Instrument Type codes. The final output should look like the attached excel file.

Thank you in advance for your help and guidance.

X400Parsing.yxmd

Output_Example.xlsx

Regex

Parse

Iterative Macro

Accepted answers

HW1

There you go

X400Parsing.yxmd

All comments

HW1

I guess you forgot to attach the text file/input file. Can you please add that to enable iterate regex?

kbsocgen

Please find text file attached, thank you.

Sample_X400_July_22.txt

HW1

There you go

X400Parsing.yxmd

kbsocgen

Thanks HW1, your solution worked perfectly. However, when I tested your solution against a larger dataset I discovered a few unexpected exceptions where the regex for Instrument Number failed to capture the data. Please is it possible to modify regex to handle the following Instrument Number:

BAP-78810
BAP-148080
B8241066 C
Q2111999 A

Thank you

HW1

There you go

X400Parsing.yxmd

kbsocgen

I've successfully tested the solution. You've provided a great solution to learn from and build similar solution for other text files. Thank you

HW1

Cheers man.

Also, it would be a great idea to learn regex. This solves a whole lot of use cases in itself.

I deal with converting a huge amount of PDF files to text and then to a data frame. I use it a lot.

kbsocgen

Agreed, learning regex will enable me to achieve the text to data frame solutions I need.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703