Alteryx Designer Discussions

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

Parsing ASCII File

5 - Atom

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:

 

 

15 - Aurora

@anom23 

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

0114-anom23.PNG

Alteryx Certified Partner

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.

 

5 - Atom

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.  

Alteryx Certified Partner

Hi @anom23 

 

I attached the workflow that it reads your uploaded file.

 

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

8 - Asteroid

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.

 

Alteryx Certified Partner

HI @anom23 

 

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

The setting is below.

 

 

AkimasaKajitani_1-1611033558312.png

 

 

15 - Aurora

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

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

0114-anom23.PNG

5 - Atom

thank you this works!!

15 - Aurora

@anom23 

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

Labels