Working with a .txt file with dups headers

[itssumanb]

Hello friends, 

I have a text file (sample attached) that I am having a tough time working with on Designer. As you can see the headers (dates and office address) appear on each page. Column headers (Invoice no, postal code etc.) for data are consistent throughout all pages. What I'm trying to accomplish is to get an output file (preferable on Excel) where the headers (date and address stuff) only appear once at the top of the sheet and all other data are then merged (append). In this way, I will have one file with unique column headers to do my analysis. 

[Qiu]

@itssumanb 
I hope this is something would help/

[itssumanb]

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? 

 

[atcodedog05]

Hi @itssumanb 

Can you provide this sample data to work on.

[itssumanb]

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

[atcodedog05 · Accepted solution]

Hi @itssumanb 

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 : )

[Qiu]

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

[itssumanb]

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

[itssumanb]

Hi @atcodedog05 ,

I hope you are doing well.

As I started working on this workflow you helped me with, I noticed the Regex coding was not working to one line item. Wonder why... Can you help again, please? Attached and highlighted is the line I am referring to. It is returning NULLs. Thank you so much. 

[atcodedog05]

Hi @itssumanb 

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 🙂