How to trap fields/values from a .txt input
- 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
I have a .txt file where the data is somewhat unstructured as shown in the two attachments, especially page 2. Highlighted in both are the data I would like to bring into Alteryx as a table with the field names (e.g. PAYT GRP ID/NAME) and their corresponding values (e.g. 24/MONTHLY AFIS). How is the best way to trap this information and bring it in. The other issue is this txt document can have a page range of anywhere between 3 - 15 but I'm only needing the information from pages 1 & 2. Thanks.
Solved! Go to Solution.
- Labels:
- Data Investigation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
1. You can go to whatever person or program is generating the file and get them/it to write the data out in a usable format (I'd recommend csv or fixed-width, but anything that is given as an option by the Alteryx input tool should work).
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you. I don't know Python so will likely explore something along the lines of option 1.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Jake5
Following the flat input path + some regex its possible to do it. Check the attached workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Jake5 Here is another way to do this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?