Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Working with a .txt file with dups headers

itssumanb
8 - Asteroid

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. 

19 REPLIES 19
Qiu
21 - Polaris
21 - Polaris

@itssumanb 
I hope this is something would help/

0702-itssumanb.PNG

itssumanb
8 - Asteroid

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? 

 

Alt2.JPG

atcodedog05
22 - Nova
22 - Nova

Hi @itssumanb 

 

Can you provide this sample data to work on.

itssumanb
8 - Asteroid

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

atcodedog05
22 - Nova
22 - Nova

Hi @itssumanb 

 

Here is how you can do it.

 

Workflow:

atcodedog05_1-1626114902118.png

 

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
21 - Polaris
21 - Polaris

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

itssumanb
8 - Asteroid

@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
8 - Asteroid

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. 

 

itssumanb_0-1627590542954.png

 

atcodedog05
22 - Nova
22 - Nova

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 🙂

Labels
Top Solution Authors