Oh no! The Community appears to be haunted! Help us find all the spooky specters here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Using Render and output file tool to same excel workbook

jchan58
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?
38 REPLIES 38
DavidP
16 - Nebula
16 - Nebula

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
8 - Asteroid
You are right. Am using render tool to output first otherwise it will overide the worksheet generated by the output file tool
danilang
18 - Pollux
18 - Pollux

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
16 - Nebula
16 - Nebula

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

 

 

jchan58
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
DavidP
16 - Nebula
16 - Nebula

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

jchan58
8 - Asteroid

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

papalow
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