What if you client wants to have separate tabs for different months in the same excel spreadsheet? What if they want them in different excel spreadsheets? In the below blog I will review how to export both options.
In this workflow I am connecting to Sales Superstore data that is packaged with the Tableau application. (You can google it if you don't have it.) It is what I am most familiar with and I know it contains a date value spanning across multiple years.
In my first step I use the DateTime Tool in the Parse tab to pull out the Month and Year in this format : "MON_YEAR"
The Order Date column is already formatted as a Date so I select that format to convert to a string.
Next I aggregate my data using the Summary Tool to display the newly created Month_Year column along with the State column. I then select the Sum option for Sales and Profit.
Next I add the Formula Tool to add the following formula as a column called FilePath;
"C:\Users\dawnh\Documents\"+[Month_Year]+".xlsx|Sheet1"
This will pull the month and year as separate spreadsheets.
I filtered out the most current year with the Filter Tool so I wouldn't export too many spreadsheets. I then added a Select Tool to remove the Month_Year column.
When I finally added the Output Data Tool I formatted as below;
I had to select an excel spreadsheet from the drop-down and type out a name for the file. Then I checked the box at the bottom for Take File/Table Name From Field. I selected Change Entire File Path from the drop-down. I selected the FilePath column from the last drop-down for Field Containing File Name or Part of the File Name. Last I unchecked the box for Keep field in Output.
When I ran the workflow, it exported all of my sheets for 2020 to the C drive location.
Here is what the excel file looks like when I open Sep_2020.xlsx;
If you want to export your data on separate tabs of the same excel spreadsheet, you set up your Output Data Tool as below;
You will select the option to Change File/Table Name for the first drop-down option at the bottom of the Output Data Tool.
I also add a Select Tool right before this Output Data Tool and remove the FilePath formula. You don't need it for exporting your data to separate tabs in the same Excel workbook.
You will need to play with the sort of your data for this method using the Sort Tool. Alteryx will output your data in the order it is in the list.
I didn't sort the data and it appeared as below;
Notice that the April and August tabs are in the front.