One Star

tFileOutputExcel and existing pivot table

Hello,
I have an excel document with 3 sheet. The sheets 1 and 2 are built on excel pivot table using data on the sheet 3 named "Data".
I put data from a database in the sheet "Data" using a tFileOutputExcel with "inculde header" and "Append existing file".
My job works correctly (the sheet "Data" is updated) but the sheets 1 and 2 lost the pivot table properties (there is only the old results). It seems that the excel file is deleted and rewritten, that why the dynamic properties are lost.
Somebody have a solution ?
Thank you.
Frederic.
7 REPLIES
Community Manager

Re: tFileOutputExcel and existing pivot table

Hello
The sheets 1 and 2 are built on excel pivot table using data on the sheet 3 named "Data".

Pivot table? is it a function in Excel? TOS is a ETL tool, it only load the data into the appointed sheet, and it don't fires any function defined in excel.

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tFileOutputExcel and existing pivot table

Hello,
yes pivottable is a functionnality in Excel, I did not know until yesterday, it use the data in the sheet "Data".
It is a dynamic function, like Excel formula. For exemple I have create a formula in my sheet 1: =Data!G2+Data!G3 which is a sum of 2 cells in the sheet "Data", and after my data import the formula become =#REF!H2+#REF!G2 even if the sheet "Data" exists.
I think that the tFileOutputExcel component does not recreate correctly the excel file. Or do you think it's an Excel problem ?
Best regards
Frederic
One Star

Re: tFileOutputExcel and existing pivot table

Hello,
I have the same issue. The first sheet is "Summary" and second is "Data". "Summary" contains a pivot table (tableau croisé dynamique in french) and when I write records to "Data" sheet on which the pivot table is based on, the pivot table loses its references to this set of data. Of course, I checked append to existing file and append to existing sheet in tFileOutputExcel properties.
Regards.
One Star

Re: tFileOutputExcel and existing pivot table

Hi All,
I also have the same issue, any solutions about this? I have tried to use tfilecopy and the pivot table function can still be kept, please kindly have a check.
Thanks
Seven Stars

Re: tFileOutputExcel and existing pivot table

@fdaude: it looks like the "Data" is sheet is being deleted and replaced by Talend. The deletion causes the pivot table to break in the same way that a formula referencing cell A1 will break if you delete column A even though there is still a column A afterwards.
Try using the "Append existing sheet" and "Is absolute Y pos" options to overwrite the data on the sheet without replacing the entire sheet.
You should also be careful about exactly what data the pivot table is defined to use i.e. a named range or a data range and how you deal with writing more or fewer rows than previously existed.
PS: I haven't actually tried this.
One Star

Re: tFileOutputExcel and existing pivot table

Just tried even with appending it somehow destroy the pivot tables.
Anyone else have a solution?
One Star

Re: tFileOutputExcel and existing pivot table

For me it works like this:
I have a datasheet to which I append data as described above (Append file, Append existing sheet, Is absolute Y pos, X=0, Y=1). In the same Excel file I have several pivot tables referencing a named range. As the amount of data input into the file by Talend varies I have made the range dynamic, i.e. based on the number of cells in the first row/column.
The definition of my named range looks like this: "=OFFSET(Data!$A$1;0;0;COUNTA(Data!$A:$A);COUNTA(Daten!$1:$1))" (translated from a German version of Excel)
I hope this helps.