How to create multiple excel sheets in talend in same subjob

Seven Stars

How to create multiple excel sheets in talend in same subjob

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.


Accepted Solutions
Seven Stars

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

Hi @MeghanaKottala ,

The solution you provided worked for me.
Thank you all for your valuable time.
Attaching the solution provided by @MeghanaKottala , so that it can be helpful to all

Regards,
Yogesh.

View solution in original post


All Replies
Seven Stars

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

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

 

 

Eleven Stars

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

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"Smiley Sad(String)globalMap.get("row1.sheetname"))

Regards
Abhishek KUMAR
Seven Stars

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

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.

Eleven Stars

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

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.

Regards
Abhishek KUMAR
Employee

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

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 :-)

Seven Stars

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

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.

Seven Stars

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

Hi @nikhilthampi ,

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

Seven Stars

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

Hi @akumar2301 ,


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

Seven Stars

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

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

Seven Stars

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

Attached are the job changes suggested by you guys.
PFA

Seven Stars

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

what have you given in tFixedFlowInput ?

Seven Stars

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

Please find below attached screenshots of tflowtoiterate and tfixedflowinput

Employee

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

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 :-)

Seven Stars

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

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

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

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 :-)

Seven Stars

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

Hi @nikhilthampi ,

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

Employee

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

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.

 

image.png

 

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 :-)

Seven Stars

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

Hi @nikhilthampi ,

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.

Employee

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

@kuwaryogi27 

 

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 :-)

Seven Stars

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

Hi @nikhilthampi ,

after tmap_1 if i place tlogrow then it displays correct data but if i place tlogrow after tfixedflowinput then it displays null

Employee

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

Ok. This means that your tFlowtoiterate mapping is not correct.

 

Could you please select the checkbox (Use the default key value in global variables)? This should make sure that no manual mapping errors are present. instead of tFixedFlowinput, could you please try trowgenerator as below?

image.png

 

Ideally it should propagate the data to downstream systems.

 

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 :-)

Seven Stars

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

Hi @MeghanaKottala ,

The solution you provided worked for me.
Thank you all for your valuable time.
Attaching the solution provided by @MeghanaKottala , so that it can be helpful to all

Regards,
Yogesh.

View solution in original post

Seven Stars

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

Hi @MeghanaKottala , @nikhilthampi , @akumar2301  ,

Thank you for your valuable time, i really appreciate it from the bottom of my heart.
Looking forward to stay connected and need you guidance.
Cheers!!

Regards,
Yogesh.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

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 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog