tFileOutputExcel and existing pivot table

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[alevy]

@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.

[Anonymous]

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

[Anonymous]

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.

[Swechchha]

Had same issue. Did a bit more alongwith your so,ution and it WORKED for me 🙂

1. Create Sample Excel Workbook with Pivot sheet and a "data" sheet.
2. Select range of cells in your "data" on which you created your pivot and Name it using Name Manager in Menu.
3. Change formula of this Named Range in Name Manager to dynamic : =OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),COUNTA(data!$1:$1))
4. Right click on any cell in your PIVOT table sheet.
5. Click PivotTable Options and select Data Tab.
6. Add a check mark to Refresh Data When Opening the File. Click Ok.
7. Now in Your Talend Job, using tFileCopy , copy your Template in destination.
8. Using tFileOutputExcel overwrite your "data" Sheet simply how you do it always. (Append Existing File, not Existing Worksheet)(Is absolute Y pos)
9. Ta Da.😊