Working with a .txt file with dups headers

Here is how you can do it.

Workflow:

1. Using multi-row formula to calculate page no on every occurrence of Invoice No header.

2. Using filter tool to keep only rows which have Invoice Number (I,e data)

3. Using data cleanse to remove tabs and duplicate spaces.

4. Using Regex to parse data.

5. Keeping only needed columns using select.

Hope this helps : )

prase file.yxmd

Sorry, i was only able to make it work using reporting tools.

Workflow:

Output:

Hope this helps : )

Output File.xlsx

prase file.yxmd

All comments

Qiu

@itssumanb
I hope this is something would help/

0702-itssumanb.yxzp

Hello @Qiu

Thank you so much for looking into this for me. Your solution is definitely helpful and very close to getting what I need. The only caveat is when addresses are something like attached and highlighted. In the first case, the 4817903 should be the TRADE NO and 9106-5300 QUEBEC INC#142 should be the TRADE NAME. I have no clue how to read/write regex expressions, can you help with this for me, please?

Can you provide this sample data to work on.

I have attached an updated text file with sample data that has longer Trade names. Thank you so much for helping.

Test Alt as of March 1220-v2.txt

Here is how you can do it.

Workflow:

1. Using multi-row formula to calculate page no on every occurrence of Invoice No header.

2. Using filter tool to keep only rows which have Invoice Number (I,e data)

3. Using data cleanse to remove tabs and duplicate spaces.

4. Using Regex to parse data.

5. Keeping only needed columns using select.

Hope this helps : )

prase file.yxmd

Qiu

@itssumanb
I am sorry that I was not able to reply in time.
But glad that @atcodedog05 had given the desired answer. 👍

@Qiu please don't apologize. You did help and your solution was very close to the expected results. I appreciate your help. Thank you

Its because the number has a comma in-between. Please find the modified workflow. If it doesn't work can you provide a sample file with that row. We can take a look into it 🙂

prase file (1).yxmd

It worked. Thank you so much!

Glad to hear

Happy to help : ) @itssumanb

@atcodedog05 can you direct me to the best resources (links) for learning Alteryx Regex or your recommendation as to how I can approach towards learning this powerful Alteryx functionality? I love them but can never get to understand how to write 🙂

You can refer to this website it should be helpful https://regexr.com/

You can definitely post it has a new question so that lot more people can share you their learning journey. I mostly learnt it by exposure and experience 🙂

This would be tricky. Is it possible to share the expected output file.

Hello @atcodedog05

I have attached both the input file (text) and the expected output file (excel). Thank you very much for your help.

Input text file.txt

Expected output file (Excel).xlsx