PowerCLI

 View Only
Expand all | Collapse all

Remove double quote and break line from Powershell-Script

  • 1.  Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 07:21 AM

    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).



  • 2.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 07:42 AM

    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?



  • 3.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 08:21 AM

    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.



  • 4.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 08:28 AM

    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?



  • 5.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 09:37 AM

    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.



  • 6.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 09:45 AM

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



  • 7.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 09:47 AM

    Sorry It was mistakenly happened :p



  • 8.  RE: Remove double quote and break line from Powershell-Script
    Best Answer

    Posted Apr 07, 2020 10:04 AM

    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



  • 9.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 11:20 AM

    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.



  • 10.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 11:32 AM

    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)',''



  • 11.  RE: Remove double quote and break line from Powershell-Script

    Posted Apr 07, 2020 12:00 PM

    Yeah! Thank you once again



  • 12.  RE: Remove double quote and break line from Powershell-Script

    Posted Mar 22, 2021 06:13 PM

    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



  • 13.  RE: Remove double quote and break line from Powershell-Script

    Posted Mar 22, 2021 06:23 PM

    Try with

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