Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Importing multiple excel files

Zaid
8 - Asteroid

Hi All,

 

I have found multiple threads with the issue but somehow i am unable to implement it correctly at my end. I would be glad if somebody could elaborate the steps.

 

The main objective is to import multiple excel files having one sheet in each of them. The column headers and the number of columns is identical in all the files. I want to bring data from all the excel files in one excel file. For example, lets say that I have data for for all months in different excel files. Now, i want them to combine all of them and bring them in one excel file.

 

Can you please elaborate, how this can be achieved using dynamic input and directory or some other tools.

 

Thanks

Zaid

14 REPLIES 14
LordNeilLord
15 - Aurora

Hey @Zaid

 

Can you share an image of your workflow how far you've got?

Zaid
8 - Asteroid

Hey  @

 

 

 

LordNeilLord
15 - Aurora

Hey @Zaid

 

First option (if all the files are the same and the sheets are the same) would be simply use the wildcard input function:  https://www.youtube.com/watch?v=1y1gFXDkYwA

 

If you want to go down the dynamic input route...try this blog: https://nalediholly.wordpress.com/2017/04/22/import-multiple-files-to-alteryx-with-the-directory-too...

 

If that fails (your getting schema errors) you'll need a batch macro to import the sheets: https://www.concentra.co.uk/blog/how-import-multiple-excel-sheets-and-files-different-schemas-altery...

TarabR
5 - Atom

I have tried to create my own macro using your instructions on https://www.concentra.co.uk/blog/how-import-multiple-excel-sheets-and-files-different-schemas-altery...

I was looking into this for a directory import i have with close to 20 excel files that have different schemas. I was able to create and run the macro with my directory, however, the output just shows the one file i had used in my macro stacked 20 times. I am a newbie and this is my first solo workflow. I would appreciate if you could point to what I am missing in being able to pull all my 20 files, instead of pulling one file 20 times.

Nordy_Bernard
6 - Meteoroid

I think there may have been some changes/configurations to Alteryx that are making some of these responses irrelevant. 

1. Wildcard function apparently doesn't work with MS Excel files

2. When I've taken the Dynamic Input tool examples and reconfigured to point to my files, results are only pulling in one of two files in my test folder. Both excel files are identical in schema and format.

3. There are some tools referenced and required in #3 below that no longer exist in Alteryx.

 

Any chance we can have the Alteryx team take a look at these walkthroughs. I think they're out of date and no longer relevant for users.

 

I've exported a dummy workflow to prove the point on #2. As an example.

DanM
Alteryx Community Team
Alteryx Community Team

@Nordy_Bernard, Your packaged workflow will not work since no one will have access to your directory. However, there are other ways to bring in data without having to worry about schema. You mentioned that your schema is the same and while most believe that to be true Excel doesn't. The work around in this instance is to create a batch macro that does not look at the schema but rather either looks at the position or names of the fields. It's actually one of the simplest macros to build and can be used at any point and can even be added onto for others to use.

 

Screenshot 2019-10-04 13.04.56.png

 

 

Replicate the tools in the pic above. Open the Interface Designer found in your VIEW and then select the gear. Choose either Configure by Name or Position. Save the macro but leave the workflow open. Go to your workflow you want to add the macro to. Right click on the canvas and add the macro to the canvas using the tool options

 

 

You can then deploy this macro and feed it your Directory Full Path field. Make sure you add a Browse tool after the macro to view ALL the data.

 

See attached sample macro

 

If you believe something is wrong with the tool, please contact our Support team to assist in troubleshooting.

 

DanM

Nordy_Bernard
6 - Meteoroid

DanM,

 

You're the man! Thank you very much for the help on this. The macro works and provides the expected results. I really appreciate the help as it was a bit harder to follow the other macro examples, but we're where we need to be now.

hellyars
13 - Pulsar

@DanM 

 

This is exactly how I had my macro configured and it was working just fine.  I tried a clean sheet macro using your template.  No joy.  I am now getting an error that says Record X "No Sheet Specified, you must specify a sheet".   But the sheet is specified in the sample provided in the macro -- and each file has the sheet.  

DanM
Alteryx Community Team
Alteryx Community Team

@hellyars couple of questions:

 

1. What tools are you using to input the macro with?

2. the field that you are using to update the macro, does that contain the sheet name?

 

In most cases with this macro, the user will use a Directory tool. You will want to confirm the field you are using contains the sheet name in the path. You may need to add the sheet name to the file path.

 

DanM

Labels