How to trap fields/values from a .txt input

Hi @Jake5

Following the flat input path + some regex its possible to do it. Check the attached workflow

Hi @Jake5

Try the attached workflow.

Hi @Jake5

You are right, there is an error unable to parse page numbers above the page 10. Please replace the expression of the multi row formula by this one, it should work now.

if CONTAINS([Field_1], 'PACKET PAGE:') THEN TONUMBER(REGEX_REPLACE([Field_1], '(.*?)(\d+)(.*)', '$2')) ELSE [Row-1:Page] ENDIF

(Basically, i just added the red ? to the previous version)

@Jake5 to be honest with you, i tried 4 and it worked kkkkk, but some other values would make it work too.

I was able to resolve and have attached the updated version for reference. It just needed a padright formula in front of the Regex tool.

Option Workflow_v3.yxmd

All comments

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-can-I-import-these-txt-data-in-a-clean-way/td-p/1062912

Hi @Jake5

Seems to me that you can import the file as Flat ASCII with fixed width and work with it. Take a look at those examples:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Text-to-columns-add-header/td-p/1049583

I expect you're providing .png files because your data is proprietary.

If that's true then please mock up some .txt files that look like yours so that we can work with them to try to solve your problem.

If it's not proprietary then please upload the files here so that we can work with them to try to solve your problem.

Thank you! I was able to mock up a sample file - see attached. And again, I'm only in need of the data from pages 1 and 2 that are highlighted on the .png attachments.

Sample file.txt

I poked around with this for a bit. Here's what I've found.
Alteryx isn't really equipped to handle text data files that are not in either a fixed-width format or a delimited format.

The closest things to what you posted that it can input are .csv (comma separated values text file) or flat ASCII (text files that are delimited or fixed-width).

I can see three possible ways to automate access to the data in the text file.

2. You can pull the data straight from your database (you may have to run some data cleaning and processing with this).

3. You can write a custom script to parse the file (personally, I'd use python; you might have a different preference).

What do you want to do?

Thank you. I don't know Python so will likely explore something along the lines of option 1.

Hi @Jake5

Following the flat input path + some regex its possible to do it. Check the attached workflow

This was very helpful - thank you for preparing this for me as it seems to meet my need! I will need to study it a bit further to understand how you made this work and will let you know if I have any further questions.

jdminton

@Jake5 Here is another way to do this.

Txt example.yxmd

Thanks, but I'm getting a missing field 1 error when attempting to run this - see attached screen capture. I did have to reconnect the input file but mirrored the setttings you used (i.e. File Format = CSV, Delimiters = \t). Can you help me understand why "Field 1" is not writing out from my input?

Error msg.png

Felipe - I'm trying to accomplish a similar objective with the attached .txt file, but I don't understand how to adapt your Regex formula to work with this data set. When running it, I can't get the expected values within the Key and Value fields as I don't understand how this formula works. Also attached is a screencapture (.png) of the fields I'm trying to make use of along with the workflow. Are you able to take a look and advise how to get this regex to work? Thanks.

Sample file_R00848.txt

R00848 example.png

R00848 example.yxmd

Hi @Jake5

Try the attached workflow.

jdminton

@Jake5 for the Field 1 error, you need to uncheck the box in the input configuration that says "First Row Contains Field Names"

Thank you Felipe. That achieved the desired results, but can you share what update you made compared to the workflow I sent you? The regex formula looks the same as what I included in mine.

Thank you, Felipe. So something I just realized is for this input, there can be instances where page 1 layout can repeat in subsequent pages, the difference from one page to the next being the field value for INCLUDE/EXCLUDE GROUP ID/NAME:. Using the attached txt file, page 1 has INCLUDE/EXCLUDE GROUP ID/NAME: = 5/QUARTERLY FI - A RKCONNECT, page 5 = 11/QUARTERLY TPA - R RKCONNECT and so forth. I need the workflow to identify all pages where this layout is present in order to render the attached, desired output. My challenge is trying to trap the unique value for INCLUDE/EXCLUDE GROUP ID/NAME from each page. Are you able to assist?

Sample file_R00848_v2.txt

R00848 example.yxmd

Desired R00848 output.xlsx

Hi @Jake5

You are right, there is an error unable to parse page numbers above the page 10. Please replace the expression of the multi row formula by this one, it should work now.

if CONTAINS([Field_1], 'PACKET PAGE:') THEN TONUMBER(REGEX_REPLACE([Field_1], '(.*?)(\d+)(.*)', '$2')) ELSE [Row-1:Page] ENDIF

(Basically, i just added the red ? to the previous version)

Yes - that worked - thank you!

Hi @Jake5

Sorry about the delay to answer, i just wanted to take a time to do it better.

1-Enter this website: https://regex101.com/

2-Fill the test string field with this full text (even the spaces before/after):

        INCLUDE/EXCLUDE GROUP ID/NAME:        5/QUARTERLY FI - A RKCONNECT

3-Fill the regular expression with this:

\s{4,}(.*?)\s{4,}(.*?)\s{4,}.*

Description of what the groups are getting:

Group 1-Four or more spaces

Group 3-Four or more spaces

Group 5-Four or more spaces and everything after them

Group 2-Smallest sequence of characters between Group 1 and Group 3

Group 4-Smallest sequence of characters between Group 3 and Group 5

Hope this make sense, you will get it through practice.

Felipe - I really appreciate that you took the time to explain this....very helpful! For the '4 or more' expression in this formula, I'm trying to understand how you decided on 4. Is it because any values in groups two and four had 3 or fewer spaces so the '4' expression avoids grouping it with a spaced value?

@Jake5 to be honest with you, i tried 4 and it worked kkkkk, but some other values would make it work too.

Hi, Felipe. It's been awhile but I am hoping you can assist with this question. The attached txt file recently underwent a formatting change and it seems to have disrupted how records within the Value column display within the Regex Configuration tool. Referring to the txt file, page 1 for example. I would expect the values beginning in row 10 to parse so that the 2nd value in the row (for example, the value EXCLUDE for FUND SELECTION OPTION) would display in the Value column. But right now Alteryx is showing this as blank. Are you able to help tweak the Regex configuration so that EXCLUDE, INCLUDE, etc appears in the value column? I have included the workflow for reference. Thanks.

R00848_W0525M1.TXT

Option Workflow_v2.yxmd