10 Replies Latest reply on Apr 7, 2020 4:59 AM by SaqiChangx

    Remove double quote and break line from Powershell-Script

    SaqiChangx Novice

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

        • 1. Re: Remove double quote and break line from Powershell-Script
          LucD Guru
          Community WarriorsUser ModeratorsvExpert

          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?

          Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
          • 2. Re: Remove double quote and break line from Powershell-Script
            SaqiChangx Novice

            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.

            • 3. Re: Remove double quote and break line from Powershell-Script
              LucD Guru
              vExpertCommunity WarriorsUser Moderators

              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?

              Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
              • 4. Re: Remove double quote and break line from Powershell-Script
                SaqiChangx Novice

                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.

                • 5. Re: Remove double quote and break line from Powershell-Script
                  LucD Guru
                  Community WarriorsUser ModeratorsvExpert

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

                  Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                  • 6. Re: Remove double quote and break line from Powershell-Script
                    SaqiChangx Novice

                    Sorry It was mistakenly happened :p

                    • 7. Re: Remove double quote and break line from Powershell-Script
                      LucD Guru
                      User ModeratorsCommunity WarriorsvExpert

                      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

                      Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                      • 8. Re: Remove double quote and break line from Powershell-Script
                        SaqiChangx Novice

                        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.

                        • 9. Re: Remove double quote and break line from Powershell-Script
                          LucD Guru
                          vExpertUser ModeratorsCommunity Warriors

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

                          Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                          1 person found this helpful
                          • 10. Re: Remove double quote and break line from Powershell-Script
                            SaqiChangx Novice

                            Yeah! Thank you once again