Unable to get the date output proper format in the csv

[ganapa2000]

Hi,

I am trying to get the Date Information from multiple VMs, I am getting the date but when I open the output csv file, the date is not showing half info but when I open the csv file in the notepad, I am seeing the output but there is a closing (") double quote in the next line

Please help!!

$code = @'
"### Output ###"
("$(Get-Date)", "$(Get-TimeZone)") -join '::'
'@

$report = @()

Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row | Where {$_.OS -match 'Microsoft'} |
ForEach-Object -Process {
$sInvoke = @{
VM = $_.Name
GuestCredential = $Creds
ScriptTYpe = 'powershell'
ScriptText = $code
}
$result = Invoke-VMScript @sInvoke
$dummy, $out1 = $result.ScriptOutput -split '### Output ###'
$out1 = $out1.TrimStart("`n`r")
$report += $row | Add-Member -MemberType NoteProperty -Name 'DateTime' -Value ($out1) -PassThru
}
$report | ft -auto

[LucD]

Why the double quotes around those cmdlets?
and the '### Output ###' line?
You could just do

$code = @'
(Get-Date), (Get-TimeZone) -join '::'
'@

$report = @()

Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row | Where {$_.OS -match 'Microsoft'} |
ForEach-Object -Process {
$sInvoke = @{
VM = $_.Name
GuestCredential = $Creds
ScriptTYpe = 'powershell'
ScriptText = $code
}
$result = Invoke-VMScript @sInvoke
$out1 = $result.ScriptOutput
$report += $row | Add-Member -MemberType NoteProperty -Name 'DateTime' -Value ($out1) -PassThru
}
$report | ft -auto

[ganapa2000]

Hi LucD,

The closing quotes after the date is coming in next line because of which the csv is showing the date in incorrect.

ideally the closing quotes should be same line, how can I get that ?

[LucD]

Did you try the code I just posted?

[ganapa2000]

Yes, I tried, still the same

$code = @'
(Get-Date), (Get-TimeZone) -join '::'
'@

$report = @()
Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row | #Where {$_.OS -match 'Microsoft'} |
ForEach-Object -Process {
$sInvoke = @{
VM = $_.Name
GuestCredential = $Creds
ScriptTYpe = 'powershell'
ScriptText = $code
}
$result = Invoke-VMScript @sInvoke
$out1 = $result.ScriptOutput
$report += $row | Add-Member -MemberType NoteProperty -Name 'DateTime' -Value ($out1) -PassThru
}
$report | ft -Wrap -auto
$report | Export-Csv -Path ".\Date_Info.csv" -UseCulture -NoTypeInformation

[LucD · Accepted solution]

I added the option to remove every CR and LF in the string

---

$code = @'
(Get-Date), (Get-TimeZone) -join '::'
'@

$report = @()
Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row | #Where {$_.OS -match 'Microsoft'} |
ForEach-Object -Process {
  $sInvoke = @{
    VM = $_.Name
    GuestCredential = $Creds
    ScriptTYpe = 'powershell'
    ScriptText = $code
  }
  $result = Invoke-VMScript @sInvoke
  $out1 = $result.ScriptOutput.Trim("`n`r")
  $report += $row | Add-Member -MemberType NoteProperty -Name 'DateTime' -Value ($out1) -PassThru
}
$report | Format-Table -Wrap -auto
$report | Export-Csv -Path ".\Date_Info.csv" -UseCulture -NoTypeInformation

[ganapa2000]

Thank you very much...that worked perfectly 🙂