combining and mapping rows of an excel file

One Star

combining and mapping rows of an excel file

Hello people,
I am completely new to Talend and I'm researching of Talend can help me with my 'problem'.
So here's the deal. I have these excel files with data I need to send to a webservice. This wasn't that hard to achieve and I managed to get things working with a tFileInputExcel --> tXMLMap --> tESBConsumer setup.
But here's the real problem, and I wonder if Talend can help me with it;
The excel file basically looks like this:
customer_id     product_number     product_name          customer_name
11                  12345                  PC SUPPORT             John Smith
11                  56789                  Macbook Pro             John Smith
12                  12345                  PC SUPPORT             Adam Johanssen
12                  66554                  ACER laptop              Adam Johanssen
13                  12346                  PC/Tablet SUPPORT    Sandra Bullet
14                  56788                  Macbook Air              Peter Sandman
What basically happens is a customer buys a hardware product like a PC or laptop, and buys some kind of service product for that PC.
With my current setup, my job just parses over the whole .xls file row by row and maps it. But what I need is to somehow combine rows into a single 'object' based on the customer_id, so that the hardware product and service product a customer bought, belong to the same row/object.
So what needs to happen is:
-check if every service product has a hardware product (are there duplicate customer_id's?)
-if yes, combine/map those products so it becomes something like this
customer_id     service_product_number     service_product_name          customer_name         hardware_product_number   hardware_product
11                   12345                                 PC SUPPORT                       John Smith                  56789                                 Macbook Pro
-if not, throw a fault/log it/ignore it
-send the combined rows/object to a webservice
If someone could at least give me a tip on what components I could use for this (if there are any) it would be much appreciated.

Re: combining and mapping rows of an excel file

I do not understand your dataset from the description you gave.
You mention software product - there is none shown on your example
The components you have listed are correct.
you can have a look at 'tSchemaComplianceCheck' to validate unique IDs
each row is in fact loaded into a single 'object' (java); please have a look at the 'CODE' tab in your job.
were these suggestions helpful?
One Star

Re: combining and mapping rows of an excel file

Hello Nicolas,
I made a mistake. It should have been 'service product' instead of software product. (i changed it in my original post).
What I meant is that a Customer (John Smith) buys a product (Macbook) and also buys a 'service' product (PC SUPPORT) to go along with his brand new Macbook.  But these products John has bought (Macbook and PC SUPPORT) are on separate rows in the .xls file.
What i want is that both rows belonging to that Customer (John Smith with ID 11) become a single row/object so I can send that combined data to a webservice. 
I hope this made it a bit more clear, I will look into tSchemaComplianceCheck, thanks! 


Join us at the Community Lounge.

Register Now


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables


How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration


Downloads and Trials

Test drive Talend's enterprise products.