Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Combining similar Fields from multiple excel files

varindersra
5 - Atom

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

DATA 1.jpg                DATA 2.jpg 

 

                                    Combined DATA 1 & 2

      Combined DATA 1 & 2.jpg

 

Thanks in advance!

Varinder Singh

 

7 REPLIES 7
Bob_Blackey
11 - Bolide

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

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

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

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

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

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

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.

Labels