Hi - this may be too far fetched and probably out of my league here but is there a way to get part of the data file name ( in this case a csv ) in the datamapper and use it as a variable in the template. For example - I have a csv file name 142812.T.000999081.DEES.119784297972154_20191213081518.csv. I need to use the last 14 digits and convert that into a date field as such - Friday, December 13, 2019 08:15:18 AM EST
Thanks
In the Datamapper pre-processor, you have a Fixed automation properties named OriginalFilename. That gives you access to your original file name.
Then using javascript, extract the time stamp from it and convert it to date format. There is some manipulation of the string but you’ll manage 
Hi - I am still trying to work through this. I have this working but still working on output format. Is there a way to extract the date from the file name when it is not in a static position. For example one file name will be 142812.T.000999081.DEES.119784297972154_20191213081518.csv.
I need to extract the date which will always be the last 14 digits. But the last name ( DEES ) will vary in length. I realized after I created the following script. Still trying to manipulate so my output looks like
Friday, December 13, 2019 08:15:18.
Option Explicit
Dim myFile, newFileName1, newFileName2, newFileName3
myFile = Watch.ExpandString(“%O”)
newFileName1 = mid(myFile,46,2)
newFileName2 = mid(myFile,49,2)
newFileName3 = mid(myFile,42,4)
Watch.SetVariable “ReqDate”, newFileName1 & “/” & newFileName2 & “/” & newFileName3
Thanks
BTW, you could have done all this from within the Datamapper, no need of doing this in Workflow with VBScript.
Have you tried using REGEX?
I tried it with this: \d+.\w+.\d+.(\w+).\d+_\d+.\w+\g and it worked with the filename sample you posted. it returned DEES. Of course, this is if your filename has a fixed format, not in length but in what type of character we get, digits or alphabetical.
Thank you - I wasn’t exactly sure how to get it working in the Datamapper. I have done something similar like this in workflow, that’s why I used workflow.
Unfortunately I do not know REGEX. So, I haven’t tried it. I will try to get what you gave me above working though. Is there ant links that would help with REGEX. Always willing to learn something new!
Thank You
Hi - I need to get back to this with a little help. As indicated above I need to extract the last 15 digits of the file name only: 142812.T.000999081.DEES.119784297972154_20200515152357.csv to use as a variable date field in my template. I need the format to be Friday, May 15, 2020 hh:mm:ss (AM/PM) EDT. If this is even possible. I have tried several times to get this working I am trying to use REGEX but can’t get anything to work.
Thank you !
In the DataMapper, you can use code similar to the following to extract a new Date built from the file name:
// to use the current filename: var fName = data.filename;
var fName = "142812.T.000999081.DEES.119784297972154_20191213081518.csv";
var fDate = fName.match(/_(.*)\./)[1];
var newDate = new Date(fDate.slice(0,4),fDate.slice(4,6)-1,fDate.slice(6,8),fDate.slice(8,10),fDate.slice(10,12),fDate.slice(12))
newDate
Make sure the field type is set to Date. In the DataModel it will show up as a standard date, using your current Windows settings. Then, in the Designer, you can use all the standard formatting methods for date fields in order to have it displayed exactly as you want.
Thank you !!! I created a new field and called it ‘ReqDate’. Then I placed the code in there. I changed the field to a date field. Is this what i needed to do? I am not getting any results. Or do I need to use the preprocessor?
Thank You!
Sorry, I was missing one line in my previous post. I have amended it now.
That works perfect for the specific file name. Can I make it so it changes when the file name changes? The file name format stays the same. If I alter anything in the var fName I get errors in the other lines.
As the first comment in the script states, you can use the following code to use the data file name:
var fName = data.filename;
I did that and i get an error on the next line - Cannot read property “1” from Null. If I remove [1] I get errors on the next line - cannot call method ‘slice’ from Null
What’s the actual file name? Remember that while designing your data mapping config, the file name is a temporary file name. That probably explains why the RegEx doesn’t work.
The actual file name varies but the last 15 digits always represents the date. The example I gave above is an actual file. Attached is a screen print a couple different ones.
Yes, I know but the filename in the DataMapper GUI is a temporary name (something like inputdata.11198345628345.csv). Obviously, my code cannot work on that temporary file name.
So let’s try something else:
var fName = automation.properties.OriginalFilename
This will ensure that the DataMapper uses the file’s original name when Workflow launches the operation. In the DataMapper, you can set a default value for the OriginalFilename property through the Preprocessor task (in the Fixed automation properties section) . While you are designing and testing your DM process, it is that default value that will be used, but at production time, the file’s actual name will be used.
I am still getting the same errors as before when using var fName = automation.properties.OriginalFilename. I also set the default value in the preprocessor.
Can you post the latest version of the code you are testing, along with the default value you set in the OriginalFilename property?
Sure - I used 05/21/2020 and then I used Friday, May 21, 2020 in the Fixed automation properties section
var fName = automation.properties.OriginalFilename;
var fDate = fName.match(/_(.*)./)[1];
var newDate = new Date(fDate.slice(0,4),fDate.slice(4,6)-1,fDate.slice(6,8),fDate.slice(8,10),fDate.slice(10,12),fDate.slice(12));
newDate;
The OriginalFilename cannot contain a date! It must contain a filename that has the same format as the one you described previously.