I am going to be looking into some 'wierd' log files and Oracle Tables with very strange date formats. I noticed that .NET has a ParseExact() function that appears to be able to handle these.. Quite simply, the Powershell code below shows both the translated date as a parseable object or converted to mm/dd/yyyy. Probably convertible to pure WB with the .NET CLR. My question concerns if the CLR code code be set up as a udf where I could pass in the ugly date format and a translated output.
If processing a long log file would the udf make more sense if the .net libs were opened prior to the call, or perhaps I should create a C# dll [where I would ask Jim for help] and call it each time.
Then the 2d question becomes can this be done in native WB w/out the ParseExcact() reference???
$uglyDate = '10 18 --- 2017'
$dt = [DateTime]::ParseExact($uglyDate, 'MM dd --- yyyy', $null)
$dt
dt.Tostring("MM/dd/yyyy")
I recommend searching for "Execute CSharp in Memory" and use that approach over the DLL. Does basically the same thing without the hassle of maintaining the DLL.
Jim
Guessing the WinBatch approach would be a bit of a hassle as well if you have a bunch of different formats and no good way to know which one is coming down the pipe.
Jim
There is nothing magical about ParseExact. It is implemented in mscorlib.dll so the C# source code for it is freely available. And since mscorlib.dll is loaded with the CLR you don't even need to load an assembly to use it directly in a WIL script. So you get:
objDateTime = ObjectClrNew("System.DateTime")
Null = ObjectType("DISPATCH",0) ; Creates a null pointer that the CLR understands.
uglyDate = '10 18 --- 2017'
dt = objDateTime.ParseExact(uglyDate, 'MM dd --- yyyy', Null)
;; Keep in mind that dt is returned as a VT_DATE variant, can be used accordingly
;; and will display in WIL YMDHMS format.
Message("Converted Date", dt)
Yeah...what he said :)
Jim
Should have mentioned that the WIL TimeFormat function can be used to convert a VT_DATE variant to and valid string representation.
So you can pass it something other than the YmdHms format?
Jim
No. A VT_Date variant is automagically converted to a YMDHMS date when you attempt to use the variable as a string. WinBatch has worked this way since February of 2004. Since the TimeFormat function is expecting a HMDHMS string date in its first parameter, the magic just happens.
As an update. After installing the latest version I looked at the timeformat() function and it exceeds expectations. I can now open the log files with uglydateformats and convert them to a string where I can extract text Monfth, text day, mm/dd/yyyy date. But, having to use the CLR I haven't tested whether the snippet below should be called at the beginning of the script or opened/closed within the UDF that parses the dates in the log file along with other 'fields' [i.e. as each line is read sections are collected, parsed and inserted into a local db table using ADO.
objDateTime = ObjectClrNew("System.DateTime")
Null = ObjectType("DISPATCH",0)
My money would be on opening it outside the loop.
Jim
Thanks for starting the discussion. I see some use in the future. Saves a lot of string manipulation.
objDateTime = ObjectClrNew("System.DateTime")
Null = ObjectType("DISPATCH",0) ; Creates a null pointer that the CLR understands.
uglyDate = '10 18 --- 2017'
dt = objDateTime.ParseExact(uglyDate, 'MM dd --- yyyy', Null)
month = TimeFormat(dt,"MM")
day = TimeFormat(dt,"dd")
year = TimeFormat(dt,"yyyy")
message("HEY",month:@CRLF:day:@CRLF:year)
Jim
If efficiency is important then it would seem reasonable to load the assembly and initialize the NULL reference once. On the other hand, if efficiency where not an issue then placing the assembly load and NULL reference in a UDF with the static method call may make more sense for maintainability reasons.
Quote from: td on October 30, 2017, 02:12:50 PM
If efficiency is important then it would seem reasonable to load the assembly and initialize the NULL reference once. On the other hand, if efficiency where not an issue then placing the assembly load and NULL reference in a UDF with the static method call my make more sense for maintainability reasons.
Yes, I would choose door number 1. But for a refresher - does the objDateTime need to be set to 0 before closing the script [like an ADO Object reference] or does it dissipate automagically?
WinBatch will clean up the object reference for you at process termination.
Quote from: td on October 31, 2017, 06:39:26 AM
WinBatch will clean up the object reference for you at process termination.
Good to know as I will follow this up with another of my [hopefully not obnoxious] Powershell thread.