topic Re: Adding user defined header row in the Excel file in Talend Studio

Adding user defined header row in the Excel file

RSA1598789664 — Sat, 16 Nov 2024 01:26:29 GMT

Hello Everyone,

Could some one please help me on the below requirement.

I have set of Excel files and each Excel files has 2 sheets with in it.

One of the Excel file name : Europe_talend

Sheets of Europe_talend.xlsx as below

Requirement

I want to create a single .xlsx file with below structure

Tab Name = XSLXname_TabName

The first row of the Excel should have Heading: Data for XLSXName - TabName

The data should follow the heading

i want to insert header row as "filename-sheetname" (highlighted as yellow in below snapshot) in everysheet of an Excel feel.

Expected output file :

Regards,

Anil Rawat

Re: Adding user defined header row in the Excel file

Anonymous — Fri, 18 Sep 2020 04:07:21 GMT

Is the sheet name changed in each Exce file? If so, you can try to use a tFileList to iterate each excel file, set the sheet name using position on tFileInputExcel. There is a global variable that stores the current sheet name,

(String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET")

this global variable can be used as the header value.

Regards

Shong

Re: Adding user defined header row in the Excel file

RSA1598789664 — Fri, 18 Sep 2020 06:32:23 GMT

Hi Shong,

Thanks for replying on my post !

I have already used tFileList to iterate each excel file. and set the sheet name using position on tFileInputExcel.

My question is - how can i insert a header row in the tFileoutputExcel as fFileOutputExcel has checkbox option to include header and does not allow to use global variable which can be used as header value.

Below is the job flow for my requirement.

Regards,

Anil

Re: Adding user defined header row in the Excel file

RSA1598789664 — Fri, 18 Sep 2020 07:05:29 GMT

Hi Shong,

I have added global variable current sheet name as one of the global variable in tFixedFlowInput

and got the result like this

but i want Europe_talend-Paris as my first row and the data of the excel sheet should follow the heading as below

Could you please share your thoughts on the same?

Regards,

Anil

Re: Adding user defined header row in the Excel file

RSA1598789664 — Sat, 19 Sep 2020 10:49:55 GMT

@Shicong Hong . Could you please share your expert advice on the same ?

Re: Adding user defined header row in the Excel file

Anonymous — Mon, 21 Sep 2020 04:20:33 GMT

Add separately headers to the Excel files before writing data to it. eg:

tFixedFlowInput--main--tFileOutputExcel1 (write Europe_talend-Paris )

|onsubjobok

tFixedFlowInput--main--tFileOutputExcel1 (append existing file, write Talend_ID, Talend_Location, Servers)

|onsubjobok

write data to existing file.