Join the Alteryx Community’s Maveryx Summer Cup event! Compete, network with others, and earn your gold through a series of challenges from July 24th to August 11th. Learn more about the event here.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Parse excel data into multiple rows and columns divided by pages

geeklarokcmie
8 - Asteroid

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 700
EffDt <= 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 Days
NoException 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
NoException 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 Rule
EffDt <= 4 day.=1Debit or Credit=CAND.=1Debit or Credit=DAND effectiveDateEffective DateYYYYMMDD <= Effective Date YYYYMMDD 4 Days No
Many 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
5 REPLIES 5
Rhys_Cooper
8 - Asteroid

Hi @geeklarokcmie. This is an interesting problem I began to solve but notices that page 26 information appears to appear in 3 seperate rows in the output where has page 25 only occupies one row. Can you please explain the logic to why this is the case?

 

Regards - Rhys

geeklarokcmie
8 - Asteroid

Appreciate your support in finding a resolution.

 

The source data is from a txt file that I converted to excel using Power Query.

 

The data in each page is not consistent - one page may have three rows of data under the column op value section or the additional info section

while other page may only have a single row of data under the columns op value section or the additional info section.

 

Let me know if this information was helpful. 

Rhys_Cooper
8 - Asteroid

@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.

Gaurav_Dhama_
9 - Comet

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.

geeklarokcmie
8 - Asteroid

@Rhys_Cooper @Gaurav_Dhama_ Please find the attached txt file.

 

Labels