Run Command to delete data from excel sheet
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi There,
I have been trying to follow posts in the community for the below scenario but, couldn't figure out anything.
I have an existing excel with 6 data tabs and 2 pivots tables. I have been successfully been able to develop a workflow to have the data tables replaced and the Excel refreshes pivots on opening. But, before i run the workflow i manually open the excel and delete historic data in the data tabs. The output tool configuration i used is "Append to existing" . This way, i get the data out of my workflow into the Data tabs(which has been cleared of data but has headers intact) in the excel format that the file originally has.
Challenge: Is there a way to open the excel and delete the historic data, re-save and close it through Alteryx. So, that i can use this before i Input the data to my existing workflow and process the output. This'll save a lot of my time.I know this can be probably be done using Run Command. But, not sure how to do it.
Can someone help me how to achieve it?
Thanks
- Labels:
- Run Command
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can use PowerShell commands via the Run tool to manipulate Excel sheets. Example site is https://sqlnotesfromtheunderground.wordpress.com/2014/01/25/modifying-excel-documents-with-powershel...
However, I don't understand why you can't edit the Alteryx workflow to delete the records first and then repopulate with the new data. I've attached a rough example
Cheers
G
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The user wants it to work that way. The historic data should be removed by Alteryx.
I did try my luck with Run Command. So, i created a VB script (saved as .vbs). The file runs fine and deletes the data on the Data tabs, saves and closes the excel. So, i know that the script has no issue when ran without Alteryx. However, when i run it through Alteryx Run Command tool i get an error which i have no clue about. Below is the screenshot of my workflow and the error.
Below is the configuration of the Write Source under the Run Command
I have been struggling to understand why is Alteryx errors out when the VBS file runs fine.
Please help if you can :(
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
ok - if it needs to be vbs then take a look at this thread where @jdunkerley79 has solved the issue and has posted an example
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Run-VB-script/td-p/64153
Cheers
G
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @GrahamL
I checked your overwrite solution and it did help me with what I needed to do so thank you so much! :)
This might be a very basic question but what does the 1=0 actually mean in the filtering tool?
I can see what it's doing but I don't understand why it works.
Thank you in advance :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi GrahamL
I had the similar issue and this workflow solved the problem. Thank you for the flow.
But have a question what is 1=0 in filter?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
If you click a cell and then press DELETE or BACKSPACE, you clear the cell contents without removing any cell formats or cell comments. If you clear a cell by using Clear All or Clear Contents, the cell no longer contains a value, and a formula that refers to that cell receives a value of 0 (zero).
Regards,
Rachel Gomez
