Alteryx Designer Desktop Discussions

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

Output File Name as field, merge into one column

TinaZhao
5 - Atom

I am using this macro to import every sheet from multiple excel files in a directory.

https://community.alteryx.com/t5/Community-Gallery/Read-All-Excel-Files/ta-p/897750

 

In the output, it will output sheet name as field, the final output will like below

 

column1  column2  column3  file1|||sheet1  file2|||sheet2 file3|||sheet3

  xx              xx            xx        file1|||sheet1  

  xx              xx            xx        file1|||sheet1  

  xx              xx            xx                                file2|||sheet2

  xx              xx            xx                                file2|||sheet2

  xx              xx            xx                                                   file3|||sheet3

  xx              xx            xx                                                   file3|||sheet3

 

 

I want the sheet name columns to merge into one column, something like this

column1  column2  column3  filepath

  xx              xx            xx        file1|||sheet1  

  xx              xx            xx        file1|||sheet1  

  xx              xx            xx        file2|||sheet2

  xx              xx            xx        file2|||sheet2

  xx              xx            xx        file3|||sheet3

  xx              xx            xx        file3|||sheet3

 

Is there any chance to do this, any help will be appreciated!

4 REPLIES 4
nagakavyasri
12 - Quasar
binuacs
20 - Arcturus

@TinaZhao Try the attached batch macro

binuacs_0-1686255121718.png

 

TinaZhao
5 - Atom

Thanks for the help. I am looking for sth more dynamic, cuz I might use this macro in different directory. Instead of manually selecting columns to transform, is there any other way to automatically merge these file path columns?

TinaZhao
5 - Atom

May I ask which column is "FullPath" in the macro? Which column does it correspond to?

Labels