To give you a boost with some general concepts in PowerShell, I have tried to give an answer below to each of your specific (sub)question:
"How to iterate through CSV data from PowerShell?" would perhaps
be an alternate question title.
Most cmdlets in PowerShell are using the PowerShell Pipeline. The Foreach-Object is a native object to iterate through each object (row) in the pipeline where $_ is the current object and Region a sample property:
PS C:\> $Flat | Foreach-Object { $_.Region }
BC
Fraser
Interior
Northern
Unknown
Vancouver Coastal
Vancouver Island
You might also use the Foreachmethod to iterate through the objects:
PS C:\> $Flat.Foreach{ $_.Region }
BC
Fraser
Interior
Northern
Unknown
Vancouver Coastal
Vancouver Island
but I'd be more interested in, for example, something like this basic SQL:
SELECT * FROM $flat limit 3
The PowerShell complement of the SQL command SELECT * FROM $flat limit 3,
would be the Select-Object cmdlet:
PS C:\> $Flat | Select-Object * -First 3
Date : 2020-01-23
Region : BC
New_Tests : 2
Total_Tests : 2
Positivity : 0
Turn_Around : 32
Date : 2020-01-23
Region : Fraser
New_Tests : 0
Total_Tests : 0
Positivity : 0
Turn_Around : 0
Date : 2020-01-23
Region : Interior
New_Tests : 0
Total_Tests : 0
Positivity : 0
Turn_Around : 0
or using WHERE clauses. Perhaps even just returning row/record n?
To select a specific object (row) you might use the Select-Object -Index <index> Parameter:
PS C:\> $Flat | Select-Object -Index 3
Date : 2020-01-23
Region : Northern
New_Tests : 0
Total_Tests : 0
Positivity : 0
Turn_Around : 0
Or just directly index into the object array:
PS C:\> $Flat[3]
Date : 2020-01-23
Region : Northern
New_Tests : 0
Total_Tests : 0
Positivity : 0
Turn_Around : 0
To find an object with a specific property, you might use the Where-Object cmdlet:
PS C:\> $Flat | Where-Object Turn_Around -eq 32
Date : 2020-01-23
Region : BC
New_Tests : 2
Total_Tests : 2
Positivity : 0
Turn_Around : 32
Date : 2020-01-23
Region : Vancouver Coastal
New_Tests : 2
Total_Tests : 2
Positivity : 0
Turn_Around : 32
Or the Where method:
PS C:\> $Flat.Where{ $_.Turn_Around -eq 32 }
Date : 2020-01-23
Region : BC
New_Tests : 2
Total_Tests : 2
Positivity : 0
Turn_Around : 32
Date : 2020-01-23
Region : Vancouver Coastal
New_Tests : 2
Total_Tests : 2
Positivity : 0
Turn_Around : 32
To select a specific property (column) you might use the Select-Object -ExpandProperty <property> parameter:
PS C:\> $Flat | Select-Object -ExpandProperty Region
BC
Fraser
Interior
Northern
Unknown
Vancouver Coastal
Vancouver Island
Or use the Member Enumeration feature:
PS C:\> $Flat.Region
BC
Fraser
Interior
Northern
Unknown
Vancouver Coastal
Vancouver Island
Preferably represented as:
Most cmdlets default output the results to the pipeline (see: Write-Output). If the command is the last command in the pipeline, the objects are displayed in the console. PowerShell uses default formatters to define how object types are displayed, but you might use format cmdlets, as e.g. Format-Table and Format-List, to format the output to the console your own way:
PS C:\> $Flat | Select-Object * -First 3 | Format-Table
Date Region New_Tests Total_Tests Positivity Turn_Around
---- ------ --------- ----------- ---------- -----------
2020-01-23 BC 2 2 0 32
2020-01-23 Fraser 0 0 0 0
2020-01-23 Interior 0 0 0 0
Which I take as, perhaps, the fields or properties belonging to a generic Object.
In PowerShell, they called Properties and belong to the specific object.
To list the Properties and and methods of an object you might use the Get-Member cmdlet:
PS C:\> $Flat | Get-Member
TypeName: System.Management.Automation.PSCustomObject
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Date NoteProperty string Date=2020-01-23
New_Tests NoteProperty string New_Tests=2
Positivity NoteProperty string Positivity=0
Region NoteProperty string Region=BC
Total_Tests NoteProperty string Total_Tests=2
Turn_Around NoteProperty string Turn_Around=32
How do I map a row from CSV to an Object in Powershell on Linux?
Import-Csv ./Flat.csv | ... (e.g. Where-Object ... or Select-Object ..) | ...
You didn't use the word Join, but if you looking for a way to join a list of objects (from a csv file) with another list of objects (resident in PowerShell) based on a related property, you might want to check this external Join-Object cmdlet (see also: In Powershell, what's the best way to join two tables into one?)
Note that PowerShell Core is based on .Net Core framework which are both intended to be cross-platform. Meaning there is basically no differents in selecting objects and properties (mapping) between any operating system that supports PowerShell Core.
