Consolidating 100+ Excel Sheets with the Exact Same Format and Transform Data Afterwards

[mlu2422]

Hi Community,

I tried to use the Dynamic Input tool to input the same tab (exact same amount of columns and rows) from 100+ excel workbooks all at once. I configured to have the tool read a list of data sources from the field FullPath and it successfully returned me all the data stacking on top of each other. I want to consolidate with all the same tabs being next to each other and extend all the way to the very right similar to what a Join tool will give me.

My question is more on how data transformation will be achieved after using the Dynamic Input tool or if the Dynamic Input tool is limited to get to the way I want my final output to be like. 

An simple example of my input files and the desired output is attached(the example is for the state of AL and CT). And all the others files that I have are in this consistent format but they are for different states.

Appreciate if anyone has a solution for this! Thanks!

[afv2688]

Hello @mlu2422 ,

If you are joining using the same column in all the files you could build an iterative macro. Using the iterative input as to input to join join tool with the new file. After, using the filter tool, set a count and a ridiculous number to it to make your file always go back. The final output would never be reached (to make ir more simple).

Cheers

[danilang]

Hi @mlu2422 

Working off the macro idea from @afv2688, here's a solution to output what you're looking for

Main

The main program uses a directory tool to build a list of files, appends the sheet name to each full path and passes this to the Consolidate macro.  The output of the macro is passed through the Cross Tab tool to get one column per state.

Macro

The macro takes the list of paths and selects the 1st one.  The data from the file is loaded.  The top-most branch uses a field Info tool, coupled with a Sample and a Select tool to get name of the last field, which corresponds to the State name.  This is appended to each record from the input data in the Name Column. The State field is rename to Value and passed out the data output of the macro.  

The bottom most branch passes all the records except the first back to be reprocessed.  The Macro finishes when there aren't any more files to be processed. I set the the iteration limit to a "ridiculous" number of 1000 to ensure that all the 100+ files would be processed before the limit kicks in

The results after the cross tab looks like this 

Attached are the workflow package and the 2 sample files I created from your input data

Dan

[neilgallen]

@mlu2422 With all deference to those who have answered before, I think the solution is much simpler.

Assuming your files are named in a way that distinguishes the state (*"AL.xlsx" or something similar) you can accomplish the entire request using the dynamic input tool and the cross tab tool. 

Setup your input tool to include the filename as a field. From there, use the cross tab to transpose the data into the desired format, using filename as the column header and grouping your data by the initial column.

That should give you what you're looking for.

[mlu2422]

Hi Dan,

I have Alteryx Designer x64 and was unable to open your zipped workflow.

Anyway for you to reattach with the earlier version of Alteryx? Much appreciated if you are able to. Thanks!

[afv2688]

Hello @mlu2422 ,

If you want to open the workflow with an earlier version of alteryx just do the following:

Opent the yxmd file with an app like notepad or any similar editor.

On the first lines you will see the version of the program:

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2019.3">

This can be done given that all workflows are really xml files.

Just switch it to your version and done :)

Cheers

[neilgallen · Accepted solution]

I hate to chime in here again, but I really believe that this is being made too complicated. There's no need for a dedicated macro as far as I can tell. You just need one cross-tab tool.

This works if you include the filename as a field in your dynamic input tool.

[danilang]

Hi @neilgallen 

I do agree that your solution is much simpler, assuming that the file names do contain or can be mapped in some way to the state information.  However, @mlu2422 gave no indication that this is the case.  The other solutions just proceeded from the information provided in the post.

Dan