Convert Date in TextFile using Powershell using a predefined format

Question

In the Code Sample below, how do I convert the time stored in [10/Mar/2021:03:27:29 +0000] to MM-dd-yyyyTHH:mm:ssZ format. The reason I ask is we need this data to be used in a downstream system where the data is stored as 03-10-2021T03:27:29Z. I was assisted by @712648 (Mathias Jensen) and he suggested to look at parsedate however, I am receiving issues using the same in the below code. The same parse date when run indivdually executes without any issues

Code

cls
$date = Get-Date -UFormat "%m-%d-%Y %H-%M-%S"
$BasePath = "C:\Temp\AWS_S3\S3Logs\"
$JsonOutFile = "$Basepath"+"JsonFile - $date.json"

#Combine TextFiles Into A Single Text File
$ConcatenatedFile = "C:\Temp\AWS_S3\S3Logs\CombinedFile.txt"

#Get-ChildItem $BasePath -File -Filter *.txt | gc | out-file -FilePath $ConcatenatedFile

#Build Columns for JSON file Key:Pair combination
$columns = 'Bucket Owner', 'Bucket', 'Time', 'Remote IP', 'Requester', 'Request ID', 'Operation', 'Key', 'Request-URI', 'HTTP status', 'Error Code', 'Bytes Sent', 'Object Size', 'Total Time', 'Turn-Around Time', 'Referer', 'User-Agen', 'Version Id', 'Host Id', 'Signature Version', 'Cipher Suite', 'Authentication Type', 'Host Header', 'TLS version'
$data = Get-Content -Path $ConcatenatedFile

$parsedLog = $data -replace '\[([^\]]+)\]','"$1"' |ConvertFrom-Csv -Delimiter ' ' -Header $columns
$date_format         = "yyyy-MM-ddTHH:mm:ssZ"
    $parsedLog | Select Bucket Owner, Bucket, 
([datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)), 
Remote IP, Requester, Request ID, Operation, Key, Request-URI, HTTP status, Error Code, Bytes Sent, Object Size, Total Time,Turn-Around Time, Referer, User-Agen, Version Id, Host Id, Signature Version, Cipher Suite, Authentication Type, Host Header,TLS version | ConvertTo-JSON -Compress
#$jsonFile #| Out-File -FilePath $JsonOutFile

#Remove The Concatenated File after JSON File is created
#Remove-Item -Path $ConcatenatedFile

Contents of CombinedFile.txt

fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:11:57 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe H3KK9B3EERPBZZN1 REST.GET.BUCKET - "GET /SampleS3Bucket?list-type=2&encoding-type=url&max-keys=1000&fetch-owner=true&delimiter=%2F&prefix=Test1%2F HTTP/1.1" 200 - 986 - 126 125 "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.964 Linux/4.9.230-0.1.ac.224.84.332.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.282-b08 java/1.8.0_282 vendor/Oracle_Corporation" - nH4rr0OG7OPS9WYZKyi4F0VcjffvyHpVhZgJGrzB99OtGBv8EqfdbKifFaREa1sFdxLMjU1GjoU= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader s3-us-west-2.amazonaws.com TLSv1.2
fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:13:32 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe 7F9VJTFGE993PZ0B BATCH.DELETE.OBJECT Test1/Debt+Dictionary_BASE.xlsx - 204 - - 71222 - - - - - Ya9Yoe23lAFZdY7grWdyD/RggdJq14T2E02enlt4qAlebHfsbeiG2JQ+qZP8CF/iNvCUuTpv7gY= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader s3-us-west-2.amazonaws.com TLSv1.2
fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:13:32 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe 7F9VJTFGE993PZ0B REST.POST.MULTI_OBJECT_DELETE - "POST /SampleS3Bucket?delete= HTTP/1.1" 200 - 177 - 63 - "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.964 Linux/4.9.230-0.1.ac.224.84.332.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.282-b08 java/1.8.0_282 vendor/Oracle_Corporation" - Ya9Yoe23lAFZdY7grWdyD/RggdJq14T2E02enlt4qAlebHfsbeiG2JQ+qZP8CF/iNvCUuTpv7gY= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader s3-us-west-2.amazonaws.com TLSv1.2
fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:17:40 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe GQ80J3H2EJVH13RS REST.GET.VERSIONING - "GET /SampleS3Bucket?versioning= HTTP/1.1" 200 - 113 - 17 - "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.964 Linux/4.9.230-0.1.ac.224.84.332.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.282-b08 java/1.8.0_282 vendor/Oracle_Corporation" - 96OcC7CN8VmIeJLY96PfWBNivqn6quBIZAYslV1kF2wZbKjYBdkn1N5aE0xbzZxyWUTDav0CT7A= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader s3-us-west-2.amazonaws.com TLSv1.2
fd89d80d676948bd913040b667965ef6a50a9c80a12f38c504f497953aedc341 SampleS3Bucket [12/Mar/2021:14:26:57 +0000] 117.222.211.65 arn:aws:iam::486031527132:user/jdoe 7G6TJ2NEKZYDR36M REST.GET.POLICY_STATUS - "GET /?policyStatus HTTP/1.1" 404 NoSuchBucketPolicy 307 - 9 - "-" "S3Console/0.4, aws-internal/3 aws-sdk-java/1.11.964 Linux/4.9.230-0.1.ac.224.84.332.metal1.x86_64 OpenJDK_64-Bit_Server_VM/25.282-b08 java/1.8.0_282 vendor/Oracle_Corporation" - 53JowWaGjViTyT0CCtzmzhoxh4WGKe8UcOkwv7Q/HlGVfStvPeDzIzlaFJsJpUujg2j5apQ9VBc= SigV4 ECDHE-RSA-AES128-GCM-SHA256 AuthHeader SampleS3Bucket.s3.us-west-2.amazonaws.com TLSv1.2

Error

Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime." At line:18 char:1 + $parsedLog | Select ([datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH ... + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : FormatException –

However the same code when the date is considered individually work just fine PARSEDATE code

$date = "12/Mar/2021:14:11:57 +0000"
$date_format         = "yyyy-MM-dd HH:mm"
[datetime]::ParseExact($date,"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)

Output 2021-03-12 14:11

Doug Maurer · Accepted Answer · 2021-03-16 19:25:35Z

1

I see a few issues.

First, You aren't using a valid calculated expression for your time property.
Second, an extra pair of double quotes are being added in the replace statement.
Third, there are property names with spaces that aren't being quoted.

I believe you should change these lines

$parsedLog = $data -replace '\[([^\]]+)\]','"$1"' |ConvertFrom-Csv -Delimiter ' ' -Header $columns
$parsedLog | Select Bucket Owner, Bucket, ([datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)), Remote IP, Requester, Request ID, Operation, Key, Request-URI, HTTP status, Error Code, Bytes Sent, Object Size, Total Time,Turn-Around Time, Referer, User-Agen, Version Id, Host Id, Signature Version, Cipher Suite, Authentication Type, Host Header,TLS version

to these

$parsedLog = $data -replace '\[([^\]]+)\]','$1' |ConvertFrom-Csv -Delimiter ' ' -Header $columns
$parsedLog | Select 'Bucket Owner', Bucket, @{n='Time';e={[datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)}}, 'Remote IP', Requester, 'Request ID', Operation, Key, Request-URI, 'HTTP status', 'Error Code', 'Bytes Sent', 'Object Size', 'Total Time','Turn-Around Time', Referer, User-Agen, 'Version Id', 'Host Id', 'Signature Version', 'Cipher Suite', 'Authentication Type', 'Host Header','TLS version

This can also be written in a manner that's easier to read and maintain.

$selectProps = 'Bucket Owner',
               'Bucket',
               @{n='Time';e={[datetime]::ParseExact($_.'Time',"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format)}},
               'Remote IP',
               'Requester',
               'Request ID',
               'Operation',
               'Key',
               'Request-URI',
               'HTTP status',
               'Error Code',
               'Bytes Sent',
               'Object Size',
               'Total Time',
               'Turn-Around Time',
               'Referer',
               'User-Agent',
               'Version Id',
               'Host Id',
               'Signature Version',
               'Cipher Suite',
               'Authentication Type',
               'Host Header',
               'TLS version'

$parsedLog | Select-Object $selectProps

answered Mar 16, 2021 at 19:25

Doug Maurer

8,9773 gold badges16 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NottyHead Over a year ago

Hi, This line $parsedLog = $data -replace '\[([^\]]+)\]','$1' |ConvertFrom-Csv -Delimiter ' ' -Header $columns gives the Time column as Blank/Empty. HOwever your code on $SelectProps made the code readable and I understood where I had bungled up including the spaces in ColumnNames Thanks for the help

Doug Maurer Over a year ago

That’s great news! Happy I could help.

Evan · Accepted Answer · 2021-03-16 19:42:03Z

0

$parsedLog | Select ($_.'Time')

Is returning all of the columns.

$parsedLog | Select {$_.'Time'}

Returns the time column.

Your code really doesn't like to use the curly braces where you are using them. I would suggest converting the time in $parsedLog by itself prior to retrieving all columns.

So do something like this first:

foreach ($log in $parsedLog){
    $log.time = ([datetime]::ParseExact($log.time,"dd/MMM/yyyy:HH:mm:ss +0000",$null).ToSTring($date_format))
}

Now you can select time just like the rest of the columns.

answered Mar 16, 2021 at 19:42

Evan

1111 bronze badge

Collectives™ on Stack Overflow

Convert Date in TextFile using Powershell using a predefined format

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related