Truncating .xlsx file with multiple sheets before overriding

[gjunhao96]

Hi all, 

I have a workflow that looks like this where I output the data to seperate sheets in the same file using the [path] field. My issue is that, lets say I ran the workflow again, but this time removed the 3rd row (symbol). In my output file, only the num and alpha sheet are replaced, while the old symbol sheet is still in the output.

How can I sort of truncate the file, before I save the new outputs, to ensure that only the data found within the flow is kept?

[IraWatt · Accepted solution]

Hey @gjunhao96,

There are probably a few ways to do this. One way is to use the block until done tool:

The top output overwrites the entire file removing all pre existing sheets. Then Alteryx runs the second output which generates all your sheets.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[gjunhao96]

Thank you so much!

[flying008]

Hi,@gjunhao96 

You selected the option "Overwrite Sheet or Range", this set only overlay sheet data, but never remove exist sheet in xlsx file.

[Christina_H]

If you're outputting to an existing Excel file, you're not going to be able to delete sheets that already exist by just overwriting the other sheets.  Instead, you need to overwrite the whole file to get rid of the old sheets.  In this case, changing the output tool to overwrite the file won't work because then each individual sheet that gets exported will overwrite the others and you'll just end up with the last sheet.

I got it to work by adding record IDs and redirecting the first record to a separate output tool that overwrites the whole file, then adding sheets for the remaining rows.  I did need to use the CReW Wait A Second macro to prevent the outputs writing to the file simultaneously.