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.
Some columns are not parsed as expected, but fortunately they are not among the columns you need.
Hi @anom23
I attached the workflow that it reads your uploaded file.
If you have any questions, please don't hesitate to contact me.
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.
@anom23
Just as @AkimasaKajitani pointed out, a cleaning tool will be necessary in front.
@AkimasaKajitani ありがとうございます。
thank you this works!!
Thank you for letting me it help and also the accept mark!😁