Remove double quote and break line from Powershell-Script

[SaqiChangx]

Hello,

here we go again,

situation: I am trying to develop an Automation-script, which should remove the double quote and avoid the break line. If I open the CSV file with Notepad++.

Problem: below you see the example.

$report = [System.Collections.ArrayList]@()

Write-Host "Gathering VM statistics"

Measure-Command{

foreach($vm in Get-View -ViewType Virtualmachine) {

    if (!$vm.Config) { continue }

    $vms = [PSCustomObject]@{

      VMName = $vm.Name

      Hostname = $vm.guest.hostname

      OS = $vm.Config.GuestFullName

      IPAddress = $vm.guest.ipAddress

      Boottime = $vm.Runtime.BootTime

      #PoweredOff = ""

      VMState = $vm.summary.runtime.powerState

      UsedSpaceGB = [math]::Round($vm.Summary.Storage.Committed/1GB,2)

      ProvisionedSpaceGB = [math]::Round(($vm.Summary.Storage.Committed + $vm.Summary.Storage.UnCommitted)/1GB,2)

      MemoryUsage = $vm.summary.quickStats.guestMemoryUsage

      Datastore = $vm.Config.DatastoreUrl[0].Name

      CreatedDate = $vm.Config.CreateDate

      Notes = $vm.Config.Annotation

    }

    $Report.Add($vms) | Out-Null

}

}

$Report | Export-Csv -Path C:\Users\Administrator\Downloads\report_$(Get-Date -Format 'yyyy_MM_dd').csv -NoTypeInformation -Delimiter ";"

$csv = 'C:\Users\Administrator\Downloads\report_2020_06_04.csv'

(Import-Csv $csv -Header "VMName", "Hostname", "OS", "IPAddress", "Boottime", "PoweredOff",  "VMState", "UsedSpaceGB", "ProvisionedSpaceGB", "MemoryUsage", "Datastore", "CreatedDate", "Unit", "To_Archive", "Responsible_Team", "Backup_Strategy", "Notes") | % {

$_.Value = $_.Value -replace "`r`n",' '

  $_ } | Export-Csv $csv -NoTypeInformation -UseCulture | ConvertTo-Csv -NoTypeInformation | % { $_ -replace '"', ''} | Out-File $csv

In this script, I want to export some values in CSV first and after that, the double quotes should be removed from the CSV file and there should be no break line. but i'm stuck and don't know how to compare the date of the exported file and make changes.

another solution is:

Get-Content C:\Users\Administrator\Downloads\report_2020_04_06.csv | Foreach-Object {$_ -replace '"', ''} | Out-File C:\Users\Administrator\Downloads\report_Test.csv -Force -Encoding ascii

but again the same problem, the script runs daily with Task Scheduler with Date. (report_$(Get-Date -Format 'yyyy_MM_dd').csv).

[LucD]

I can understand replacing the double quotes by single quotes, but why do you want to remove the <CR><LF>?

The resulting file will become one long string of text.

And how do you want to join those strings together?
Note that the resulting file can not be read by Import-Csv anymore.

To replace the double quotes and remove the <CR><LF> you can do

((Get-Content -Path C:\Temp\test.csv |

ForEach-Object -Process {

    $_ -replace '\"',''''

}) -join '') | Out-File -FilePath C:\Temp\test.csv

Also, I don't understand what the question about the date in the filename is about.

Do you want to target the file for a specific date?

[SaqiChangx]

The Exported CSV File will be used in .Net so that is why it is important to remove to avoid error.. If you see the above code I used Delimeter ";", so my output: VMName;Hostname;OS;IPAddress;Boottime;VMState;UsedSpaceGB;ProvisionedSpaceGB;MemoryUsage;Datastore;CreatedDate;Unit;To_Archive;Responsible_Team;Backup_Strategy;Notes If you look at the code above ($Report | Export-Csv -Path C:\User\Administrator\Downloads\report_$(Get-Date -Format 'yyyy_MM_dd').csv -NoTypeInformation -Delimiter ";") the exported CSV file has the date and the same CSV file should be in Get-Content -Path C:\Temp\test.csv |, but the file Report_Date.csv gets a different date every day. I hope i explained it.

[LucD]

The delimiter is not a problem, but how will you join lines together.
Where you now have (simplified)

VMName;Hostname;OS

VMName;Hostname;OS

VMName;Hostname;OS

will that become

VMName;Hostname;OS VMName;Hostname;OS VMName;Hostname;OS

My question, what will be placed between those lines? Also a semi-column?

Your filename question is not clear to me.
What are you trying to do?

Find and work with a file for a specific date?

[SaqiChangx]

Let me explain with the Code. I just have recently added your given code in my script and it is working. and I remove the -join because the output is totally rough.

Actually I used the wrong expression (break-Line).

I wanted to avoid new-Line, if the Text is long enough, it should not be in the second line.

$report = [System.Collections.ArrayList]@()

Write-Host "Gathering VM statistics"

Measure-Command{

     foreach($vm in Get-View -ViewType Virtualmachine) {

    if (!$vm.Config) { continue }

$vms = [PSCustomObject]@{

            VMName = $vm.Name

            Hostname = $vm.guest.hostname

            OS = $vm.Config.GuestFullName

            IPAddress = $vm.guest.ipAddress

            Boottime = $vm.Runtime.BootTime

            #PoweredOff = ""

             VMState = $vm.summary.runtime.powerState

             UsedSpaceGB = [math]::Round($vm.Summary.Storage.Committed/1GB,2)

             ProvisionedSpaceGB = [math]::Round(($vm.Summary.Storage.Committed + $vm.Summary.Storage.UnCommitted)/1GB,2)

            MemoryUsage = $vm.summary.quickStats.guestMemoryUsage

            Datastore = $vm.Config.DatastoreUrl[0].Name

            CreatedDate = $vm.Config.CreateDate

            Unit = ""

            To_Archive = "" 

            Responsible_Team = ""

            Backup_Strategy = ""

            Notes = $vm.Config.Annotation

     } }

          $Report.Add($vms) | Out-Null

} }

$Report | Export-Csv -Path C:\Users\Administrator\Downloads\report_$(Get-Date -Format 'yyyy_MM_dd').csv -NoTypeInformation -Delimiter ";"

((Get-Content -Path C:\Users\Administrator\Downloads\report_2020_04_07.csv |

ForEach-Object -Process {     $_ -replace '"', '' })) | Out-File -FilePath C:\Users\Administrator\Downloads\report_checked.csv

Your Question about Filename:

1: The script runs daily and creates a report file with the current date and after it has been checked and double quotes within the script have to be removed and also new lines have to be avoided if the text is long.

2: Get-Content ...... does this (removes '"') if the -path is correct, otherwise it did not work.  from the exported -Csv CSV file ........ (report_date). Because the report gets a new date every day.

[LucD]

Since you marked your last answer as the Correct Answer, I assume your issues are solved?

[SaqiChangx]

Sorry It was mistakenly happened 😛

[LucD · Accepted solution]

I'm still not sure what exactly you mean by removing the line-feed.

For the reportname, why don't you store the name in a variable.

Then you could do everything in one script.

$report = [System.Collections.ArrayList]@()

Write-Host "Gathering VM statistics"

Measure-Command{

     foreach($vm in Get-View -ViewType Virtualmachine) {

    if (!$vm.Config) { continue }

$vms = [PSCustomObject]@{

            VMName = $vm.Name

            Hostname = $vm.guest.hostname

            OS = $vm.Config.GuestFullName

            IPAddress = $vm.guest.ipAddress

            Boottime = $vm.Runtime.BootTime

            #PoweredOff = ""

             VMState = $vm.summary.runtime.powerState

             UsedSpaceGB = [math]::Round($vm.Summary.Storage.Committed/1GB,2)

             ProvisionedSpaceGB = [math]::Round(($vm.Summary.Storage.Committed + $vm.Summary.Storage.UnCommitted)/1GB,2)

            MemoryUsage = $vm.summary.quickStats.guestMemoryUsage

            Datastore = $vm.Config.DatastoreUrl[0].Name

            CreatedDate = $vm.Config.CreateDate

            Unit = ""

            To_Archive = ""

            Responsible_Team = ""

            Backup_Strategy = ""

            Notes = $vm.Config.Annotation

     } }

          $Report.Add($vms) | Out-Null

}

$reportName = "C:\Users\Administrator\Downloads\report_$(Get-Date -Format 'yyyy_MM_dd').csv"

$Report | Export-Csv -Path $reportName -NoTypeInformation -Delimiter ";"

((Get-Content -Path $reportName |

ForEach-Object -Process {

     $_ -replace '"', '' })) |

Out-File -FilePath $reportName

[SaqiChangx]

Thank You Sir, you are amazing.

It is working I already tried with variable too, but something went wrong.

This question with new-line (Line breaks). The long Text breaks. like here in Example

ES-MYR-IE-MFS;ES-MYR-IE-MFS.tgwdev.internal;Microsoft Windows Server 2016 or later (64-bit);10.14.14.199;;poweredOn;46,51;300;3932;ds_es_ssd_strong;05.07.2018 14:49:36;IBER;;;;Windows Server 2016 Standard Edition - Desktop Experience, English

Main Machine - MASTER

but it should give me output like this: Windows Server 2016 Standard Edition - Desktop Experience, English Main Machine - MASTER

not like this:

Windows Server 2016 Standard Edition - Desktop Experience, English

Main Machine - MASTER

Anyways, my main question has been answered thank you once again.

[LucD]

Ok, now I get it, it is the Notes field alone where you want to remove the <CR><LF>.

Change that line to

Notes = $vm.Config.Annotation -replace '(?:\r|\n)',''

[SaqiChangx]

Yeah! Thank you once again

[bikashyadav]

Hi LucD,

I am trying to replace two items( abc with agf & xyz with rst) from the exported csv. But it is not working. Below is the snap of my script.

$VMReport = "C:\Users\Documents\VMINfo1.csv"

&{foreach($dc in Get-Datacenter){

foreach($cluster in Get-Cluster -Location $dc){

foreach($esx in Get-VMHost -Location $cluster){

Get-VM -Location $esx |

Select @{N='Datacenter';E={$dc.Name}},Name,PowerState,@{N="OS";E={$_.GuestId}},Notes

}

}

}} | Export-Csv $VMReport -NotypeINformation -UseCulture

Get-Content -Path $VMReport |

ForEach-Object -Process {

$_ -replace 'abc', 'egf' -or $_ -replace 'xyz', 'rst' } |

Out-File -FilePath $VMReport

[LucD]

Try with

($_ -replace 'abc', 'egf') -replace 'xyz', 'rst'