Grouping items from a CSV file

I have a CSV file for input objects to be processed and they look like this:

Cart   Item
C1   Apple
C1  Orange
C3  Orange
C5  Orange
C2  Apple
C3  Orange

What I’d like to do is read in the CSV file and perform the following:

  1. Collate all items for C1, C2, C3 & C5.
  2. For…Each a loop thru the items for C1, processing the cart contents, then C2, process its contents, etc.
    Note that there may be 10 carts or 100 or even 22. There may be Apples, Oranges, Bananas, anything could appear in the Item column and needs to be processed.

Is this possible?

At the moment I have code which goes thru each line of the CSV and processes each line item.

Thanks.

Background:
The “Cart” is a vCenter entry in our environment. The Items are host names. My code logs into each vCenter and enumerates the VMs on each host, exports the VM details to a CSV file then logs out again. So for one vCenter, I could end up with say 10 export CSV files.

The code which enumerates the VMs on the hosts is another script which normally runs on its own. Here’s how I call it:

$ReturnData = & (".\Get-VMData.ps1") -Cluster $ClusterName -VMHost $VMHostName -ExportFileNamePrefix $ExportFileNamePrefix

You can use Group-Object then process the groups:

$Data = @'
Cart,Item
C1,Apple
C1,Orange
C3,Orange
C5,Orange
C2,Apple
C3,Orange
'@

$Csv = $Data | ConvertFrom-Csv
$CartList = $Csv | Group-Object -Property Cart

foreach ($Cart in $CartList) {
    'Cart {0} contains {1} items.' -f $Cart.Name, $Cart.Group.Count
}

I appreciate the example data, but, I think that I’m failing to picture what your intended outcome is. When you say “and needs to be processed” I don’t know what that means contextually.
If there ends up being 10x C1s with a mix of apples, oranges and bananas, do you just want a count of unique fruit per cart?
If so @matt-bloomfield example is what I would lean towards. But given the extra info about this being vCenter data now I’m wondering if it couldn’t all just be one script that loops through all of the vCenters, and then does something with all the returned vCenter data, instead of writing everything out to file to re-ingest it and work on it.

OK, so the CSV contains a list of VMware hosts and which vCenter they belong to. There may be multiple hosts for any vCenter. The script needs to log into each vCenter and enumerate all the VMs running on those hosts.

My script currently reads each row, picks out the vCenter name and the host name, logs into the vCenter, enumerates the VMs on that host, exports the data to CSV then goes to the next row, which is a vCenter and host.

If the vCenter appears say 20 times in the CSV, my script will log into it 20 times. I thus want to log into each vCenter once, send all the host names for that vCenter to my VM enumeration script which will export all the VMs on all the hosts for the one vCenter and then log into the next vCenter.

1 Like

got it.
let me see if i can get into an environment like yours and do some testing

Matt, your code worked perfectly. Here’s what I have come up with:

# Read the host data from the CSV file.
$VMHostData = Import-CSV $ImportCSVFile -ErrorAction SilentlyContinue
# Group all hosts by the vCenter name.
$vCenterList = $VMHostData | Group-Object -Property vCenter

....

ForEach($vCenter in $vCenterList)
    {
    # For this vCenter, place all the hostnames under the field "Server_Name" into one variable.
    $VMHostNames = ($vCenter.Group | Select-Object -expandproperty 'Server_Name')
  
    $ReturnData = & (".\Get-VMData.ps1") -VMHost $VMHostNames -ExportFileNamePrefix $ExportFileNamePrefix

The code in my script “Get-VMData” cycles thru each host, one-by-one, and enumerates all VMs on the respective host.

Thanks so much!!

2 Likes

Hey Grey0ut, thanks for helping out.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.