Combining similar Fields from multiple excel files

[varindersra]

Hello Guys,

I am new to the Alteryx. I am looking to combine data from multiple excel files. See Images below. I am basically want to combine Data 1 and Data 2 to get a result same as Combined DATA 1 & 2.

 DATA 1                                                                                                   DATA 2

                 

                                    Combined DATA 1 & 2

     

Thanks in advance!

Varinder Singh

[Bob_Blackey]

Hi Varinder,

Welcome to the wonderful world of Alteryx.

You want to use the JOIN tool to get what you need. You would JOIN on the FIELDS column.  Note that in your output you cannot have two fields with the same name.

[Bob_Blackey]

Hi Varinder,

Welcome to the wonderful world of Alteryx.

You want to use the JOIN tool to get what you need. You would JOIN on the FIELDS column.  Note that in your output you cannot have two fields with the same name (DATA). Within the JOIN tool you can rename one of the fields.

There are quite a few resources available to help you learn how Alteryx works. The Alteryx website has a training section with short videos which I'd review. Also in Alteryx under the menu:

Help | Sample Workflows | Tutorials | Blending Data

there is an example of a join. I would review all the tutorials and as many of the other samples as you can. The best way to learn is do.

Cheers,
Bob

[SeanAdams · Accepted solution]

Hey @varindersra

There are 2 parts to your question:

a) How do I read multiple excels at once

b) how do I stitch them together into 1 table.

a) reading multiple Excels

This is where Alteryx is FANTASTIC - if you use the input tool, and instead of picking 1 specific excel file, you can use a wildcard (for example *.xlsx).   What Alteryx does then is to read every one of the excel files, and pulls them into one stream.

b) stitching them into 1 table

I've attached a workflow for you - and depending on whether you want this oriented by row or by column you can use either of the two outputs I've provided.

The transpose and cross-tab tools are tremendously powerful, and allow you to do a huge amount of data-restructuring very quickly - well worth learning these tools, and this may be an easy way to start playing.

If this answers your question - would you mind marking this as solved?  If you still have doubts or questions - feel free to reply to this thread with any additions or updates to the workflow attached, and with your open / remaining questions. 

Cheers @varindersra

Sean

[varindersra]

Thanks all for providing useful info. 

Sean: I was able to use your point b) and able to format the data the way I wanted. Thanks for that. But, I wasn't able to figure out how to use your point a). I do have 100's of excel files which I need to manipulate as per the workflow you provided. If I can import all data at once from multiple files that will be fantastic.

I was searching web on this and found that, there is some kind of add-in I need to install, to have this functionality of using wildcard. Is that correct?

Thanks in Advance!!

Varinder 

[SeanAdams]

Hey Varinder,

No need for any addins - in the filename section in the top-box you just take Filename.xlsx, and replace it with *.xlsx (assuming that the file format is the same).    If the file format is not the same, you can use a directory tool (to find the filenames with a wildcard), pump it into a dynamic input, and then union the results together.

if you're struggling with older Excel files - then there's a great pair of articles referred to by @PaulN here- that will answer your question exactly with walkthroughs, etc

https://community.alteryx.com/t5/Data-Preparation-Blending/Can-Alteryx-open-multiple-EXCEL-sheets-at...

Good luck Varinder - let me know if you're still stuck once you've worked through these for a bit?

[syatham1]

Hi can you post the same workflow in version 10.6 or may be if that's not possible a screen shot. when I tried to open it wouldn't open because it was created in most recent version and cannot be read. Thanks in advance.

[shreyanshrathod]

Hi @SeanAdams !!

I went through your solution for point b)

However, here you have two excel files so you used two different "Input Data tools" and joined them.

What if there are 20 such files ?

Note :- My requirement is the same as mentioned by @varindersra , but I have to provide the flexibility to the user to choose what files does he wants to read from a selected folder.