This article discusses how to load a CSV file into a PowerShell array using two methods:
- Method 1: Using Import-Csv cmdlet, and
- Method 2: Reading CSV data with Get-Content and then processing each row.
We will run our code on PowerShell 7.3.2 using a “employees – list.csv” file with the following contents (opened with Notepad):
ID,Position,Salary,Joining Date 2001,Junior web developer,5600,8/27/2020 2006,Accountant,7200,7/22/2019 3014,Senior Data Scientist,7800,5/1/2013 1946,HR Manager,6200,9/9/2021 1947,Managing Director,22000,9/24/2009 1901,Procurement officer 2,6100,12/8/2010 1902,HR,5700,12/8/2010 2008,Procurement officer,6100,2/8/2011
The first row contains the fields, and the other rows hold the records. The content above shows that the “employees – list.csv” is a comma-delimited file. Note that you can use other characters as delimiters – for example, semi-colons or hash symbols.
Let’s now discuss how to read CSV files into PowerShell arrays.
Method 1: Using Import-Csv Cmdlet
Import-Csv cmdlet loads CSV data into a table-like custom object. Each column in the CSV becomes a property of the custom object, and the row values become the property values.
The cmdlet has the following syntax (showing three relevant options only):
Import-Csv -Path <path_to_csv_file> -Delimiter <value_separator> -Header <customer_headers>
Where -Path is the path to our CSV file, -Delimiter is the values separator (if a value is not passed to this option, a comma is used as delimiter), -Header contains the custom headers (if this option is not supplied the first row of the CSV is used as the headers).
Let’s use the cmdlet to load the CSV file described earlier.
|
1 2 3 4 |
# Load CSV file content $data = Import-Csv -Path ".\employees - list.csv" # View the data $data |
You can also load specific fields from the CSV file by piping the output of Import-Csv to the Select-Object cmdlet. Here is an example showing how to load ID and Position columns only.
|
1 2 |
$data1 = Import-Csv -Path '.\employees - list.csv' | Select-Object -Property ID,Position $data1 |
Accessing values of the PowerShell array
The output of the Import-Csv cmdlet can be accessed like a typical PowerShell array.
Note: PowerShell uses zero-based indexing; that is, the first element of an array is given index 0, the second element is at index 1, and so on.
Here are some examples of how to access the values.
|
1 2 3 4 5 6 |
$data = Import-Csv -Path ".\employees - list.csv" $data[0] # First element $data[1..3] # Second to the fourth element $data[0,4] # First and the fifth element $data[-1] # The last element $data[5..($data.Length-1)] # Third to the last |
Method 2: Using Get-Content and ForEach
This method brings a different blend of control in processing the loaded data. The Get-Content cmdlet gets the data on the CSV file then we loop through the rows using the ForEach command. Here is an example.
|
1 2 3 4 5 6 7 8 9 10 11 |
# initialize an empty array $arr = @() # Get the content of '.\employees - list.csv' $file_content = Get-Content -Path '.\employees - list.csv' # Loop through each row. Select-Object -Skip 1 skips the header row. ForEach ($row in $file_content | Select-Object -Skip 1){ # Split the row based on the delimiter to get individual record values $arr = $arr + @($row -Split ",") } # Display the result on the console. $arr |
Output (truncated):
Then you can access the array values as we did in Method 1
|
1 2 3 |
$arr[0] #Output: 2001 $arr[1] #Output: Junior web developer $arr[5..9] |
Output:
Accountant 720 7/22/2019 3014 Senior Data Scientist
Alternatively, we can load the data into a hash table of array values, as shown below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
# Creating a hash table with four keys with empty arrays. $hash_data = @{ 'id' = @() 'Position' = @() 'Salary' = @() 'JoiningDate' = @() } # Load the CSV file $file_content = Get-Content -Path '.\employees - list.csv' | Select-Object -Skip 1 ForEach ($row in $file_content) { $fields = @($row -Split ",") # Assign the resulting 4-element array into four variables $id, $position, $salary, $joiningDate = $fields # Assign the corresponding values into the key of the hash_data # Trim() is used to remove trailing white spaces. $hash_data['id'] += $id.Trim() $hash_data['Position'] += $position.Trim() $hash_data['Salary'] += $salary.Trim() $hash_data['JoiningDate'] += $joiningDate.Trim() } $hash_data |
Output:
Now, you can access the data on $hash_data like any other PowerShell hash table and/or array. For example,
|
1 |
$hash_data.JoiningDate # or $hash_data["JoiningDate"] |
Output (truncated):
8/27/2020 7/22/2019 …
|
1 |
$hash_data.JoiningDate[0] # Output: 8/27/2020 |
We can also pack the CSV data into an array, each element being a hash table of row values.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# Create a hash table that will keep our data $arr_data = @() $file_content = Get-Content -Path '.\employees - list.csv' | Select-Object -Skip 1 ForEach ($row in $file_content) { $fields = @($row -Split ",") $id, $position, $salary, $joiningDate = $fields # Create a hash table of the row data $hash_data =@{ "Id" = $id "Position" = $position "Salary" = $salary "JoiningDate" = $joiningDate } # Append the resulting hashtable into the array variable defined earlier $arr_data += $hash_data } $arr_data |
Output (truncated):
Then you can access the row values through indexing. For example, $arr_data[0] will fetch the hash table for the values in the first row and $arr_data[1..3].Id gets the Id values for rows 2 through 4.
Conclusion
This guide discussed two PowerShell cmdlet tools that can load a CSV file into an array. The first method uses the Import-Csv to read CSV into a table-like custom object made of column names as properties and row values as property values. The values of the custom object can then be fetched through indexing.
The second method uses the Get-Content cmdlet. In this method, the CSV file is read using the cmdlet then the records are fetched and processed through looping.