Truncating .xlsx file with multiple sheets before overriding

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?

New Workflow2.yxmd

Output

Accepted answers

IraWatt

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

Workflow.yxmd

All comments

IraWatt

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

Workflow.yxmd

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.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235