Parse excel data into multiple rows and columns divided by pages

Question

Hello Experts, I have 100,000 +rows of excel data that needs to be parsed into rows and columns. To distinguish data in the rows - it is indicated by page numbers (ex: page 1 of 700). Below is the input data and the expected output: Page 25 of 700EffDt <= 4 dayRULE 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 DaysNoException Combo Rule status Page 26 of 700Many 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 DaysNoException Combo Rule status Page 27 of 700 Output: RULE NAMELeft CriteriaColumnOpValue LogicalRight CriteriaColumnOpValue LogicalLeft Match Group ToleranceRight Match Group ToleranceLeft Item Additional Info (L)Op Right Item Additional Info (R)ToleranaceException StatusCombo RuleEffDt <= 4 day.=1Debit or Credit=CAND.=1Debit or Credit=DAND effectiveDateEffective DateYYYYMMDD <= Effective Date YYYYMMDD 4 Days NoMany DR Contract #.=1Debit or Credit=CAND>=1Debit or Credit=DAND Many DR Contract # Reference 4 =*086AND Transaction Type=DWAND Reference 1 = Reference 7 Many DR Contract # Reference 1<> Reference 7<> Effective DateYYYYMMDD = Effective Date YYYYMMDD 30 Days No

geeklarokcmie · Answer

@Rhys_Cooper @Gaurav_Dhama_ Please find the attached txt file.

tete_txt.txt

Gaurav_Dhama_ · Answer

Can you put up the original txt file preview of the same data. The data is broken into multiple rows not by logic but by word, making it difficult to put it together.

Rhys_Cooper · Answer

@geeklarokcmieThank you for your reply: would be very tricky creating parsing logic if the data per each page is inconsistent. We might have to look into parsing the txt file directly itself. Could you share this, or a sample of it, please.