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

CSV.txt

Yoshiro_Fujimori

@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

workflow.yxzp