Dynamically change file name while preserving pivot table integrity

Hi Everyone,

I'm working on automating a report from different pulled data sources I have. My issue is that when I try to dynamically change the file name to include today's date, the overwrite also deletes my tab with my pivot table.

I initially started with just having the manipulated data export to an output sheet with the pivot table tab, as the pivot table would be able to reference against the overwritten data sheets because the fields and field names were the same. After, the sheet refreshes the pivot table when it closes and is ready to be attached to an email.

I only have 2 logical directions on how to over come this problem. 1: find out how to overwrite sheets and dynamically change the file name while keeping the pivot tab. 2: Take the an output file were the pivot table tab correctly references the overwritten data and have alteryx take the output sheet and run a dynamic name change.

If anyone has any ideas, that would be fantastic.

-R

SASDaily.yxmd

Workflow

Accepted answers

RCS

@patrick_digan @wjohndenham

Hey so I posted this question awhile ago and just saw the feed back. Since July 2018 I've found some crafty ways to automate reports using some of the things ill mention in this post, including solving the initial problem I asked about. I have recreated a sample workflow with some data about cars for example purposes.

Attached is a workflow in which 2 things are happening.

1. A "Batch Runner" is being executed which takes a template "Shell" file with a pivot table that points to the a data sheet which has no data, only formatted field headers.

-The template file is copied from a specified location and pasted with an attached date stamp in a file I designated "Finished Report."

2. A workflow that is transforming data and then being written to the data sheet in the date stamped copy of the template file.

-Since the file is date stamped, this process can be used to run a daily report without having to copy and rename output of an excel workbook generated by a workflow.

Things to Note:

>The template shell file has a pivot table using a "Defined Named" instead of a range to reference for data. A defined name is used because you can set it to be an offset formula that expands and contracts when rows or fields are added. (Making the reference dynamic)

This formula is used in the defined name *Data Source*: =OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),COUNTA(data!$1:$1))

Define Name.PNG PivotTableDSsetas'DataSource'.PNG

Then all you have to do is set the pivot table to be refreshed upon opening. And voilà, the report is automated taking into account changes in record lengths or added fields (if you are planning on overwriting the sheet instead of appending).

Once you have finished designing the pivot table with the data set, you go to the data tab where the data is being reference and delete all rows except your formatted field headers. You keep these because in the next step, you will be appending by field name and wont have to remove any formatting you put on those headers. Your shell file is now ready to be automated.

Next, follow the framework in the workflow I provided to execute a daily batch runner and then the workflow you want to populate results.

BatchRunner Plus Workflow.PNG

Take a look at the workflow and see what it is doing. The top part is a batch runner which copies the shell file into the empty "Finished Reports" folder so it may be appended by the second part of the workflow.

The second part of the workflow now has a file to write too because the output tool is set to use a field value as the directory. Luckily I was able to create a directory path with a date stamp so the output will point to the same shell file copied by the batch runner.

If cmd line and batch files dont make much sense, just think of them like this. Batch files (files with .bat extensions) are just instruction scripts that you are telling your computer to execute. In our scenario, the batch runner is writing to this .bat file the "instructions" to copy and paste a file we designated in the formula tool. The Run Command tool then can be used to point to a .bat file so alteryx can tell your computer "Hey!, follow these instructions. I want you to move a file located right here, make a copy, and paste it over there." You dont need to edit a batch file at all in file explorer as alteryx creates and writes the instructions to the .bat file based on what you tell it to do in the formula tool within your workflow.

I used this method for a lot of reports I used to do that I now automated and scheduled on the alteryx server so I can work on bigger and better things! Now I don't even touch the files, the workflows do all the work and produce a multitude of reports daily. You can also use any input tools like sql or a dynamic file drop to pull last modified file in a folder.

Also to mention, I use a block until done and a dummy join to ensure that the cmd line executes the .bat file first so that the transformation workflow has a destination to write to. This will not work in other scenarios because of alteryx's order of operations when using larger data sets. My solve is just to have a "Batch Runner" create copies of all the reports I need to run at once, schedule that at like 1am, then have my workflows run after. *You could probably even use a conditional runner* Using a the "Label Block Until Done" tool from Crew will allow you to run a workflow with up to hundreds of run cmds.

I will include all the files and examples, let me know if you have any questions.

Data.zip

All comments

patrick_digan

@RCS I would try something like the attached v11 workflow. Basically, I would use a run command tool to output your data (instead of the normal output tool). Then I just read back in the same excel file with only 1 record. Then I use a formula tool to create the bat script that copies the excel file and adds today's date. Then I use a run command tool to run the bat file that I'm creating.

Hopefully this points you in the right direction. As you've found out, when you write to the excel file and add the date in the name, it doesn't include your pivot table from your original file. The reason is that you're writing to a brand new file. It's not directly deleting anything, it's just writing to a new file that doesn't have the pivot tab from your original file.

Let me know if you have any questions!

Community 8-1-18.yxmd

RCS

im having a difficult time implementing this into my workflow. Is there any way I can get more extensive help?

wjohndenham

@patrick_digan thank you for sharing this. Would you mind unpacking this a little bit more? I definitely need to save a new excel file each time while preserving pivot tables in my workbook. If you could provide some explanation to the logic/use of each tool in the workflow that would be really appreciated.

mohitb52

You can use python tool in developer window.

you just need to use the OS.rename(source,destination) function in python and rename you current excel file to the desired file.

Also if you want to keep the old file also in the path then just create a backup of that file using copy method in python Shutil.copy(src, destination)

RCS

hey mohitb52,

Currently learning python and its wonders. its taking time though. I agree you can do something similar, but my idea was to cut out the middle man. The middle man being another piece of software.

The .bat script is native to windows and can be written in any text editor. Also you don't lose your template copy, its simply copied and pasted in a new location with an updated date stamp. Im developing a macro right now that I'll try to release that will help the analyst where a few templates are given as options (or choose your own based on an old report but if you do it will run more script to clean it up so that its ready for the solution method using dynamic naming in my method for pivot tables), you select the finished file location, select where to put the template, make a report directory, and have the option of invoking mass email with text you want to write to the audience. This would basically be a big replacement macro for any out put tool and would include localized creation and deletion of .bat files to preform its operations).

All thats left is designing your transformative part of the workflow and the data extraction part. Schedule that puppy, and now management gets their beautiful report on their own cadence.

I just worry about people getting spooked working with .bat files. If a distro went around and they didn't read the source code, it could be compromising. Perhaps a checker tool that will only pass parameters to the run tools "write function" if the directory is a subdirectory of c:\users\joe.shmoe\documents\* or just check to make sure it not writing anything that is a technical folder like appdata, temp, windows, program data, program files &(x86), windows, etc.

the end goal is to wrap this entire solution up into 1 macro, make it have better and easier functionality when selecting write locations , preserve safety while invoking 1 or more .bat scripts (there are evil people who solely manipulate these kinds of things to get access or do damage by sharing modified versions where malicious cmdline code is replaced inside the macro since many ), and have it saved in a generated directory specifically for that report

thuyduongnguyen

@mohitb52 Hi, thank you very much for your suggestion.

I wrote several lines of python code to change file name and it works.

My intention is to have the workflow gone through my excel file and create outputs. Then, in the end, the python code will change all files names at once. Therefore, I put this python tool at the 2nd anchor of "Block until done".

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703