Hi all, I have a very challenging project for my organization and this task is for those who love challenges, therefore I need help from my community members.
I want to consolidate many excel files into one, there are multiple excel files with different names I receive in a directory between15-20 files. Each file consists of several tabs, some have 1 some have 2 some have 3, but the catch is there is one tab which has common data in all the files, like the example the data consists of name, ID, Order, Date. The tabs also have different names, they are not similar, each file has different names of those tabs, Sometimes there are few files which don't have any data in it (Please Note this point) and Sometimes the data columns are not adjusted like other file data, the order sometimes change like Name, Order, ID, Date. Therefore I need to consolidate the data of these files into one in the output with the data aligned properly and One separate summary file for the points mentioned below. There are few important outcomes I want from Alteryx in this case.
1) Consolidation of files as per the order which is present in all the files and if some file have different order I want Alteryx to send a summary of how many files were not in order (this data to be sent in Summary file)
2) Summary of how many files have the data and how many files don't have the data (Names of the files).
3) Total Count of Names in the summary file.
4) The file should consist of the Date in the output (Consolidated File and Summary file), the date which is provided in the data.
Therefore there should be two files one for consolidation and one for Summary as mentioned above. The Summary file data should be in Table form for visibility. There has to different tables for different requirements (Individual Tables). Kindly help me in this, I know this is challenging but I want to get help from my fellow community members. Below I am attaching sample data which resembles my data. Hope this attached helps, ping me for additional requirements.
Solved! Go to Solution.
Hi. I am working a similar exercise where I was required to consolidate multiple files (tables) into one consolidated file (table).
To put this into a Alteryx workflow is straight forward.
Hope this helps.
See image snapshot of my workflow.
There's a couple of items in this that direct me to batch macros.
The files could change name. That means that you need to get the name each time. A folder browse is the easiest way to do this.
The tabs are inconsistent. This means that you need to get the tab names. This can be done either with a dynamic Input or a batch macro.
Columns can be out of order. This will be handled automatically to re-align them in most solutions. If you NEED to know the order they were in, then an extra process will be needed.
Multiple outputs for Summary and Data. This would be my recommendation anyhow.
So, most of the above can be achieved with the CReW XLSX macro available here. However, you lose customisability. If you don't want to build it yourself, then that is your best option.
Basically, the way that I would set this up to build it myself is:
After getting used to Batch Macros, they become a bit of a preference due to their versatility. The CReW XLSX Macro contains a couple of batch macros that do what I've said above for you. It sounds like you have little confidence in the metadata of the files coming through and so with a Batch Macro, you can even set the input to not assume headers and manually sort that out if you want or include a "Field List" to get the order of columns and add to your summary... That second batch macro is your key to designing the solution in the way that fits your situation.
@KGT could you please send me a sample wf of my requirement ?
Hi @the_data_guy Actually I have a directory, I can't have a single input tool for every file because the number of files is dynamic, everyday new file comes and the count of files is not similar. Could you help me in this and if possible could you please send me a wf attachment.
Hi @Kaish
You can use a combination of the Directory too, filter tool, and dynamic input tool to read dynamic files from a directory. See attached snapshot of this simple wf I put together to demonstrate the concept.
When you setup the initial nodes in Alteryx you will understand how to configure each node. There is also an example wf in Alteryx when you search for Dynamic Input to help you understand how to construct your logic to read dynamic files from a directory.
Hope this helps.
@Kaish
find the workflow attached
mark done if solved.
Hi @Raj, this wf will not work because my sheet names are not similar, Could you create the workflow with the data provided above ?
@Kaish Find the updated.