Add the filename to a table - for each record

[david_rogers]

I have daily data come in and need to make one of the columns the filename.  i.e. reach record in the table would have "todaysfile_02162015.txt" or whatever the file name is.  I can use "Field Info" to get the filename but then I can't link it back into the data so that every record has the filename.  Note: it's the same filename for all the records.

[AdamR_AYX · Accepted solution]

On the Inout Data tool under Options there is an option called "Output File Name as Field" which I think does exactly what you want.

Cheers
Adam

[david_rogers]

Excellent, wish it gave the extension also but I can work that out.  Thanks

[galdous]

I am not seeing an option under the InOut tool menu for "Output File Name as Field".    

[Aguisande]

Galdous,

I believe Adam is pointing to the Input Data Tool in the IN/OUT Category. See picture attached.

[mbarone · Accepted solution]

David,

If you choose "Full Path" (see below), this will give you the entire path.  You can then use reg ex tools/formulas to strip out just the file name.  I like to use the Text to Columns tool delimited on the "\".  That way I just use a select tool after to take only the file name field.  Saves from having to figure out the reg ex expression.

Here's an example:

Input tool:

Text To Columns Tool:

Select Tool:

Final result:

[galdous]

I see.  I was looking in the wrong place.   Thanks.

[mbarone]

You're welcome!

[PamPam]

This is a great solution when the input is a flat file. What if the input is a database file? I don't see the "Output File Name as Field" when reading a database file!

I'm using ODBC connection to read a table that is sitting in MySQL and would like to have the database name as a new field. The database name is the date that the file was crated, and I would like to add it to every row. Exaclty the same problem as David's, but with database file.

Thanks,

[mbarone]

Oh - yes, that's correct - the file name output option will not apply to ODBCs.  

In this case, create an extra stream coming from your input that connect to the Field Info tool.  Then use a select tool after that and choose only the "Source" field.  That will have your database name.  It might look ugly, so you may have to parse it out using Reg Ex or something like that.

Once you have that, you can just use an append tool to append that field to your entire dataset as an extra column.