This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am new to Alteryx and trying to generate an Excel output file for each entity below with two sheets. The sheet names will correspond to Schedules 1 and 2. I have an output data generated in the workflow as shown below. I have also attached a sample data file to the post.
The file path should be c:\Cycle18\CountryNameFrombelow\
The file name should be EntityCodefromBelow.xlsx
Sheet names should be Schedulenumberfrombelow i.e. 1 or 2
The workflow and macro worked exceptionally well. Do you know how I can add country as a sub folder?
In other words, the excel output files that belong to Belgium entities should be saved in the Belgium file folder.
Also, I got an error when I reran the workflow, which says that the sheet already exists. This is, I believe, caused by the output files saved from the previous run. Is it possible to overwrite previous files?
Ok, I racked my brains this afternoon to set up the solution for you.
I used Run Command Tool to create sub-directory with Country Name (also had to create a formula) and modified the Output Tool to Overwrite Sheet.
(You can see the macro got even bigger)
The Run Command Tool will create a bat file that creates the folder. So this bat file will be in the folder where you're creating your sheets. You can create another bat file to delete it after run, if you want (but that's not the focus now).
Solution appended. I hope it suits you. Have a nice week.
This worked as intended and I tagged the issue as resolved. I learned quite a bit from your workflow. Thanks a lot.
I had to fix one issue where country name contained a space e.g. South Africa. Run command created South and Africa folders i.e. two folder in this instance. I inserted a double quote before and after the country field to make this work ("South Africa"). Then the excel output file resulted in an error since it did not find folder that contains "South Africa" . Thus to fix the second issue, I removed the double quotes using FindReplace tool before the excel output file was created.
It took about 90 seconds to finish a small sample file, which is a performance problem given I have a large volume of entities. To improve performance, I am thinking about creating country sub-folders in one mass and separate it from the iterative macro since I know the list of the countries from my input file. Is it possible to pass multiple sub folders to Run command?
I am also thinking using the table and render tools to create an output file for single entity as part of the iterative macro. Couple of things I learned from my data providers is that the output excel files must have the same number of sheets and subtotal rows formatted to bold font. Please let me know if you have If I am not on the right track or if you have better ideas for improving the performance.