Date time conversion format is changing in csv file when pasted on local desktop using PowerShell

Question

I have a below csv file which is coming from below command:

PowerShell Command: Get-Eventlog -Logname application -after ([datetime]::Today) -EntryType Error,Warning -ErrorAction Ignore | Sort-Object EventID | Select-Object EventID, EntryType,Timewritten, Message | Export-csv "C:/temp/input.csv"

Output file(input.csv) in csv:

"EventID","EntryType","Source","TimeWritten","Message"

"50","Warning","4/21/2021 12:03:38 AM","Description","2021-04-20 16:00:00.0","2021-04-20 23:40:00.0"
"50","Warning","4/21/2021 12:03:38 AM","Description","2021-04-20 16:05:00.0","2021-04-20 23:50:00.0"
"100","Error","4/21/2021 12:03:38 AM","Description","2021-04-20 17:00:00.0","2021-04-20 23:50:00.0"

Below is the output file(output.csv) when pasted on local desktop from remote server:

EventID,EntryType,TimeWritten,Message

50,Warning,4/21/2021 00:03,Description,00:00.0,40:00.0

50,Warning,4/21/2021 00:03,Description,05:00.0,50:00.0

100,Error,4/21/2021 00:03,Description,00:00.0,50:00.0

As you can see time format(in message field) is changing ( its showing in mm:ss.0 ) when csv file pasted from remote server in message column.

From 2021-04-20 16:00:00.0 to 00:00.0.

      **2021-04-20 23:40:00.0 to 05:00.0**

i need my output in this format like in Input.csv file. Please help how to control date time conversion format when pasted from remote to local desktop.

Answer 1

This isn't an answer, but the reason that your script is having trouble is how export-csv handles the datetime. It converts the datetime to powershell standard format.

Microsoft, is there a workaround for this?

Answer 2

I'm going to take a guess here and say that you've used Excel somewhere in this process.

If you started with this file (which, by the way has one too many columns and the last column doesn't have a column name in the header):

"EventID","EntryType","Source","TimeWritten","Message"  
"50","Warning","4/21/2021 12:03:38 AM","Description","2021-04-20 16:00:00.0","2021-04-20 23:40:00.0"  
"50","Warning","4/21/2021 12:03:38 AM","Description","2021-04-20 16:05:00.0","2021-04-20 23:50:00.0"  
"100","Error","4/21/2021 12:03:38 AM","Description","2021-04-20 17:00:00.0","2021-04-20 23:50:00.0"  

The Excel spreadsheet would look like this:

And if you exported that from Excel you'd get a file that looked like this (note the new "Column1" name in the header):

EventID,EntryType,Source,TimeWritten,Message,Column1  
50,Warning,4/21/2021 0:03,Description,4/20/2021 16:00,4/20/2021 23:40  
50,Warning,4/21/2021 0:03,Description,4/20/2021 16:05,4/20/2021 23:50  
100,Error,4/21/2021 0:03,Description,4/20/2021 17:00,4/20/2021 23:50  
  

If you used PowerShell to import the CSV and then export it (with no manipulation of the data by you) you'd get an exported file that looked like this:

"EventID","EntryType","Source","TimeWritten","Message"  
"50","Warning","4/21/2021 12:03:38 AM","Description","2021-04-20 16:00:00.0"  
"50","Warning","4/21/2021 12:03:38 AM","Description","2021-04-20 16:05:00.0"  
"100","Error","4/21/2021 12:03:38 AM","Description","2021-04-20 17:00:00.0"  

Note that the last column was NOT exported because it lacks a name in the header

So, clearly you are not just using "copy-and-paste" from one machine to another.