Delete an Excel Sheet if Dataset is Empty

[ricoo]

Hi All,

I have a workflow that outputs an exception data set for each column that has any problems. For example in the screenshot below, the dataset has 4 records of the Column "Customer Name" that has its value exceeds 40 characters. This dataset will then be outputted to an .xls file with the sheetname pointed to the "Column" field. This works fine, but the concern is that if this workflow will be run on the 2nd time having the dataset entirely empty, excel will retain the old version where it has the "Customer Name" sheet with these 4 records.

 

 

Is there a way in Alteryx to update the Excel file in a way that if the dataset doesn't have any record at all, then it will delete the corresponding sheet in Excel?

I also noticed that, when I select the Output Option "Overwrite File", the output excel file will only have one sheet. I believe Alteryx overwrites the sheet for every dataset.

Another example on the screenshot below. I have a different set of records for the Column "Type" dataset but with THE SAME structure as the above dataset ("Customer Name"), that is UNIONed to the output excel file. 

When the workflow is done running, Alteryx shows that all the sheets have been successfully created. 

 

 

But when you open the file, it only has one sheet.

[Felipe_Ribeir0]

Hi @ricoo 

This macro is able to delete files: https://community.alteryx.com/t5/Community-Gallery/Delete-Files/ta-p/1005983

I did not understand your full context, but it seems to me that using this macro combined with your current workflow you will be able to solve this.

[ricoo]

Hi @Felipe_Ribeir0 thanks for taking time to reply to my concern. I've now solved my problem, but still thanks!

[ricoo · Accepted solution]

Hi @Felipe_Ribeir0 , apologies if I wasn’t able to post the solution immediately. Here’s how I managed to do it.

I separated the process of accessing the .xls file.

The first process I did was to overwrite the file.

And another process to append new sheets. This way there’ll be no deleting of sheets because the spreadsheet will be recreated from a whole new file.