Macro to import multiple excel files

[yilingchen02]

Hi there,

I have a folder called students and each excel file contains information of one student. Sth like this

Alice.xlsx  tab 'Sheet1'

Height 160

Weight 100

Bob.xlsx tab 'Sheet1'

Height 170

Weight 110

Charlie.xlsx  tab 'Sheet1'

Height 180

Weight 120

I would like to combine these excel files into one, see below:

Height 160 Alice

Weight 100 Alice

Height  170 Bob

Weight 110 Bob

Height 180 Charlie

Height 120 Charlie

Does anyone have a macro template that handles this? 

Thanks,

Yiling

[alexnajm]

You shouldn't need a macro if they are all the same structure and all have the same sheet name - try out a wildcard in the name, i.e. *.xlsx in place of Alice.xlsx in the Input Data tool

[yilingchen02]

Thanks. Would you be able to show a sample workflow? 

[alexnajm]

If you can pass on the data then sure! Otherwise the first option here goes over it: The Ultimate Input Data Flowchart - Alteryx Community entrée de données eingabedaten

[yilingchen02]

Thank you. Upon further checking, one file had one fewer column than the rest files. How to deal with this? I tried the Input Data Tool (first option) and the macro (third link), can't fix the issue. Error looks like this "Bob.xlsx|||'Sheet1$'" has a different number of fields than the 1st file in the set and will be skipped

[alexnajm]

Ah yes, if the structure differs then the batch macro option will work. Can you pass on the data so I can create a solution?

[yilingchen02]

Thank you. I attached the sample input file to illustrate. Please let me know if you have any questions.

[alexnajm · Accepted solution]

Here it is (edited)

[yilingchen02]

It worked! Thank you so much