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?

[AkimasaKajitani]

Hi @pbobys 

The Excel files name are different, so the workflow doesn't work well.

You have to change the same file name at Output tool.

[AkimasaKajitani]

The chart is not shown, so I re-make the workflow.

Is the output is that you require?

[briantsmith90]

@DavidP would you be able to take a look at Pier's second question? That is the big issue I am also facing. Thanks

[gupta_sahil]

Hi David, While joining you used Sheetname field in join, can we use Table field in join instead of Sheetname?

[anupriyavats20]

Hi @DavidP I'm using your solution to solve a very similar problem myself! However, I need to create 5 sheets in total and the block until done tool only allows 3 input streams, is there a way to overcome this? The solution works perfectly and im able to create multiple sheets but what do you suggest if there are more than 3 sheets in total?

Thanks!!

[YMD]

You can use multiple block until done (9 outputs in this example)

[Rahul3]

Thanks DavidP. I was searching for the same solution. I was using the "Block Until Done" tool to output data in one tab and then moving to the next where I was outputting the another set of data to another tab of the same output excel above. But, I was getting the excel corrupted error when I was opening that excel.

I used your way and it worked. But, Instead of taking only one record of transactions table for rendering, I took the whole dataset of transaction table and removed the Join+Output combination. That is still working. Is there a reason that why you used the output tool separately to write the transaction table in excel output.

--

Thanks

Rahul

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

Thanks for your help.

[ShravaniChanda]

Hey, I have a similar requirement, you know i want to order my columns in the excel file, sheets coming from the output tools should be appear first, and Render tools output sheets towards the end. I tried giving numeric values in the tab names but it didn't work. do you have any suggestions.