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