Advanced CSV Filtering in Powershell?

Question

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

"Import csv doesn't support wildcards so I can't filter out somedomain.com" - you don't need a wildcard, you just said it was in the GUEST column? Import-Csv ... |? GUEST -notlike *somedomain.com* — Mathias R. Jessen
– Mathias R. Jessen, Commented Oct 19, 2021 at 13:49
Please post a representative portion of your sample data directly in the question (as an aside: the linked file isn't publicly accessible) and describe the desired output. — mklement0
– mklement0, Commented Oct 19, 2021 at 13:55
Mathias, I see what your saying, instead of trying to filtering "for" what I want, just filter out everything else... I'll give it a shot. — Yukaputz Mike
– Yukaputz Mike, Commented Oct 19, 2021 at 14:25

Mathias R. Jessen · Accepted Answer · 2021-10-19 15:18:47Z

1

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
}

answered Oct 19, 2021 at 15:18

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Advanced CSV Filtering in Powershell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related