Combining Multiple Excel Files

[ebledsoe22]

Hello,

I have multiple excel files with the same schema that I need to combine into one excel file. I want each sheet within the combined excel file to represent each separate file. I also need a sheet that totals up all of the values on the subsequent sheets. Can this be done in Alteryx?

[gaoa]

Hi @ebledsoe22 

You can use the Directory and Dynamic Input tool (look at sample workflow under Dynamic Input Example). Once combined into one sheet you can use Formula tool to add a column that totals the values.

[ebledsoe22]

Hi @gaoa,

Thank you for your response. What do I put for "File Specification" within the Directory Tool? Right now I have "." Should I keep it this way? When I run my workflow, nothing appears in the results panel, so I may have set it up incorrectly. 

[ebledsoe22]

I fixed the File Specification section, now I can see the meta data of the files I want to combine. However, when I try to add on the dynamic input tool, it says that my files can't be found. What do I choose for the template?? I have a template available, but it doesn't seem to be working. 

[gaoa · Accepted solution]

Hi @ebledsoe22 

First you would need to make sure the excel files are named in identical structure, for example,

FileName
CO Store File - North.yxdb
CO Store File - South.yxdb
CO Store File - West.yxdb

Then use the "File Specification" below. Asterisk (*) stands for a "wildcard" that brings in multiple files with the same file name structure.

Then you would want to configure the Dynamic Input tool like this:

[ebledsoe22]

For the Input Data Source Template, what file am I using? Am I using a blank template or one of  the files I am trying to combine? I think this is where my error is coming from. I wasn't sure what information to provide. Each of my files follows the following naming convention, Year UnitID "Tas Import Draft" Date. Do I need to change my template file name to follow the same naming convention? In my file specification I have *Tas Import Draft*.

[alexnajm]

@ebledsoe22, you can use any existing file that has the structure of the files you are trying to read in. In @gaoa's screenshots (I agree, the Sample Workflow is very helpful here - look at the sample workflow under Dynamic Input), North was used but any one of the files (South, West, etc.) could be used as long as the template matches.

[cagriffin32]

Yes, it can be done. You can use the output tool to output to different sheets. It would be simple to summarize the data and include that also. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Output-to-multiple-sheets-within-an...

[ebledsoe22]

Thank you all for your responses. I have added in one of the import files as a template, but now I am still getting an error. It asks me to select a sheet, but I already have. 

[alexnajm · Accepted solution]

Hi @ebledsoe22,

Look at the Medium option as an example and add a Formula tool with the sheet name beforehand - The Ultimate Input Data Flowchart - Alteryx Community entrée de données eingabedaten