Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Using Render and output file tool to same excel workbook

Highlighted
8 - Asteroid
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?
Highlighted
14 - Magnetar

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.

Highlighted
8 - Asteroid
You are right. Am using render tool to output first otherwise it will overide the worksheet generated by the output file tool
Highlighted
17 - Castor
17 - Castor

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

14 - Magnetar

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

 

 

Highlighted
8 - Asteroid
If output tool complete first with sheet 2, Render tool will wipe out sheet2 completely by overriding workbook with only sheet 1 creation
Highlighted
14 - Magnetar

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.

Highlighted
8 - Asteroid

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

Highlighted
8 - Asteroid

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

Highlighted
8 - Asteroid

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

Labels