community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Replace the Header row of all files within a folder by two rows from a different sheet

Meteoroid

Hello,

 

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.

 

example1.PNG

Alteryx
Alteryx

Hi @david_volkmann,

 

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:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Reporting/td-p/402953

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!)

 

Best,

Giuseppe

 

Meteoroid

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?

 

Capture.PNG

Alteryx
Alteryx

Hi @david_volkmann

 

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:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Finding-nearest-with-multiple-selectio...

 

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.

 

This article here also shows the concepts:

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-...

 

Hope this helps!

Giuseppe

Alteryx Certified Partner

Hi @david_volkmann 

 

I do not know if this is exactly what you need but it follows a workflow for your checking. Hope it helps in something.

 

Best Regards

Meteoroid

Final update: Workflow was scrapped as the render option can't hand high volumes of reports or heavy large data inputs.

 

Solution will be to rename all columns as to avoid the need for headers.

Labels