Append data from render tool to existing excel

[tanja90]

Hi all,

In attachment is the output excel file which is generated monthly (sheet Old_report). Data for next month should be append to existing file, and final report should look like on sheet New_report.

Is there a way how to do that?

Thank you for your time.

[HomesickSurfer]

Hi @tanja90 

Use the Output Data tool.

Configure the output file to reference a range like such: C:\temp\tanja90\final.xlsx|||'New_report$A:B'

Append to existing sheet by field name and preserve formatting.

Hope this works for you.

[tanja90]

Thank you for the reply @HomesickSurfer.

I tried this option before as well, and data are append but formatting is lost. 

[HomesickSurfer]

Hi @tanja90 

Can you form if you are appending to a range in the worksheet?  Formatting should not be lost if so.

FullPath + ||| + ' + Sheetname + $ + Range + '

eg.

C:\temp\tanja90\final.xlsx|||'New_report$A:B'

[tanja90]

Hi, 

yes, I put the range there but it doesn't keep formatting 😕

[HomesickSurfer]

Hi @tanja90 

If you are willing to share your workflow, I can review and determine why it isn't retaining the formatting.

[ViMx]

Hi Tanja, my experience with writing tables to excel is following:

• Render tool
  • creates always new file (I haven't found a way how to append rendered tables to existing file or sheet)
  • there is possibility to render multiple sheets (but from one layout, not as multiple write-outs...)
  • if I created rendered xlsx and try to write to it via standard output, it creates xlsx that seems to be corrupted (so render and after Outdata in the same file is not the way)
• Output data
  
  • is possible to append data in existing sheet or table
  • however table header is already formatted, newly inserted data are plain text only (formatting is loosed/overwritten)

So if I have to add some data to existing table, I can either add them without formatting (Output data) or read all data that are already in existing output, include them in workflow to data that should be added and render whole dataset in new excel.