Combining Multiple Excel Files

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?

Developer Tools

Input

Output

Macros

Accepted answers

gaoa

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:

alexnajm

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

All comments

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

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-Excel-file/ta-p/1241

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

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

ebledsoe22

@alexnajm Thank you so much! The article you provided was very helpful. I used the Medium section and my errors went away.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703