Collating Multiple Excel Data into one single Excel File

[ananth7358]

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 ConfigurationInput Data - ConfigurationOutput message

Regards,

Ananthakrishnan S.

[Aguisande]

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]

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]

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]

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]

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.

 

[vishwa_0308]

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 · Accepted solution]

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]

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]

The Input tool will need the FileFormat adjusted.

Attached is an example macro and workflow that uses it.