Outputting Raw data and Tables in the same Excel workbook

[CamarilloBrillo]

Hi there, 

I've been running into an issue trying to route both tables (via Render) and raw data (via Output data) to the same excel file. I've tried a few different ways of getting it to work but none of them seem to do the trick. The closest I've gotten is by using a "Block until done" where the Render gets produced first and the Output data second. In the resulting excel file I can see all the tables as expected. I can also see a sheet for the Raw data (via Output data), however the sheet is empty. When I open up the file I get a warning from excel saying the following: "Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)".

I've since tried reshuffling the "Block until done" with less success, as I don't even see the raw data sheet being created in the excel file. 

Also tried to turn the raw data into a table, however due to the relatively large dataset (c. 60k records) It's taking way too long to produce (>10min) and the formatting is all over the place. 

Does anyone have any suggestions how this can be resolved? 

Attached are a couple of files showing a simplified dummy reproduction of the issue I'm facing. I've also attached a snip of the simplified workflow. 

Thank you in advance!

Camarillo Brillo

[geraldo]

@CamarilloBrillo 

make the following correction:
Place a layout tool
change the orientation option as in the print
click on table
and put the name of the sheet
and exclude the recording of excel via output because the recording of excel in output with render tool are incompatible

[ChrisTX]

If you think the error may be related a to file write-contention issue:

See my two replies in this post:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Clear-the-output-file-before-r...

1) attachments:
          Excel Output different formats to multiple Sheets in one file.yxmc
          Excel Output different formats to multiple Sheets in one file - Sample workflow.yxmd

2) How to configure multiple copies of the CReW Parallel Block Until Done macro

And if you'd care to add a Like to this Related idea:
   Enhance options for Excel output

   https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Enhance-options-for-Excel-output/idi...

Chris

[CamarilloBrillo]

hi geraldo - thanks for the quick response! Unfortunately that doesn't solve the issue I'm facing. I am aware of the requirements to have the excel sheet with the table renamed to a specified string. What I'm struggling to do is the following:

- Have 2 sheets in the final output file

- 1. to contain the render output - i.e. the table

- 2. to contain the unedited data - i.e. the same data as the original input file

- I'd like to have both views side by side in 2 separate sheets in one document

Thanks for helping out!

[danilang]

Hi @CamarilloBrillo 

The trick here is to let the Render tool write both sheets

Use a Table tool on both sheets, add the sheet names and union the results.  Use a Layout tool to configure the output to multiple sheets

Group By the [sheet] field.  Pick Vertical with Section Breaks to get each table written to a separate tab in Excel.  Use the [sheet] field as the section name as well.

Configure the Render tool to write to a specific file.  Your output will show on two sheets and since the Render tool is outputting both, there won't be any corruption issues that arise from using an Output Tool

Dan  

[CamarilloBrillo]

hi ChrisTX - Thanks for providing the info. Not really sure how I can best utilise it.. I'll have a read through to see if I can figure it out.

[CamarilloBrillo]

hi danilang - thanks for the suggestion. I've tried that option as well but doesn't seem like an optimal solution. Reason is two-fold: 

1. The workflow takes a lot longer to run (c. 5min as opposed to 15sec) 

2. Can't get the formatting to work as required - the raw data sheet in my report has around 60k rows and 40 columns. If I stick to the default table formatting the raw data table sheet gets truncated. If I use a custom size - I can get the raw data table sheet to look okay, but the table columns in all other sheets are way too wide. 

Open to any suggestions on the latter point as well. 

Thank you!