Dynamically add a Sheet (Table) into Many Excel Files using Alteryx
Background
While dealing with multiple excel files, there was a requirement to output a reference table into multiple excel files on a periodic basis. The reference table itself would change periodically and the multiple excel files, each containing several other tabs, were subjected to independent changes. The multiple excel files were located in a single directory for better accessibility. In short the requirement was to retain the original formatting and contents of multiple excel files after appending the reference table to each file on a periodic basis.
How To...
At first, the requirement may be difficult to comprehend, however a step-by-step approach would eventually meet the end goals. We begin the workflow by adding the reference table and a “Directory Tool” to read the existing files as shown below. In the “Select Tool” we will select just the full path for the directory and omit all other fields.
In the next step, edit the full path of the directory using a “Formula Tool” and add a sheet name (such as: Ref_Table) to the full path. For excel files, the sheet name should be appended with a separator ‘ ||| ’ as shown below:
In the next step add a “Append Fields” tool to link the reference table with the full path. Note, if there are more than 16 records being appended then an error may occur, just change the settings for this tool to allow all appends. This is an important step, as it will replicate the reference table per excel file.
In the final step an output tool is used to deliver the results as expected. The output tool is configured so as to specify the output directory and file name. Example: C:\test.xlsx|||
Choose the option to “Preserve Formatting on Overwrite (Range Required)” to retain the existing excel format. Choose the option to “Take File/Table Name from Field” and “Change Entire File Path” followed by choosing the field name from the dropdown option as shown. Set this workflow for auto-execution on a periodic basis to fulfill all the necessary requirements.
Summary
This blog explains how to dynamically add a table to multiple excel files.
Alteryxの導入なら、クラスメソッドにおまかせください
日本初のAlteryxビジネスパートナーであるクラスメソッドが、Alteryxの導入から活用方法までサポートします。14日間の無料トライアルも実施中ですので、お気軽にご相談ください。
