I am trying to combine multiple excel files into one output file

[Edgar_Ryan_C]

Dear Experts,

I am humbly asking for help. I tried using directory input to import multiple excel files (wildcard). Is there a way to export these files into a single output file but in different sheets according to its filename? Assuming each files have different format.

[Qiu]

@Edgar_Ryan_C 
How about an Input Data Tool?

[Edgar_Ryan_C]

@Qiu

This looks better. Can you send the .yxmd file? It is not working on my 2019.4 version.

[Qiu]

@Edgar_Ryan_C 

Here you go.

[Edgar_Ryan_C]

@Qiu 

It is working with the files you sent (Test1 and Test2). However, not the case for my files. I have attached my excel files, each is different from the other. Was it because each file has a different data? For example, A B C on FS001 then G H I J on FS002.

Thank you!

[ArtApa · Accepted solution]

Hi @Edgar_Ryan_C - You will need a batch macro to read files with different schema. I created a quick sample for you:

Please note:

-For a new sheet to be created in the output file, your Alberta files must have some values. That's why as you can see above I added 1's.

-This workflow only reads "Sheet1". You would need to create one more batch macro to read all files with all sheets before combining them into one output.

Hope that helps.