Data360 Analyze

I am using a template (excel file) that has some data and pivot already created by another team. I need to write new data to that excel file at the end so that existing data is not touched and pivot information can be updated. I am able to write new data using the updated excel as a template but it is writing at the top instead of at the bottom. Can someone please help in reversing the output fields.

Attached files

Test.docx

Based on the attached document, the OutputSpec property is filled in with a copy of the default example from the OutputSpecExample property. The example has the data append starting to the top of the first page - more on this below, along with the possible resolutions.

To break down the first line of the OutputSpec example, there are four variables:

<workbook>
<sheet inputIndex="1" outputIndex="1" dataStartRow="2" dataStartColumn="1"/>
...
</workbook>

• inputIndex - Specifies which input pin the data is coming from.
• outputIndex - Specifies which worksheet of the output Excel file the data is to be output to.
• dataStartRow - Specifies which row in the worksheet will contain the first record of the input.
• dataStartColumn - Specifies which column in the worksheet will contain the first input field.

If you want to append the data to a specific location on the Excel spreadsheet's first tab, then you can modify the dataStartRow and dataStartColumn values to the appropriate values. 

Alternatively, you can leave the OutputSpec property blank and it'll automatically append to the bottom of the first tab, starting from the first row below existing data and from the first column.

Both methods above have their benefits and drawbacks. Leaving OutputSpec blank will ensure that your data will always be at the bottom of existing data, which is good when the existing data varies in size. Using the OutputSpec with dataStartRow and dataStartColumn values tends to give better formatted results since you can append the data to precise locations. 

Thank you so much Gerard for the quick response but I have tried leaving "Output spec" blank so that data gets appended at the end but still the data gets added at the top instead of bottom in the excel. So not really sure what needs to be changed in specification properties

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Are you using Append Excel? If so, try just using the regular Output Excel. I find its easier to overwrite data than trying to append it.

1. Read in the data from your current file first.
2. Append your new data to it with a Cat node (feel free to sort or anything else to get it in the order you like) Make sure your new data in the second pin of the Cat node so it will be at the bottom of the new dataset.
3. Then write out the full data set to the sheet on your current file that is holding the data for your pivot table. Attached is a basic example.

I added one more step of clearing out the "data" sheet, just in case you are doing any other logic and end up with less rows on that sheet when you want to write to it.

Attached files

Excel with Pivot table.lna
FileInUseWithData.xlsx

Thank you Gerry for the quick code but the pivot is still getting distorted as the date is "15/02/2021" in this format and cant be changed to "2021-02-15". The moment I refresh on pivot after creating the file, the data gets displayed with distorted details in pivot.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

I presume you are talking about your file and the date format is not lining up? Does this approach seem to work for you outside of the date format not appearing as you wish? If so, here is what I have found to work for a file to maintain the format you want in this scenario. 

1. Open your template file (or in this case your actual file with the pivot table).
2. Highlight the column that has the date field in it.
3. Select Conditional Formatting -> New Rule.
4. Select "Use formula to determine which cells to format".
5. The formula you enter is "=TRUE" but with no quotes.
6. Pick your date format that you want and save the file.
7. Run the data flow again. I would expect this time that the date column you highlighted in step 2 will respect the date format you want.

I know its a bit of a roundabout way but I think it will solve your formatting issue. The same can apply to any number formatting issue or anything similar.