Batch Macro xls and xlsx files
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Batch Macro
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What part isn’t working? Are you using the batch macro from the “ultimate input data flow chart”?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The solution I found for now, is to use 2 macros one for each format.
I'll see how I can improve that. Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
