Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Batch Macro - Including File Name When Importing Multiple Excel Files with Extra Lines

BarryJT
7 - Meteor

Hi- I'm sure this is discussed somewhere and probably a simple fix but my head is spinning from trying to find the correct solution...  I am importing multiple Excel files with different columns, different schemas and inconsistent extra number of lines above the header column.  This is only the second or third batch macro I've built and while I've gotten most of it to work, I still have one issue. While I do get a consolidated output from the Excel files with, for the most part, accurate column headers, the file name of the Excel files, which I want to be included in a SINGLE column are showing as separate columns for each Excel file; with only the file used as template in the macro having the correct header of FileName; the other Excel files are in columns with the file name as the column header. I have provided two screenshots - one shows the macro and the other the workflow using the macro with the problem columns highlighted in yellow. I've also attached compressed file with my sample workflow, macro and data files.

 

How do I get a single column for the file names instead of the ugly mess I am returning in my workflow?

 

Thanks for the help,

Barry

Macro Screenshot.PNGWorkflow Screenshot.PNG

 

3 REPLIES 3
AngelosPachis
16 - Nebula

Hi @BarryJT ,

 

Go into your batch macro and configure your dynamic rename tool so the Filename column is unselected

 

Screenshot 2021-09-17 071140.jpg

 

If you now run the workflow, you should see Filename under a single column

 

AngelosPachis_0-1631859155627.png

 

That happened because whilst for other columns named F1, F2 and so on you want to get the column headers from the first row of data, your filename column created by your input tool will be named Filename so once you get the first row of data as column header it will take the actual value, rather than maintaining the header.

 

Hope that helps,

Angelos

 

mst3k
11 - Bolide

1) your select tool at the end is renaming "Terminations July" to "FileName". This only happens when the macro is processing the July file - when it processes the August file, there's no field named Terminations July, instead it's named Terminations Aug, due to the Dynamic Rename tool you have ahead of it. Now you have a field called FileName and a field called Terminations Aug, and the Union results in what you see here

 

2) you don't really want to rename the "FileName" field, that would solve your problem if you could just avoid that, right? then you'd have a consistent "FileName" column in both datasets that would union together correctly. so.. all you have to do is uncheck FileName from your Dynamic Rename tool. That should do the trick, as well as REMOVE the select tool that's doing the literal rename for just "Terminations July".

 

mst3k_0-1631859308565.png

 

mst3k_1-1631859378056.png

 

 

BarryJT
7 - Meteor

Thank you! That was a quick fix...  

Labels