Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parsing ASCII File

anom23
7 - Meteor

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:

 

 

9 REPLIES 9
Qiu
20 - Arcturus
20 - Arcturus

@anom23 

Some columns are not parsed as expected, but fortunately they are not among the columns you need.

0114-anom23.PNG

AkimasaKajitani
17 - Castor
17 - Castor

Hi @anom23 

 

When you read the flat file that is not like table, you have to firstly load with the delimiter "\0".

 

AkimasaKajitani_0-1610626690858.png

 

AkimasaKajitani_1-1610627092243.png

 

 

After that as @Qiu has shown, you have to make the workflow.

 

anom23
7 - Meteor

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.  

AkimasaKajitani
17 - Castor
17 - Castor

Hi @anom23 

 

I attached the workflow that it reads your uploaded file.

 

If you have any questions, please don't hesitate to contact me.

BretCarr
10 - Fireball

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.

 

AkimasaKajitani
17 - Castor
17 - Castor

HI @anom23 

 

If you want to use @Qiu 's solution, you can use Data Cleansing tool.

The setting is below.

 

 

AkimasaKajitani_1-1611033558312.png

 

 

Qiu
20 - Arcturus
20 - Arcturus

@anom23 
Just as @AkimasaKajitani  pointed out, a cleaning tool will be necessary in front.

@AkimasaKajitani ありがとうございます。

0114-anom23.PNG

anom23
7 - Meteor

thank you this works!!

Qiu
20 - Arcturus
20 - Arcturus

@anom23 

Thank you for letting me it help and also the accept mark!😁

Labels