Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

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

simpleminded
7 - Meteor

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? 

8 REPLIES 8
TaraM
Alteryx Alumni (Retired)

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.

Tara McCoy
michael_treadwell
ACE Emeritus
ACE Emeritus

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
7 - Meteor

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
ACE Emeritus
ACE Emeritus

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
7 - Meteor

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
ACE Emeritus
ACE Emeritus

I begin with two xlsx files in a directory

 

Capture.PNG

 

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

 

Capture2.PNG

 

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

 

Capture3.PNG

 

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

 

Capture4.PNG

simpleminded
7 - Meteor

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
5 - Atom

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!

Labels