CODEWITHSUNDEEP
powershellcsvvmware
Yukaputz Mike
Yukaputz Mike

Reputation: 15

Advanced CSV Filtering in Powershell?

I am filtering an export from power cli on my vcenters. I need to produce two csv files from the export Vmware creates from powercli. When I export, Esxi creates a column called GUEST then populates it with "servername.somedomain.com:Microsoft Windows Servers 2016" in the column and separates it with semicolon depending on the OS type.

I need to produce csv report that has all the servers with the same OS's and the associated columns as well as a report of all the like domain servers and associated columns.

Import csv doesn't support wildcards so I can't filter out somedomain.com nor can I filter on 2016 on input. I'm at a loss which direction to turn next and would appreciate or a concise reading on better powershell techniques to manipulate data in csv files for dummies? Below is what I use to do most of the filtering but I'm stuck on the cell described above.

 Import-CSV -Path "E:\esxi.csv | ? Folder -notlike *SharePoint*| ? Notes -notlike *Physical* | ? Notes -notlike *Sharepoint* | Export-Csv "E:\filtered.csv" -NoTypeInformation

Here is a link to a sample csv.
https://drive.google.com/file/d/1DdqtVFVcZ0aFnoUDqB2ErNX_yA9LBO8H/view?usp=sharing

Upvotes: 0

Views: 105

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174990

I need to produce csv report that has all the servers with the same OS

You'll want Group-Object - it'll group input objects based on some property expression, which is exactly what you need:

# Import CSV data
$data = Import-CSV -Path "E:\esxi.csv"

# Group by OS label (the substring following the last `:` in the GUEST column)
$OSGroups = $data |Group-Object { $_.GUEST -replace '^.*:(?=[^:]+$)'}

# Loop through the resulting groups and output a new CSV for each
foreach($OSGroup in $OSGroups){
  # Extract the OS name that we grouped on
  $OSName = $OSGroup.Name

  # Export relevant records to new CSV
  $OSGroup.Group |Export-Csv "PerOSReport_${OSName}.csv" -NoTypeInformation
}

Upvotes: 1

Related Questions