Batch Macro xls and xlsx files

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.

Batch Macro

Accepted answers

All comments

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-Excel-Macro/ba-p/9988

CReW Macros also have a version of this.

@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.

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

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703