How to extract data from Unstructured format (Multiple Flat file - Daily files needs to be consolida

Conditions: Need to look for 'Report-47' and then 'Monetary Instrument Sales'. It it's then extract the values for 'Purchaser Account Number', 'Cash In', 'Reason Code', 'STAT' as highlighted. The flat file is just 1% of daily report so we have to read through complete file for each day then consolidate other daily files to get a monthly report. I am a new Trifacta user. Any advise on this will be highly appreciated!. Thanks in advance!

Trifacta Classic

Accepted answers

Trifacta_Alumni

Hey @Sundar Jagan? , no problem!

Here are the last few steps:

I've also attached the flow in version 4.2 to this comment. You should be able to import the flow (go to flow view, select more options (...), click Import, and choose the attached .zip file) and then simply replace the source file with you flat file example, that way you don't have to recreate the recipe from scratch 🙂

Hope this helps!

David

All comments

Trifacta_Alumni

Hey Sundar!

Attached is a Recipe to parse this file. Here is output + recipe panel opened

So the essence of working with unstructured data like this is to find patterns and positions that you can use to start to break the data out into rows and columns. I'm not going to walk through every step in the recipe in depth because it is extensive, but to summarize the steps taken:

Remove Structure from the dataset. Trifacta tries to infer structure (works well with csv's, excel, json, etc... not so much with unstructured text 🙂), we want to remove that structure to get all of the data into a single cell
splitrows on 'MONETARY INSTRUMENT SALES'. the splitrows step converts data into rows, using MONETARY INSTRUMENT SALES allows us to create a new row every time a new monetary instrument sale is hit.
Create an array, where each element is separated by \r\n (carriage return and new line). This will allow us to then flatten the array (converting each element into a single row) thus getting the data to a state that we can start to split into columns. We're also nesting this array with an index, that we will use later on.
Flatten the array as noted above, unnest to get the index into it's own column
split columns based on positions. This assumes that the relative format of the above file stays the same (as in, the system producing these files produces them in the same unstructured format every time. So containing some structure...)
remove the noise. only keep information on the 7th and 9th lines of each instrument sales invoice. These lines contain the info on 'Purchaser Account Number', 'Cash In', 'Reason Code', 'STAT'
back fill values so there is at least one row containing all of the above values from step 6 (for instance, at index 1).
keep row where index is 1 (thus getting only one row per sales invoice)
delete unimportant columns and rename fields

Thanks to @Alon Bartur? who came up with this solution. Shameless pitch, but the great part about using Trifacta to solve this was that seeing a preview of every step we are creating really allowed us to validate that the steps we were taking were moving us in the right direction. All in all it didn't take too long to solve (i'd be hopeless trying to do this with python 😅).

Go ahead and give it a shot and let us know if it helps!!

Trifacta_Alumni

Wow, outstanding solution @David McNamara?

The common strategy with this and similar “structured” data is to turn it all down and rebuild.

TrifactaUsers

?Excellent! Thank you so much @David McNamara? for the solution. I really appreciate all your efforts for such a quick turnaround. I am preparing recipe now using release 4.2 and post the update very soon. Thanks again!

TrifactaUsers

Hi David, Can you post the image of the remaining steps of the recipe. I can see until step 17 and want to see the remaining steps for validation. Thanks @David McNamara? ?

Trifacta_Alumni

Hey @Sundar Jagan? , no problem!

Here are the last few steps:

Hope this helps!

David

TrifactaUsers

Amazing! Thanks a lot @David McNamara? . Really appreciate for attaching the flow for version 4.2 it's very much helpful. Hats off again to both of you @David McNamara? and @Alon Bartur? . Just wondering if you would recommend any resources to extract the data in a same way from PDF.?

Trifacta_Alumni

Hi Sundar,

No resources that I know of, unfortunately. I can try pull together some information and send it along to you. I will add to my todo for next week.

Have a nice weekend

David

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235