Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Batch Macro xls and xlsx files

KS
6 - Meteoroid

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.

8 REPLIES 8
alexnajm
18 - Pollux
18 - Pollux

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

alexnajm
18 - Pollux
18 - Pollux

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
12 - Quasar

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
6 - Meteoroid

@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
19 - Altair

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
6 - Meteoroid

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
15 - Aurora
15 - Aurora

@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
6 - Meteoroid

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. 

Labels
Top Solution Authors