Parsing ASCII File
- 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,
I am new to Alteryx and I was wondering if it is possible to parse and select specific data from a flat file? The data needed would be (RECV NUMBER, RECV NAME, RECV R/T, RECV ID, AMOUNT AND RETURN CODE).See the example below for what need to be parsed. Any help would be greatly appreciated.
RETURNED DEBITS POSTED:
ORIG NAME: GEORGETOWN COMPANY ID: 012345678
ORIG EFFECTIVE DATE: 01/11/20 BATCH DESCRIPTION: PYMT
RECV NUMBER: 000999999999 TRANSACTION: RETURN
RECV ACCT: 099999999 ACCT TYPE: CHECKING
RECV R/T: 0999999 AMOUNT: $7.43
RECV ID: 009999999 RETURN CODE: R03-NO A/C UNABLE TO LOCATE
BANK ORIG TRACE#: 0123456789 CO ORIG TRACE#: 1234567899999
ADDENDA:
CORRECTION INFO:
_________________________________________________________________________________________
ORIG NAME: PENN STATE COMPANY ID: 0987654321
ORIG EFFECTIVE DATE: 01/11/20 BATCH DESCRIPTION: CHECK PYMT
RECV NAME: 001-99999998 TRANSACTION: RETURN
RECV ACCT: 888888888 ACCT TYPE: CHECKING
RECV R/T: 4444444 AMOUNT: $7.49
RECV ID: 00234382 RETURN CODE: R03-NO A/C UNABLE TO LOCATE
BANK ORIG TRACE#: 022222222222 CO ORIG TRACE#: 4444444444444
ADDENDA:
CORRECTION INFO:
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Some columns are not parsed as expected, but fortunately they are not among the columns you need.
- 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 tried your method but it seems the actual flat file is a bit weird and it failed. I have attached the file for you to review.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @anom23
I attached the workflow that it reads your uploaded file.
If you have any questions, please don't hesitate to contact me.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
As long as your data doesn't stray far from what you are showing:
Your nasty REGEX for parse method:
RECV (NUMBER|NAME)\: ([\d-]*).*\nRECV ACCT\: (\d+).*\nRECV R\/T\: (\d+) AMOUNT\: \$([\d\.]*)\nRECV ID\: (\d*) RETURN CODE\: (.*)
I usually put \s for white spaces between words so the REGEX can pass through different languages if ever needed but I left them out of this one for legibility.
- 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
@anom23
Just as @AkimasaKajitani pointed out, a cleaning tool will be necessary in front.
@AkimasaKajitani ありがとうございます。
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thank you this works!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for letting me it help and also the accept mark!😁
