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

Collating Multiple Excel Data into one single Excel File

ananth7358
6 - Meteoroid

Hi All,

 

I got stuck in below given workflow where i am trying to read multiple excel files and collating in one single file. I'm facing an issue where it is not looping through all the files. Only the same file iterated twice and loaded in output file, its not reading the data from other file.

 

Below are the screenshots for the same. Kindly help me if there are any configuration that i have to modify to make it work and read from multiple excel files.

Batch Macro - Action ConfigurationBatch Macro - Action ConfigurationInput Data - ConfigurationInput Data - ConfigurationOutput messageOutput message

Regards,

Ananthakrishnan S.

13 REPLIES 13
Aguisande
15 - Aurora
15 - Aurora

Hi @ananth7358

I'm reviewing your workflow (and re-doing it to help).

I'm curious about the Select Records tool you have within the batch macro.. What is it for? How is configured?

 

Aguisande
15 - Aurora
15 - Aurora

Hi @ananth7358

I'm attaching a workflow and a macro that read files based on your sceenshots.

As you can see, reading the excels directly and through the macro, returns the same.

 

ananth7358
6 - Meteoroid

Thanks Aguisande for your help.

Unfortunately am using version 10.6 hence i couldnt able to open your workflows. Kindly send me the screenshots so that i can try the same here with my version of alteryx. 

Select Tool am using to skip first 5 rows from excel file based on my scenario.

Joe_Mako
12 - Quasar

On thing that is missing is the sheet name needs to be added to the path before the macro. You can use a formula like:

 

[FullPath]+"|||`Sheet1$`"

 

Attached is an example of this.

 

Building from @Aguisande work, you can skip records with the Input tool options as well.

 

If you need to load multiple sheet from multiple workbooks, take a look at this video:

https://www.youtube.com/watch?v=Kl-yM-eB4a4

 

You can download the example files at:

https://community.alteryx.com/t5/Inspire-2017-Buzz/Inspire-2017-sessions-available-on-demand/m-p/711...

 

This will give you a baseline to start with, and you can customize it as needed. They were made in v11, so you can use the steps at to adjust the version number so you can open them: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versio...

 

ananth7358
6 - Meteoroid

Hi Joe,

 

I tried your workflow but still i couldn't get my desired output. Below is the error message i got it. Files are there and i verified many times. Kindly help on this.

 

 error.PNG

vishwa_0308
11 - Bolide

See what i feel is the sheet name must be different in those excel files which you are trying to consolidate..every sheets with different names in a same file will be taken as new file source.

Joe_Mako
12 - Quasar

Looks like you have a typo in your formula expression, is should be exactly:

 

[FullPath]+"|||`Sheet1$`"

 

Notice the individual character details and placement. Maybe try copy/paste instead of retyping, or opening the workbook attached in the prior message.

 

Yes, this expects that the sheet you want to load from each Excel file is named "Sheet1"

 

If you want to deal with sheets of different names from multiple Excel files, please see the links and video in the prior message.

ananth7358
6 - Meteoroid

Thanks Joe !!! It worked now by changing FileName= [FullPath]+"|||`Sheet1$`" instead of Fullpath=[FullPath]+"|||`Sheet1$`"

 

Also i tried with .xls files and the same workflow is not working now. Is there any other changes i need to do apart from directory tool.?

Joe_Mako
12 - Quasar

The Input tool will need the FileFormat adjusted.

 

Attached is an example macro and workflow that uses it.

Labels