Output File Name as field, merge into one column

[TinaZhao]

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!

[nagakavyasri]

@TinaZhao 

[binuacs]

@TinaZhao Try the attached batch macro

[TinaZhao]

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]

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