CSV is a file of Character-Separated values where a comma is used in most cases as a delimiter. This article discusses two ways of looping through the values of a given CSV file.
We will be using the baby_names.csv file in our examples. The file has the following contents:
Id,Name,Year,Gender,State,Count 11350,Emma,2004,F,AK,62 11351,Madison, ,F,AK,48 11352,Hannah,2004,F,AK,46 11353, Grace,2004,F,AK,44 11354,Emily,2004,F, ,41 11355,Abigail,2004,F,AK,37 11356,Olivia,2004,F,AK,33
The CSV file is comma-separated with six columns and seven rows. The values on the first row are the headers.
Note: All the commands in this article will be executed on a PowerShell script (.ps1 file). However, you are free to run the code directly from PowerShell.
To execute a PowerShell script, add the code into the .ps1 script, then run the following commands on the PowerShell:
|
1 2 |
cd <path_to_directory_containing_the_script> .\<filename>.ps1 |
The first command changes the working directory to the folder containing the script, and the second executes the actual script.
Method 1: Using Import-Csv cmdlet and ForEach
The Import-Csv creates table-like objects from the items in a CSV file then we loop through these items.
Script: loop_csv1.ps1
|
1 2 3 4 5 6 |
Import-CSV -Path .\baby_names.csv -Delimiter "," | ForEach-Object { # Fetch column values using $_.<column_name> $_.Name, $_.Id, $_.Count # Using Write-Host to write customized output to the prompt. Write-Host "Baby $($_.Name) of ID $($_.Id)" } |
Alternatively, we can loop through the CSV file with items as key-value pairs where the column name is the key.
Script: loop_csv1.ps1
|
1 2 3 4 5 6 |
Import-Csv -Path .\baby_names.csv -Delimiter "," | Foreach-Object { foreach ($property in $_.PSObject.Properties) { Write-Host "$($property.Name): $($property.Value)" } } |
Finding a CSV record based on Column Value
In this subsection, we want to find records based on a given column’s value. This requires us to use an if-statement to check the condition(s).
For example, the script below searches for a baby record based on ID.
Script: loop_while_searching.ps1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# Searching and displaying records Matching a criteria Function FindBaby_ByID { param ( # Parameter accepts the baby id to be searched. [Parameter(Mandatory = $true)] $BabyID ) # Import the contents of the baby_names.csv file and store it in the $baby_list variable. $baby_list = Import-Csv .\baby_names.csv -Delimiter "," # Loop through all the records in the CSV foreach ($baby in $baby_list) { # Check if the current row's baby ID is equal to the value of the BabyID passed to the function as a parameter. if ($baby.Id -eq $BabyId) { # If a record matching the ID is found, display the record on the console using Write-Host Write-Host "There are $($baby.Count) babies with the ID $($baby.Id), born in $($baby.State)." } } } # Calling the FindBaby_ByID function to search for a record with ID=11353 FindBaby_ByID -BabyID "11353" |
Method 2: Using Get-Content and a Loop
The Get-Content cmdlet is used to get the item’s content at the specified location. In our example, we will use it to read the contents of the CSV and then loop through the results.
Script: loop_get_content.ps1
|
1 2 3 4 5 6 |
# Read all the lines in baby_names.csv, then loop # the lines $babies = Get-Content -Path .\baby_names.csv ForEach ($baby in $babies) { write-host $baby } |
You can then process each row at a time. In the following code, we split the contents of each line along the comma symbol to get the PowerShell list for each row.
Script: loop_get_content2.ps1
|
1 2 3 4 5 6 7 8 9 10 11 |
# Read all the lines in baby_names.csv, then loop # the lines $babies = Get-Content -Path .\baby_names.csv ForEach ($baby in $babies) { # Split the row by the delimiter. Yields a PowerShell list $record_list = $($baby -split ',') Write-Host $record_list # Print specific items through indexing. # first at index 0 and third at index 2 Write-Host $record_list[0], $record_list[2] } |
Conclusion
This article discussed two methods of looping through a CSV file row by row. The first method discussed using the Import-Csv cmdlet to read CSV content and then loop through it. The second method works similarly but uses Get-Content to read the file.