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

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!

Sample.yxmd

Input

Transformation

Accepted answers

neilgallen

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.

cross tab settings.png

All comments

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

1.xlsx

2.xlsx

Sample.yxzp

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!

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703