topic Re: using Talend to get data from Excel and then put that Data to Excel output file in Talend Studio

using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:03:00 GMT

Hi,

I am trying to using an Excel sheet as input, and then i used the tAggregate component to get some summary values like : SUM, AVERAGE of columns of the data in Excel.

But I am having difficulty trying to place the summary data in specific sheets / cells in the output Excel file.

SO its about using Talend to create a summary Report.

Hope someone can help me out with this, it seems that this should be simple.

(I am using Talend Open Studio for BigData version 7.1)

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:07:31 GMT

Can you be more precise? Where is exactly the problem?

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:18:39 GMT

Hi,

If my understanding about your query is right, you are looking to format the output results in the excel sheet to move to specific cells (like move data to cell C5 etc.).

It is not possible with standard components. If you want to load data to an excel sheet in a predefined format, you will have to use custom java codes using routines. You can pass the values to this routine and the routine will take care of the rest of formatting.

Having said that, you may have to do some quick surfing in java forums to get codes for excel formatting and from there, you can move forward in right direction.

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: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:20:50 GMT

For example,

Lets say i sum up a column in Excel, say the value is 4250, i get from the tAggregate component.

I want to pass this value somehow into another Excel file, and put it in a particular sheet, that has name Report_Summary.

Then put it in the cell on that sheet at location B5.

That's it. Its that simple.

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:25:11 GMT

Using tFileOutputExcel and "Is absolute Y pos." in "Basic Setting" should work.

2019-05-22 18_24_26-Talend Open Studio for Data Integration (7.1.1.20181026_1147) _ GettingStarted (.png

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:25:25 GMT

I am not looking to format the Excel. Not sure if we are both using the same terminology of Format here.
OK, then if that is the case, how can i pass the results of the TAggregate component, which is one value, like sum,avg, min, max to a Variable to give it to a Java variable. Or I would prefer then to send this data to a Python script instead then. Hope you can let me know.

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:27:44 GMT

Can you post screenshots?

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:28:01 GMT

Hi sescofet,

SO can i enter the position location in Excel using this component, in the alpha-numeric form of "B5" and "$A$7".

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:29:45 GMT

Hi sescofet,
I am not sure why you are asking for screenshots. You want a screen shot of an excel file ?

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:30:22 GMT

No, but you can translate with java code directly in component definition (but why?).

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:31:48 GMT

No of your workflow and tFileOutputExcel parameters.

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:33:34 GMT

Hi,
Is there an easy way to pass the value from tAggregate component? I don't want to look at the auto generated Java code and try to figure out what is where. Using talend the point is to try not to become a Java Developer.

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:35:34 GMT

Hi Sescofet,

I have not setup the tFileOutputExcel parameters, because i did not know what i should set.
That is why i came here on this forum, in order to ask on how and what to set.

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:36:57 GMT

Hi,

Could you please try the below properties?

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: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:43:28 GMT

Hi nikhilthampi
This seems to be using an (x,y) coordinate system for only one cell which is fine for one cell.
But the other notation of "B5", can be used for a Range of Cells. Lets say i want to extract a rectangular range of cells in Excel using the range notation : "B6 9" this represent a 3 by 3 array/matrix in Excel and i want to place this input range of cells to the output.
SO i am thinking about something more generalized.

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:48:33 GMT

X:Y is the top left range used.

Other solution : data in a hidden sheet and formula to "resume sheet"

2019-05-22 18_47_42-out.xls [Compatibility Mode] - Excel.png
2019-05-22 18_48_06-Talend Open Studio for Data Integration (7.1.1.20181026_1147) _ GettingStarted (.png

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:51:22 GMT

SO, it seems i need to show you the excel files, the input and output.

See the screenshots of example Input data and the output Excel i want to get.

ExcelInputData.PNG

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:52:37 GMT

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 17:59:34 GMT

Hi sescofet,
That looks good the image you provided.
So it seems one can use X:Y, seems to be the top left point, but the cell is F6, which would be (6,6), but your coordinate values are (5,5).
I am guessing the convention being used is the reference global point is (0,0) at the top most left, which is different from the (1,1) starting point that Native Excel uses.

Re: using Talend to get data from Excel and then put that Data to Excel output file

Anonymous — Wed, 22 May 2019 18:02:26 GMT

That's right (consider using offset function from A1 for values)

Please note resolved if it is ok for you.