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

[Anonymous]

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)

P.

[Anonymous]

So just wondering if the notation of A1, B3 etc can be used instead in this component. And also if i can pass context variables into these fields of X,Y.

The final aspect of this, is how is the aggregated values of Average, SUM, MAX passed from the tAggregate component to the ExcelOutput component?

[Anonymous]

You can't use Excel notation (but B2 is "B" + "2", and 'B' - 'A' = 1 if you use ascii or UTF coding, it works if stricly less than AA) 

You can pass values in context, just use context.myValue instead of the value.in the component cells.

[Anonymous]

So sescofet,

I see in your screenshot here, it looks like you did a summary and group-by on names, and got summary values of each person.

The values may be SUM, Average it seems, But how did you get these multiple values from the tAggregate component and how was it passed to the Excel Output?

When i used the tAggregate its seems to me that it returns only one value, not multiple values.

I could be wrong.

Hope you can clarify this. Once i understand this part, i will accept this as Solution to my question.

Paul

[Anonymous]

Explanation is here: https://help.talend.com/reader/KxVIhxtXBBFymmkkWJ~O4Q/i_YvOl2oUaVpW1UTnJao_g

Then you connect tAggregate with tFileInputExcel with "Main" link

[Anonymous]

Hi sescofet,

I am aware of connecting the Excel input to TAggregate component, i was able to aggregate the columns from the input files, but i did not have control of where to place on the Excel output file.

So its the second stage i need to know, how to pass aggregated values from the tAggregate component to output excel.

BUT i was only able to do this for one aggregation, to pass only a single value to the Excel sheet.

You seem to have done it for multiple values coming from just one tAggregate component.

Its like you did a Pivot table, the summary of many people and their values.

How did you get all that from the tAggregate to the Excel Output sheet?

[Anonymous · Accepted solution]

I simply followed the given example and connect the tAggregate to tFileOutputExcel with "Main" link.

2019-05-22 21_15_55-tAggregateRow • Talend Components Reference Guide • Reader • Welcome to Talend H...

[Anonymous]

Ok,

So it seems that is all one needs to do.

I will give it a try.

Thanks for letting me know.

I will get back to reply here once i have a chance to try it out.

P.

[Anonymous]

Hi Sescofet,

Your suggestion worked. I just used a tInputExcel, tAggregate and tOutputExcel.
The x & y positioning worked.
But when i tried to now change my input Excel and have 2 sheets, and just specifying one of them for tAggregate, i ran into issues, which i will set up as another question.
I will select your response as the correct solution.