Parse data from text file

[aparna0208]

Hi,

I'm trying to parse the data from a text file and the sample data is as seen in the attachment

The required output is I want to skip the first 3 rows and row in between the data which starts with the same text as first 3 rows. The data should start from the row Cardholder number/USER ID.

I need to split that row into different columns as headers and have the corresponding values in each column under the headers. Sample output seen below.

I used a input tool to connect to txt file and split data with 'tab' delimiter and start data line from 4 to skip the first 3 rows. After this not able to achieve the output I want so looking for some help. Thanks in advance.

For example

CARDHOLDER NUMBER/USER ID        PLASTIC NUMBER      REQUEST DATE&TIME/RESPONSE DATE&TIME .......and so on

7808493827383728                                                                                                   07/08/24 16:48:32

FALCON                                                                                                                    07/08/24 16:48:32.............and so on

[KGT]

I can't find it now, but there used to be an alteryx training based on this type of format. I'll get you started on the method and then you can play with it from there. What you may not know initially but makes this job easier is those dashes define the size of the field.

If the size of your data is not too great then you can brute force by transposing all the data into one character per field, but I imagine this data is too large.

Be careful on the Input to have no delimiter and not have the first row as headers. In the attached, I've created formulas for extracting each field and then shown how to use a manual process to create 21 formulas (actually only need about11 of them). That can be automated (dynamic formula is the easiest) but concentrating on automating that takes away from solving the issue.

This probably takes a couple of hours playing around from here but the methods I've shown, combined with some row tagging to get the correct rows, and some grouping of rows should get you a solution.

[aparna0208]

Hi @KGT - Thanks for all your efforts in building the sample workflow. It took a while for me to use the logic and cook up something as per my requirements. I think now it's working as expected and kind of testing the data as it is a big text file. Once again thanks for all your help on this!

[aparna0208]

Hi @KGT 

I'm back again with another similar problem. This time the text file is little different and I tried to tweak my workflow a bit especially the formula tool with substring formulas for all different fields. But in the final output the fields are not parsed properly and data is trimmed. I tried a lot by modifying substring values but doesn't help. I have attached my sample workflow and text file. Can you please provide your inputs on this and help me out with it. Thank you in advance!

[aparna0208]

Hi @KGT Please ignore my previous post. I kind of played around with the substring formula and now it worked and I figured it out. Thank you!