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.

[PamPam]

Yep! that works perfectly. I just need to come up with the right RegEx pattern, which is not straightforward.

But, this is great! 

Thanks @mbarone

[mbarone]

Welcome!

What is the string you want to parse, and what to want the result to look like?

[PamPam]

Here is the string::

File: odbc:DSN=SuteBackups|Select netsuite_2015_m09_d21__10_07_s32.OPPORTUNITIES.* From netsuite_2015_m09_d21__10_07_s32.OPPORTUNITIES

and I need to parse it to:

2015-09-21

Appreciate that.

[mbarone]

Ah.  There's probably a million ways to do that, but I'd do it quick and easy and use a text to columns tool delimited by the underscore, with only three or four columns.  On the field with the m## and d## just take the right 2.

[PamPam]

Sounds like a great approach. I just implement it and works fine ;)

Thanks again. 

[mbarone]

Sure thing!

[bconard1]

How could the filename be extracted within a dataflow in order to insert into a filename field and / or part of the filename parsed as a new field value.  I am using the Directory input tool in order to 'loop through' multiple files and process various data / schema within the workflow and want to input the filename as a field value and part of the filename as field for all records processed.

[mbarone]

For each file you're looping through - are you reading in the data with a Dynamic Input tool?  In the configuration for the Dynamic Input tool template, you can select to add the file name to the data, just like you can in a regular Input Data tool.

[bconard1]

I'm using 'change the entire file path' as I'm inputting data from many files
"This communication, including any attachments, may contain confidential and privileged information that is subject to the CNX Resources Corporation Business Information Protection Policy. The information is intended solely for the use of the intended recipient(s). If you are not an intended recipient, you are prohibited from any use, distribution, or copying of this communication. If you have received this communication in error, please immediately notify the sender and then delete this communication in its entirety from your system."

[mbarone]

Right.  And you also have to give the Dynamic Input tool a sample file/template by clicking Edit:


And that causes a pop up that is the same as the configuration of a regular Input Data Tool, where you can bring in either the full path or just the file name as a separate field: