Working with a .txt file with dups headers
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@itssumanb
I hope this is something would help/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have attached an updated text file with sample data that has longer Trade names. Thank you so much for helping.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 : )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@itssumanb
I am sorry that I was not able to reply in time.
But glad that @atcodedog05 had given the desired answer. 👍
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Qiu please don't apologize. You did help and your solution was very close to the expected results. I appreciate your help. Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 🙂
