Issues using a batch macro to combine multiple files with different schemas

[jmcgra42]

I'm trying to combine about 50 different files with different schemas. They all have the same column names and field orders, but I suspect there's some issues with the data types. Each file has the same four identical tabs, but I only want to pick up one tab, which is named "right". I tried to create a batch macro, but I'm not sure how it implement it into my workflow. I attached what I'm currently using and samples of the files. Please help! 

[Luke_C]

Hi @jmcgra42 

Assuming all files have the tab named 'RIGHT', this is happening due to how you have your action tool set-up within the macro. The directory tool doesn't get the tab names, so just the full path is being passed with no tab name. 

Update it to 'replace a specific string' like so, and it should work (I deleted the |||RIGHT$' piece of it). 

[JosephSerpis · Accepted solution]

Hi @jmcgra42 you need to include a section where you specify which sheet you want your batch macro to work on through your different files. You can do this with a formula tool.

[jmcgra42]

Hello @luke_C l ! I removed the sheet name as you instructed, but now I'm receiving this error for the other files in the directory, "Error: macro 2 (3): Record #1: Tool #1: File not found "C:\Users\JMCGRA42\Documents\DE Sample.xlsx". Below is what my workflow looks like right now (main is on the left and the macro is on the right) Thanks!

[Luke_C]

Hi @jmcgra42 

It's working fine on my end after the change - please attach the macro.

Here's the macro with the change I proposed, try dropping it in and seeing if it works. 

@JosephSerpis 's suggestion should also get the job done. 

[jmcgra42]

Hi @Luke_C! I dropped it in, but it's only picking up file and all of that files sheets, instead of picking up every file and only the "right tab". Thoughts? Thanks so much!

[jmcgra42]

Hi @JosephSerpis! I addd the formula, but it's only picking up one file and all of the tabs within that file instead of picking up all files and only the "right" tab. Do I have to change anything in the macro? Thanks!!

[ArtApa]

Hi @jmcgra42 - You may want to read the following article: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi...

Also, I changed a workflow for you. You just need to map your Directory tool to the right path.

[AFANAKON1]

Hello colleagues,

Excellent instruction.

However, I have encountered a problem.

I am using a macro to merge multiple files, where new columns are added with the appearance of new questions in employee surveys. 

My issue is the limitation of the number of columns to 655.

Is there a way to remove this limitation?

Thanks in advance.