Is it possible to use Alteryx to delete sheets from an Excel file?

mix_pix · 2016-10-19T17:01:07+00:00

Hi, Here's my situation: * I have an Excel file that has two tabs - a 'Data' tab and a 'Pivot' tab * The 'Pivot' tab linked to the 'Data' tab and is set to auto-refresh upon open * I use Alteryx to update the 'Data' tab and re-save the file to a network directory * I use the Email tool to email the Excel file as an…

mix_pix

Hi,

Here's my situation:

I have an Excel file that has two tabs - a 'Data' tab and a 'Pivot' tab
The 'Pivot' tab linked to the 'Data' tab and is set to auto-refresh upon open
I use Alteryx to update the 'Data' tab and re-save the file to a network directory
I use the Email tool to email the Excel file as an attachment to a set of users

This process works very well, but I am now exceeding the 10MB limit of the Email tool, which means that while the Excel file is updated, Alteryx won't email the file as an attachment.

I noticed that if I delete the 'Data' tab (after the 'Pivot' tab has been updated), I substantially reduce the size of the Excel file (since Excel places the data into a pivot cache, which appears to be a very compressed version of the original data). Is there any way for Alteryx to do the following:

Update the 'Data' tab as it already does
Refresh the 'Pivot' tab
Delete the 'Data' tab and then re-save the file

Is this something that can be accomplished via the Run Command tool? I hope there's a way to accomplish this. :-)

Thanks,

-Mike

Output

Preparation

Accepted answers

All comments

dataMack

The 'doubling size' of data in excel has been an issue as long as pivot tables have been around. Luckily the fix is easy and you don't have to modify your Alteryx workflow.

In Excel, just click on the pivot table then go to Options and the 'Data' tab. Uncheck the firts 2 options and check the 3rd. This will prevent Excel from duplicating all your data into the pivot cache and will force the file to refresh the pivot directly from your data tab every time the excel file is opened. Nice and easy.

mix_pix

Thanks for the reply Jason. Unfortunately changing those settings only shaves a little size off of the Excel file. So I've been working w/ my team to call a VB routine from Alteryx that opens an Excel file, deletes the data tab and then re-saves it. If it works I'll post the solution here. :-)

cmcclellan

What is the pivot table used for - just to view or do the users actually interact (drill/filter/etc) with it ?

Bhavika

@cmcclellan

So, the users do all the traditional things to the excel pivot (drill down/ filtering/double click on cell and it redirects to the underlying data)

As per what i have read in the community, the best way is to Output data from Alteryx to the source data tab in the excel and refresh the pivot table in excel on opening. Now, the challenge i face is that i manually delete the historic data in the Source data tab before running my workflow and use the output tool (Append to existing option) to replace the Source data tab with new data. Now, i want that Alteryx deletes the historic data at the beginning of the workflow and the end product of my workflow is outputting the new data to the Source Data tab .

Could you please help me with this?

Thanks

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703