Using Render and output file tool to same excel workbook

[jchan58]

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?

[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

[danilang]

Hi @jchan58

In your last message you say this "Am using render tool to output first otherwise it will overide the worksheet generated by the output file tool".  Aren't you specifying a separate sheet name for each output? i.e. Render = ...filename|||sheet1, Output=...filename|||Sheet2 

Dan

[DavidP · Accepted solution]

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.

[jchan58]

If output tool complete first with sheet 2, Render tool will wipe out sheet2 completely by overriding workbook with only sheet 1 creation

[DavidP]

I agree, but the block until done and join tool prevents that from happening, so the render tool will complete its business and the output tool will then write its data.

[jchan58]

If output tool complete first with sheet 2, Render tool will wipe out sheet2 completely by overriding workbook with only sheet 1 creation

[jchan58]

Brilliant idea and it works perfectly. Learn some tricks today.. thanks for sharing.

[papalow]

@DavidP

This is a clever idea. 

I tried it today and I am able to Render sheets of data and a separate sheet to hold the transaction data.  However, when I open the file I get the "we found a problem with some content..." message.  My output tool is writing the file to a share drive and it is overwriting the transaction sheet.  Have I missed something?

Thanks.