Extract data from CSV File

[BRRLL99]

Hi

I have following data in the CSV file , I would like to extract only data that has FIRST R/B

Since the data is in CSV i have applied Trim function and added de-limiter "|" for every 2 spaces, I'm able to apply filter for 

FIRST R/B extract required data.

TRANS. FUNC. PROC.CODE 1RD COUNTS RECON AMOUNT
---------------- ------------ --- ---------- ----------------------------------
FIRST R/B -F PURCHASE ORIG YI 1 2,871.67 CR
                       CREDIT ORIG YI 2 1,000 CR
                       PURCHASE ORIG Y2 3 2,000 CR
---------------- ------------ --- ---------- ----------------------------------

As shown in above example data is considered as one set , I'm unable to apply filter just to FIRST R/B  -F and extract all the data in that set

I can't apply filter to credit other unwanted data of credit is also getting filtered

Expected Output:

TRANS.	FUNC.	PROC.CODE	COUNTS	AMOUNTS
FIRST R/B  -F	PURCHASE	ORIG YI	1	2,871.67
FIRST R/B  -F	PURCHASE	ORIG YI	1	2,871.67
FIRST R/B  -F	CREDIT	ORIG YI	2	1,000
FIRST R/B  -F	PURCHASE	ORIG Y2	3	2,000

CSV input file has been attached for reference

[davidskaife]

Hi @BRRLL99 

Try this, not the most eloquent solution but works!

Bring the text file in with no delimiter and untick 'first row contains field names'

It generates flags for filtering based on certain criteria - a 'Main Flag' where the row starts with FIRST R/B -F but doesn't contain TOTAL, and a 'Secondary Flag' where the row starts with CREDIT (or PURCHASE) and other flags are true.

Rather than trying to go down the Regex route i've just split the data on whitespace, and re-joined fields/created new ones as needed.

Finally i fill in any blanks in the Transaction column.

Hope this helps!

[BRRLL99]

Thank you @davidskaife 

Could you please upload same workflow to this reply message. I'm unable to download the workflow it is showing as (Virus scan in progress ...)

[Yoshiro_Fujimori · Accepted solution]

@BRRLL99 ,

If the report format has fixed width fields, it may be safer to count the columns rather than using "2 spaces" as delimiter.

You can use RegEx Tool (Parse mode) to do this. (Yes, it's kind of manual operation, but it works.)

Workflow

1st Multi-Row Formula

  Page = IF StartsWith([Field_1], "^") THEN [Row-1:Page] + 1 ELSE [Row-1:Page] ENDIF

2nd Multi-Row Formula (group by Page)

  isTrans = IF StartsWith([Field_1], "TRANS. FUNC.") THEN "True"

    ELSEIF [Row-1:isTrans] THEN "True" 

    ELSE "False" ENDIF

RegEx

  (.{19})(.{16})(.{8})(.{4})(.{20}).*

3rd Multi-Row Formula

  TRANS. = IF IsEmpty([TRANS.]) THEN [Row-1:TRANS.] ELSE [TRANS.] ENDIF

Output

TRANS.	FUNC.	PROC.CODE	COUNTS	AMOUNTS
FIRST R/B  -F	PURCHASE	ORIG YI	1	2,871.67
FIRST R/B  -F	PURCHASE	ORIG YI	1	2,871.67
FIRST R/B  -F	CREDIT	ORIG YI	2	1,000
FIRST R/B  -F	PURCHASE	ORIG Y2	3	2,000
FIRST PRESS	PURCHASE	ORIG YI	1	2,871.67
FIRST PRESS	PURCHASE	ORIG YI	2	1,000
FIRST PRESS	CREDIT	ORIG Y2	3	2,000
FIRST PRESS	TOTAL		6	2,871.60

[davidskaife · Accepted solution]

Hi @BRRLL99 

Let's try and see if this upload works :)