Parse Data From TXT File with a lot of space

[geeklarokcmie]

Hello experts,

I need help in parsing data from txt file.

The input txt file is attached below and I'm looking to get the data parsed into rows and columns as seen in the below table :

rule name

Left Criteria

Column

Op

Value

Logical

Right Criteria

Column

Op

Value

Logical

Left Match Group Tolerance

Right Match Group Tolerance

Left Item

Additional Info. (L)

Op

Left Item

Additional Info. (L)

Tolerance

Exception Status

Combo Rule

Desc3=2002 Eff Dt=1

.=1

Book Flag

=

1

AND

>1

Bank Flag

=

2

AND

effectiveDate

Effective Date

YYYYMMDD

=

Effective Date

YYYYMMDD

1 Days

No

Desc3=2002 Eff Dt=1

BICode 1

=

206

Reference 3

=

2002

AI 206

.=1

Flag

=

1

AND

.=1

Flag

=

2

AND

effectiveDate

Effective Date

YYYYMMDD

=

Effective Date

YYYYMMDD

1 Days

No

AI 206

AICode

=

206

Reference 3

=

2002

[Yoshiro_Fujimori]

Hi @geeklarokcmie ,

As this kind of report is difficult to find a simple patten,

I would treat it as a graph paper and deal with the range on each page.

I worked on the first few columns for your reference. I hope you get the point.

Workflow

Output

Page No	Row	rule name	Left Criteria	Left Column	Left Op	Left Value
1	15	Desc3=2002 Eff Dt=1	1	Book Flag	=	1
1	17	Desc3=2002 Eff Dt=1	1	BICode	=	206
2	15	AI 206	1	Flag	=	1
2	17	AI 206	1	AICode	=	206

[geeklarokcmie]

Appreciate providing this solution @Yoshiro_Fujimori. To apply your solution to 700 pages of the raw txt data doesn't provide accurate results as the data is not in the same format in every page.

I have pulled the txt data into excel and transformed using PowerQuery. Is there a simpler solution that can be provided for the below input and expected output? 

Input:

	Page 25 of 700
EffDt <= 4 day	RULE NAME
	Left Right
	Criteria = 1 Criteria = 1
	( Column Op Value ) Logical ( Column Op Value ) Logical
	Debit or = C AND Debit or = D AND
	Credit Credit
	Reference 4 = 856 Reference 4 = 859
	Left Match Group Right Match Group
	Tolerance Tolerance
	effectiveDate
	Left Item Additional Op Right Item Additional Tolerance
	Info (L) Info (R)
	Effective DateYYYYMMDD <= Effective Date YYYYMMDD 4 Days
No	Exception Combo Rule
	status
	Page 26 of 700
Many DR Contract #	MATCH RULE NAME
	Left Right
	Criteria = 1 Criteria >= 1
	( Column Op Value ) Logical ( Column Op Value ) Logical
	Debit or = C AND Debit or = D AND
	Credit Credit
	Reference 4 = 086 AND Transaction = DW AND
	Type
	Reference 1 <> Reference 7 <>
	Left Match Group Right Match Group
	Tolerance Tolerance
	Left Item Additional Op Right Item Additional Tolerance
	Info (L) Info (R)
	Reference 1 = Reference 7
	Effective DateYYYYMMDD = Effective Date YYYYMMDD 30 Days
No	Exception Combo Rule
	status
	Page 27 of 700

Output:

RULE NAME

Left Criteria

Column

Op

Value

Logical

Right Criteria

Column

Op

Value

Logical

Left Match Group Tolerance

Right Match Group Tolerance

Left Item

Additional Info (L)

Op

Right Item

Additional Info (R)

Toleranace

Exception Status

Combo Rule

EffDt <= 4 day

.=1

Debit or Credit

=

C

AND

.=1

Debit or Credit

=

D

AND

effectiveDate

Effective Date

YYYYMMDD

<=

Effective Date

YYYYMMDD

4 Days

No

Many DR Contract #

.=1

Debit or Credit

=

C

AND

>=1

Debit or Credit

=

D

AND

Many DR Contract #

Reference 4

=

*086

AND

Transaction Type

=

DW

AND

Reference 1

=

Reference 7

Many DR Contract #

Reference 1

<>

Reference 7

<>

Effective Date

YYYYMMDD

=

Effective Date

YYYYMMDD

30 Days

No

[Ben_H]

Hi @geeklarokcmie,

Are the pages in this report consistently formatted? If so you should be able to set up the method @Yoshiro_Fujimori suggested and just apply it to each page.

Regards,

Ben

[geeklarokcmie]

The pages in the report are not consistently formatted. 

[Yoshiro_Fujimori]

Hi @geeklarokcmie ,

If the report is not consistently formatted, it is difficult to predict what will come on the next page until you see it.

So we can only try to catch as much data as possible on trial-and-error basis.

I am afraid this is all I can advise.