How to combine both formatted and unformatted sheets in a single xlsx file without issues?

Question

Hi team,

We have a business requirement to create an Excel report file (xlsx) with different sheets (both formatted and unformatted sheets in a single Excel file)

I have learned a method on Alteryx community to do so (please find workflow attached as an example to illustrate our issue)

a) See top workflow - when our business team filters for certain data and it is found, then the Output1.xlsx can be opened without any issues
b) See bottom workflow - when there are no results in one of the sheets ('Sheet DE'), and after Output2.xlsx is generated, the file becomes corrupted (unable to open file)

We need your expertise to answer a few questions from our end:
1) Is there any other solution to generate an Excel output file containing both formatted and unformatted sheets? Sometimes our datasets can be very large, so if we first need to convert every dataset to formatted version (then union them together and render to output, followed by overwriting with an unformatted version), it takes as long as 45mins to generate one Excel report end-to-end which is very time consuming.
2) If our workflow is the only solution available to combine both the formatted and unformatted sheets into one single Excel file, how can we tackle the scenario in point (b)?

Thanks!

Formatted_and_Unformatted_Sheets.yxmd

Output1.xlsx

Output2.xlsx

MattBSlalom · Answer

Here are a few more thoughts to consider...

* You could potentially remove your Block Until Done task and pass everything through a Layout & Render tool.  You mentioned wanting "Unformatted" data, but you could change the Reporting Table options to appear pretty close to an unformatted Output Data result.  These options are in the "Default Table Settings..." in the Reporting Table configuration.
* Another possibility depends on if you need the Sheet to exist when there was no data written to it.  If it's acceptable for the sheet to only exist when valid data rows exist, then you can add a Formula tool just before the Output Data tool that will create new dummy field set to either Null() or "" (empty).  Then, update the Output Data configuration to "Take File/Table Name From Field", choose "Append Suffix to File/Table Name", select the Null/Empty field from the new Formula tool, and finally Uncheck the "Keep Field in Output".  This will result in appending nothing to the filename when writing data that exists, and having no row to pull the field from when data doesn't exist so no write will occur.
* You could write the Formatted data & Unformatted data into separate files, then use a Run Command tool to execute something (like a PowerShell script) to copy the sheets from one file into the other to get a combined file.  A sample script is discussed here:  Copy Excel Worksheet from one Workbook to another with Powershell - Stack Overflow

And here's the PowerShell code for posterity:

$file1 = 'C:\Users\eric\Documents\Book1.xlsx' # source's fullpath
$file2 = 'C:\Users\eric\Documents\Book2.xlsx' # destination's fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb1 = $xl.workbooks.open($file2) # open target
$sh1_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook
$sheetToCopy = $wb2.sheets.item('Sheet3') # source sheet to copy
$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook
$wb2.close($false) # close source workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
spps -n excel

I'm not totally sure what your other question is asking.  If you're asking about how to execute just one possible data flow path, then I've only seen that possible through the use of Macros so that the configuration of some tool(s) within the Macro are updated before the Macro actually starts executing.

wuaw · Answer

Thanks for the reply. Just curious, is there any other method to combine both types of sheets into a single file?

Another question is, how do I set a rule (something like an if-else statement) in Alteryx such that it will proceed as normal if there's data in the sheet, otherwise it will write on the first row before proceeding further?

MattBSlalom · Answer

It seems like you could get around this issue by creating your Excel file with the Render tool, then in the next Block Until Done path, go ahead and create the other needed sheets with just 1 row of dummy data first.  Then your other paths to create the real sheets will just overwrite the dummy versions.