Using Render and output file tool to same excel workbook

Am getting excel error when I try to open the output file generated by both render and excel output file, saying its corrupted and repaired.

The output file consists 2 worksheets, one of the worksheet using output file tool has large data set (30 columns x 300k rows), the other sheet with small data set is output from Render tool.

I could have used render tool for both sheets, but file size will be enormous.

I try to use parallel block until done to ensure both sheets wont override at the same time.

Weird the workflow complete with no issue but Getting that excel error message. The output file is showing empty data for one of the worksheet. Anyone encounter something similar?

Output

Accepted answers

DavidP

Ok, I came up with a solution that works. Have a look at my example below - change the output paths to something on your local machine.

I have a summary table and a transactions table (image 100,000 rows in the transactions table). I create the output for both tabs using report, layout and render tools, but include only one line of data for the transactions table.

The block until done only lets the join tool (with a join condition that will never produce a join) pass the transactions data to the output data tool once the excel file is created with both tabs (I use this trick sometimes when I want to hold data at some point until another part of the workflow is complete). The output data tool then overwrites the transactions tab with the full list of transactions and there is now corrupt file error.

Let me know if this works for you.

render and output data to same excel.png

render and output data to same excel.yxmd

All comments

DavidP

Hmmm, that's pretty clever! I never thought of using Render and Output Data tools together like that to reduce the file size and processing time.

Which one are you writing to first? The Output Data tool is probably more adept at writing to an existing file than the Render tool, so I'd suggest writing to the Render tool first.

jchan58

You are right. Am using render tool to output first otherwise it will overide the worksheet generated by the output file tool

DavidP

Hi @Rahul3

The reason we used the Output Data tool t write the transactions is to reduce the file size - If you use the Render tool to write 100s of 1000s or rows, the file gets very big. So using the Render tool to create the summary sheets and the Output Data tool to write the transactions, greatly reduces the file output file size.

The problem you encounter when creating a new sheet with the Output Data tool in a file created by the Render tool is that you get the corrupted error as you said, but if you create the sheet with the Render tool and then just append data to it with the Output Data tool, that error is avoided.

Rahul3

Thanks David. I do not have much data and in future also the file would not have a lot of data, So I have sticked to your solution (removing the output tool only) and using the Render tool to get the output.