Spreadsheet merge header row with body row

[lec86042]

Hello Everyone,

This is my first time here so I apologize if this is not the right place to ask this question.  I am looking for help to merge several rows into a single row in a spreadsheet.  Here is an example of the input and the desired output:

InputFile.xlsx

OUTPUT.xlsx (or csv)

Output

What components can I use to split the header which is the company ID and Name then use the ID with each invoice that starts each row with "IN".

Any help or suggestion is much appreciated.

(using talend studio 7.2)

Thank you in advance.

[Anonymous]

Hi
To merge the company ID with the body content, my idea is to use one tFileInputExcel to read only the first two cells first, details:
tFileInputExcel--main(row1)-->tFlowToIterate--iterate--tJavaFlex--runIf--tFixedFlowInput--main--tHashOutput
on tJavaFlex:
in the begin part:
String c1="";
in the main part:
c1=(String)globalMap.get("row1.c1");
if(c1.startsWith("IN"){
globalMap.put("writeData",true);
}else{
globalMap.put("company_id",c1);
}

Set the condition of runIf as:
(Boolean)globalMap.get("writeData")

on tFixedFlowInput: generate the current flow with three columns:
company_id: (String)globalMap.get("company_id")
Document: IN
Type number: (String)globalMap.get("row1.c2")

tHashOutput: write the data in memory for used later. In next subjob, you can read back the data using tHashInput.

Hope it helps!

Regards
Shong