Hi,
I get raw data in report format that looks like this:
Customer: 78566 | ||||||
Address: BOX HILL | HOSPITAL | |||||
EASTERN | HEALTH-ACCTS PAYABLE | |||||
8 ARNOLD | STREET | |||||
BOX HILL | VIC3128 | |||||
AU | ||||||
Material | Description | List price | Curr | Customer price | Curr | Deal type |
9300633714444 | WOOLWORTHS TUNA IN SWEET CHILLI SAUCE 95G | 0.885 | AUD | 0.797 | AUD | Sold-to Contr Price |
0066613004026 | BRUNSWICK SARDINES IN OLIVE OIL 106G | 0.889 | AUD | 0.801 | AUD | Sold-to Contr Price |
Please can you help me reformat the header so my output data looks like this. Thank you.
Customer | Material | Description | List price | Curr | Customer price | Curr | Deal type |
78566 | 9300633714444 | WOOLWORTHS TUNA IN SWEET CHILLI SAUCE 95G | 0.885 | AUD | 0.797 | AUD | Sold-to Contr Price |
78566 | 0066613004026 | BRUNSWICK SARDINES IN OLIVE OIL 106G | 0.889 | AUD | 0.801 | AUD | Sold-to Contr Price |
Solved! Go to Solution.
Hi @chc9,
I've had a go at this - it assumes that the structure of your input is going to be generally the same going forward.
The first thing I've done here is try to identify the specific rows we want, so I've added a record ID first.
Then I've tranposed the data and done two things
1. Identify the rows that contain "Customer:" to get the ID we need
2. Count the number of non-null fields in each row - the assumption being that the max non-null count will be the table rows.
I then join back to the main stream, just the rows that have useful data in.
Create a column for customer ID and fill it down using a multirow formula.
I then update the column names using a dynamic replace and filter out a repeat of the column names later (your input had two tables with the same structure) to give the following output
Hope that helps,
Regards,
Ben
This is a brilliant solution! Thanks.
Hi @chc9,
I've added an alternate method to read from text files (see attached)
The methodology is a bit different as you have to deal with parsing out the text file.
Broadly what I've done is -
This will work as long as this structure is fixed.
If there are changes to the structure this example should give you enough to work out how to correct it/make something that handles variation better.
Regards,
Ben