Extract File data, transform to appropriate format

Hi ,

I have a flat file in the following format  (each section shows the purchase from different perons, and each person purchase different type of items ):

Peter's Purchase
hamburger, 10.0, 2016-Jun-12
coke,3.5,2016-Jun-12
apple pie,6.5,2016-Jun-14

Address : Rm 3, 123 Tai Kwok Road, Hung Hum

Mary's Purchase
milk,5.5,2016-Jun-18
pieapple,3.5,2016-Jun-18
egg,7,2016-Jun-18
plastic bag,0.3,2016-Jun-20

Address : Rm 2, 3/F, 109 Wong Ting Road, TST

John's Purchase
umbrella,22,2016-Jun-21
battery,4.5,2016-Jun-21
fruit,5,2016-Jun-21
coke,3.5,2016-Jun-22

Address : Rm 12, 10/F, 33 Yee Ming Avenue, TKO

.....etc

 

How to do it in KNIME in order to transform the input file into tabular format in KNIME like follow ?

 

name purchase amount date
peter hamburger 10 2016-Jun-12
peter coke 3.5 2016-Jun-12
peter apple pie 6.5 2016-Jun-14
mary milk 5.5 2016-Jun-18
mary pieapple 3.5 2016-Jun-18
mary egg 7 2016-Jun-18
mary plastic bag 0.3 2016-Jun-20
john umbrella 22 2016-Jun-21
john battery 4.5 2016-Jun-21
john fruit 5 2016-Jun-21
john coke 3.5 2016-Jun-22

 

Appreciate if someone can show some hints on that. Thanks

Hi Macro,

Thanks for the hints.

I will try to construct the proper workflow base on your advise.

Thanks

Hi,

I am going to give you a starting point to work on (these are not homeworks, are they?) The workflow in workflow.png solves your task.

Now it is up to you to implement it properly. Feel free to post here your progresses and to ask again would you encounter any issues. The Regex Split nodes can be tricky, especially when optional spaces are involved. The Missing Value node requires the proper configuration to copy the value down until a new name is encountered.

The output directly from KNIME is in output.png.

Cheers,
Marco

You are welcome!\

Again, if you get stuck on some of the passages, feel free to ask here.

Cheers,
Marco.