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 looking replace the first row of all files within a folder by the first two rows of a spreadsheet in a second folder.
In the example image below, the left sheet contains an example of the data present in the multiple files with no header, and the sheet on the right side, which is saved in a different folder, contains the desired header in the first two rows.
My major struggle relies on the fact that I can't circumvent the fact that the header row of all documents need a unique name for each column in order to end the workflow.
As a final note, there is no need for the header file to contain merged or coloured cells, but there is a need for one header to cover multiple cell ranged. In the example below, the title "Header1" in the right side sheet has to be the only text present in range A1:C1.
I would greatly appreciate any help or suggestions on what could be done.
To your point: "My major struggle relies on the fact that I can't circumvent the fact that the header row of all documents need a unique name for each column in order to end the workflow."
Alteryx use field names (or headers) as identifiers for the columns, so you are correct in the fact that you can't use the same name for 2 fields.
When it comes to renaming your headers with names coming from another files, I would separate the problem in 2 parts:
1. replacing the subheaders first. You can easily do this by using the Dynamic Rename tool. You'll find it in the "Developer" category (light gray one). If you left click on the tool on the ribbon > Open Example you can learn how to configure the tools in multiples ways.
2. Adding the formatting/merged cells by using the Reporting tool. I replied to a similar post with a sample workflow attached here:
In your specific case, after reviewing how to use the Reporting tools, I'd suggest you to create a formatted table for each of the Header1, Header2, Header3, ..., add the title on top and then use the Layout tool to align each table horizontally to recreate the formatting that you showed.
Hope this helps, but let me know if you have further questions.
(Please, tag me in your reply, so that I can get a notification when you do!)
Than you for your suggestion @GiuseppeC. I am familiar with the Dynamic rename function and will put that to use for the subheaders.
Regarding the Reporting tool, I have struggled getting it to apply the required format to all files within a specific folder. I can get it to work on a single file with the specific output selection, however I wasn't able to have it dynamically work on all files I want it to. Do you have any additional suggestions on what could be done, or perhaps on what I'm doing incorrectly?
Assuming that all files that you want to apply those transformations to have a similar schema, I'd approach the problem by working on a single file first and then converting this logic into a batch macro to automate the process on all files.
I have literally just described the concept of batch macros for another user on a different use case here:
In your specific case, once you've got your logic (with the Dynamic Rename and the Reporting tools to within to create a single output), I'd use the Directory tool to load the list of files that you want to transform and use this as an input of the Control Parameter input of the batch macro.