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).
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.
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: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
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: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
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: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
Since you marked your last answer as the Correct Answer, I assume your issues are solved?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Sorry It was mistakenly happened 😛
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.
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: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
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: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Yeah! Thank you once again
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
Try with
($_ -replace 'abc', 'egf') -replace 'xyz', 'rst'
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference