Add file name as a column in excel for a directory of files

[simpleminded]

Hi all,

So I am able to add the file name as a column in Alteryx for 1 file by using the input tool and selecting the option "Output File name as Field" to file name but I have a directory of 100+ files and I want to do the same thing on all those files. I need help in understanding how do I do that? 

[TaraM]

You can bring in the entire directory of files using a wild card (*) in the path (C:\directory\*.csv) of the input tool. When the "Output File name as Field" box is checked, all the records will carry the name of the file they came from in that field.

[michael_treadwell]

The Alteryx Input tool accepts wildcard inputs

For example, C:\Users\username\Desktop\files\*.xlsx is a valid entry into the Input tool configuration.

Using this in combination with 'Output File Name as Field' should work for you

[simpleminded]

Thanks TaraM but that will disturb the structure of the data will it not? Is there a way where I add the file name column to each file so that when I open each of those files it will have a column with the file name?

[michael_treadwell]

Are you looking to read in all xlsx files, add a column to the sheet, and the output back to the same sheet?

If so, @TaraM's solution will work in combination with the 'Take File/Table Name From Field' option in the Output tool.

[simpleminded]

I think I am not clear enough in what I am trying to do:

Problem: I have a folder with 10 files. In each of those files I want to add a column with the file name

Desired output: 10 files, each file having a column with the file name

Current output: Based on TaraM and Michael_treadwell suggestions I now have 1 excel file with n sheets (n = number of files) each sheet having the content of the file and the file name.

So I got the desired output but instead of having n files I now have 1 file with n sheets. (n = number of files)

[michael_treadwell · Accepted solution]

I begin with two xlsx files in a directory

I read them in using wildcard input making sure to include file path as a column

Then, in my output tool, I use the Full Path to write the files

And I end up with two workbooks, same name, with a new column containing the file location

[simpleminded]

This VBA code did the trick of separating n sheets in n files

Thanks guys!

Sub Splitbook()

Dim xPath As String

xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False

Application.DisplayAlerts = False

For Each xWs In ThisWorkbook.Sheets

    xWs.Copy

    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"

    Application.ActiveWorkbook.Close False

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

[Cher2018]

Is there a way to do this without the VBA code?  I'm using a batch import to a directory and when I select Output Filename as Field, the first filename is added to all records from all files.

Thanks!