Batch Macro xls and xlsx files

[KS]

Hello, 

I need your help on how to use a batch marco to output from multiple files within multiple folders. 

So basicly, I have One folder for each month, and each month have multiple files, one for each region, the files can be either .xls or .xlxs

The files don't have the same columns. 

the output I need is to union all the files with adding a column to know the file name and so the region and also the month. 

I used a directory input and a tried a batch macro but it doesn't seem to work.

I hope it's clear and hope you can help me. 

Thanks so much in advance.

[alexnajm]

What part isn’t working? Are you using the batch macro from the “ultimate input data flow chart”?

[alexnajm]

Are you not seeing the files you want to read in? Make sure you select the correct folder and include subdirectories if needed in the Directory tool

Is the batch macro not working? Tell us what the error is

[KGT]

The best solution for this will be to create 2 different Batch Macros and use a filter to separate the xls and xlsx. Sheet names will be an issue if they differ.

This is a solution built several years ago to deal with it all together: https://community.alteryx.com/t5/Engine-Works/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-ALL-Exc...

CReW Macros also have a version of this.

[KS]

@alexnajm I get the following error and so it's not bringing data from the .xlsx files, only the .xls file, knowing the file used as an input in the macro is an .xls file

Batch Correct (4) Record #3: Tool #6: Error opening connect string: Microsoft JET Database Engine: External table is not in the expected format.\3274 = -328602519 - Unspecified error

@KGT thank you, I'll have a look

[apathetichell]

Are you dynamically adjusting the fileformat in your input data tool to toggle between xls and xlsx files? If not - you must do that. look at solutions to the 1000 file weekly challenge to see how to do that.

[KS]

The solution I found for now, is to use 2 macros one for each format. 
I'll see how I can improve that. Thanks 

[OllieClarke]

@KS this weekly challenge https://community.alteryx.com/t5/Weekly-Challenges/Challenge-180-Thousand-File-Challenge/td-p/461356 is a similar challenge to what you're trying to do. 

The file formats you need are:

xlsx - 25
xls - 8

Hope that helps
Ollie

[KS]

Thanks all, I tried to use the solution of the 180 challenge but it didn't work when adding the input file in the macro, and running the flow, I get the error to select a table. but I'm already selecting the table and the macro output shows the right data for that selected file. 

I'm still using 2 macros to get what I need.