topic Re: How to create multiple excel sheets in talend in same subjob in Talend Studio

How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Tue, 23 Apr 2019 14:35:08 GMT

Hi All,

How to create multiple excel sheets in talend in same subjob.
Attaching the Excel Final_GS1_Clothing_PC.xlsx, in this file i want to generate seperate excel sheet based on identical Id column.
eg : row 2,3,4 and 5 have same Id's i want to generate a sheet name starting with the Id column value and all the same id rows should get added in that sheet.

Please let me know if you have any questions.

Regards,
Yogesh.

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Tue, 23 Apr 2019 14:38:47 GMT

I have also referred link https://community.talend.com/t5/Design-and-Development/Create-Multiple-Tabs-with-single-excel-file-based-on-the-column/td-p/22746 but it is not working and giving me the following error.

error.PNG

Re: How to create multiple excel sheets in talend in same subjob

akumar2301 — Tue, 23 Apr 2019 15:00:27 GMT

Link Provided should work.

NULL is coming because , tFileInputExcel is trying to read Records after End of line

Try 1) in Advance Setting of tFileInputExcel

select "Stop reading on encountering empty rows"

Try2) in tFileOutExcel , define some dummy name if sheetname is NULL

e.g.

((String)globalMap.get("row1.sheetname")) == null or ((String)globalMap.get("row1.sheetname")).length()== 0 ? "dummy"(String)globalMap.get("row1.sheetname"))

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 08:16:45 GMT

Hi Abhishek,

I am able to generate multiple sheets in tOutputExcel but the data is coming as null.
I have attached the talend job screenshot please check
Regards,
Yogesh.

Job.PNG

Re: How to create multiple excel sheets in talend in same subjob

akumar2301 — Wed, 24 Apr 2019 08:56:05 GMT

Can you just try to put tLogRow and check if data is comiing correct before tFileexcelOutput.

if tLogRow print , your result , give us screenshot of tFileexcelOutput.

Re: How to create multiple excel sheets in talend in same subjob

Anonymous — Wed, 24 Apr 2019 09:12:41 GMT

Hi Yogesh,

Most probably you might not have assigned the output of tFlowtoTerate columns after iteration correctly.

Please add tLogrow before and after to do the testing and you will get the clue.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: How to create multiple excel sheets in talend in same subjob

Anonymous — Wed, 24 Apr 2019 09:37:20 GMT

What do you have in tJavaRow component?

Say if you have input schema as a,b,c and output schema as a,b,c for tJavaRow component.

You must mention in tJavaRow component as

output_row.a=input_row.a;

output_row.b=input_row.b;

output_row.c=input_row.c;

If you do not do this, you will get empty data in output excel file.

I hope, this solves your problem.

Thanks,

Meghana.

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 09:42:41 GMT

Hi @nthampi ,

Before tFlowToIterate it is able to display data. I am unable to connect tlogrow after tflowtoiterate

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 09:44:24 GMT

Hi @uganesh ,

I have connected tLogRow before the tFileOutputExcel but i am getting null

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 09:47:09 GMT

Hi @MeghanaKottala ,

I am doing the same, output_row.a=input_row.a;
till tMap_1 i am getting the correct data but after tFlowToIterate flow i am getting null values

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 09:53:10 GMT

Attached are the job changes suggested by you guys.
PFA

error1.PNG
scenario1.PNG

Re: How to create multiple excel sheets in talend in same subjob

Anonymous — Wed, 24 Apr 2019 09:59:39 GMT

what have you given in tFixedFlowInput ?

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 10:03:51 GMT

Please find below attached screenshots of tflowtoiterate and tfixedflowinput

tFixedFlowInput_2.PNG
tFlowToIterate_1.PNG

Re: How to create multiple excel sheets in talend in same subjob

Anonymous — Wed, 24 Apr 2019 10:31:22 GMT

Hi,

Please put a tLogrow before and after tjavarow and see whether the data is present in both tLogrow.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 10:33:37 GMT

In first tlogrow its displaying the data but in second tlogrow it is giving null values

Re: How to create multiple excel sheets in talend in same subjob

Anonymous — Wed, 24 Apr 2019 10:46:55 GMT

Perfect.

So the mapping from input to output columns is not happening correctly in tjavarow.

Could you please try for one column using automapping and check the difference.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 10:51:28 GMT

Hi @nthampi ,

Did the auto mapping but still getting null, i am attaching the output excel please have a look

Re: How to create multiple excel sheets in talend in same subjob

Anonymous — Wed, 24 Apr 2019 10:56:18 GMT

Hi,

I did a similar one and its working fine.

Could you please also add a System.out.print(input_row.id) to make sure that input data is coming correctly to tjavarow.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: How to create multiple excel sheets in talend in same subjob

kuwaryogi27 — Wed, 24 Apr 2019 11:06:55 GMT

Hi @nthampi ,

I am doing the same but after tflowtoiterate in my job tfixedflowinput is there after that i have tjavarow component in which i have synced the columns and simply clicked on generate code.
after tmap its giving me proper output but after tfixedflow its giving me null i have attached the input files as well you can try the same at your end.

tjavarow.PNG

Re: How to create multiple excel sheets in talend in same subjob

Anonymous — Wed, 24 Apr 2019 11:12:40 GMT

@ykasture

Sorry. Your previous post is slightly confusing for me.

You were telling that you added a tLogrow before tjavarow (which comes after tfixedflowinput) and you could see the data is present in in the tLogrow before tjavarow.

But you are not getting data after tjavarow and when you added a tLogrow, you got the value as null. In this case, the issue could happen only within tjavarow.

So you need to print the input data by System.out.print() and see whether the data from input_row is coming correctly. Ideally it should since you are able to see them in tLogrow before it.

Then you need to assign the mapping for one column and see the output in tLogrow after tjavarow. Ideally it should work. Still you are facing issue, let us know.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂