PowerShell: Split a CSV and Looping inside Arrays – Detailed Guide

PowerShell is a powerful scripting language that allows system administrators to automate tasks. One such task is processing CSV files to extract, modify, or analyze data. In this blog, we'll walk through the steps to split and loop through a CSV file using PowerShell, focusing on reading user details and handling array structures.

By the end of this guide, you'll understand how to:

• Open a CSV file using an Open File Dialog.
• Import the CSV into an array.
• Loop through the array to access individual records.
• Display the extracted data.

Detailed Steps

Step 1: Opening the CSV File with an Open File Dialog

First, let's begin by allowing the user to open a CSV file using a graphical interface. We'll achieve this by presenting an "Open File Dialog" box. This will make the script more user-friendly as you don't need to hardcode the file path.

Here’s the code snippet to open the file dialog:

param(
    [Parameter(ValueFromPipeline=$true,HelpMessage="Enter CSV path(s)")]
    [String[]]$Path = $null
)
if ($Path -eq $null) {
    # Add the required .NET assembly to display Windows forms
    Add-Type -AssemblyName System.Windows.Forms
    # Create a new Open File Dialog instance
    $Dialog = New-Object System.Windows.Forms.OpenFileDialog
    # Set the initial directory and file filter
    $Dialog.InitialDirectory = "$InitialDirectory"
    $Dialog.Title = "Select CSV File(s)"
    $Dialog.Filter = "CSV File(s)|*.csv"
    $Dialog.Multiselect = $true
    # Display the dialog and check if the user selected a file
    $Result = $Dialog.ShowDialog()
    if ($Result -eq 'OK') {
        try {
            # Retrieve the file path(s) selected by the user
            $Path = $Dialog.FileNames
        } catch {
            $Path = $null
            Break
        }
    } else {
        Write-Host -ForegroundColor Yellow "Notice: No file(s) selected."
        Break
    }
}        

Explanation:

• Add-Type -AssemblyName System.Windows.Forms: This line adds the .NET assembly needed to use Windows Forms.
• $Dialog = New-Object System.Windows.Forms.OpenFileDialog: Creates the dialog box for file selection.
• $Dialog.Filter = "CSV File(s)|*.csv": Ensures only CSV files can be selected.
• The dialog allows for selecting multiple files, and the result is stored in $Path.

If the user cancels the dialog, a message is displayed and the script halts.

Step 2: Importing the CSV File

Once we have the file path(s), we can import the data from the CSV into an array. PowerShell’s Import-Csv cmdlet helps us load the data. Here’s how it’s done:

# Import the CSV data into the $userobjects array
$userobjects = Import-Csv $Path -Delimiter ';'        

Explanation:

• Import-Csv: Reads the CSV file and stores each row as an object in the $userobjects array.
• -Delimiter ';': This specifies the delimiter used in the CSV file. The default is a comma, but in this case, we use a semicolon (;).

Now, $userobjects is an array where each entry represents a row in the CSV file, and the columns are treated as properties of each object.

Step 3: Accessing Data in the Array

Once the CSV is imported, you can access individual fields by referencing the column names. Each row in the CSV corresponds to an object, and we can access its properties (like Firstname, Lastname, Number, etc.) directly.

Let’s say your CSV contains columns such as Firstname, Lastname, Number, Mail, and OU. Here’s how you can access the first row of the CSV:

# Accessing data from the first row in the array
$userobjects[0].Firstname + " " + $userobjects[0].Lastname + " " + $userobjects[0].Number + " " + $userobjects[0].Mail + " " + $userobjects[0].OU        

This line prints the values of the first row, combining Firstname, Lastname, Number, Mail, and OU into a single output.

Step 4: Looping Through the Array

We can now loop through all rows of the CSV and extract or display each value. We’ll use a ForEach loop for this task:

# Loop through each row in the CSV file
ForEach ($userobject in $userobjects) {
    # Assign each column value to a variable
    $Firstname = $userobject.Firstname
    $LastName = $userobject.Lastname
    $Number = $userobject.Number
    $Mail = $userobject.Mail
    $OU = $userobject.OU
    # Display the information for each user
    Write-Host "Number: $Number, Firstname: $Firstname, Lastname: $LastName, Mail: $Mail, OU: $OU"
}        

Explanation:

• ForEach: This loops through each item in the $userobjects array.
• $userobject: Represents each row (user record) from the CSV.
• The variables ($Firstname, $LastName, etc.) are assigned values from the corresponding columns of the CSV.
• Write-Host is used to display the data for each user in a readable format.

Step 5: Handling Special Cases – Quoting Text with Spaces

In some cases, the data might contain spaces, especially in fields like Lastname. If we need to handle these fields properly (e.g., wrap them in quotes), we can do so like this:

ForEach ($userobject in $userobjects) {
    $Firstname = $userobject.Firstname
    $LastName = $userobject.”Lastname”
    $Number = $userobject.Number
    $Mail = $userobject.Mail
    $OU = $userobject.OU
 
   # Check if the Lastname contains spaces, and wrap it in quotes if necessary
    if ($LastName -match "\s") {
        $LastName = "`"$LastName`""
    }
    # Output the formatted information
    Write-Host "Number: $Number, Firstname: $Firstname, Lastname: $LastName, Mail: $Mail, OU: $OU"
}        

Explanation:

• if ($LastName -match "\s"): This checks if the Lastname contains a space (\s is the regex for space).
• $LastName = "`"$LastName`"": If there is a space, the Lastname is wrapped in quotes.

Conclusion

In this blog, we explored how to:

• Open a CSV file using a dialog in PowerShell.
• Import the CSV data into an array.
• Loop through the array to access individual records.
• Display and format the extracted data.

This approach can be expanded and modified to handle more complex CSV processing tasks. PowerShell’s ability to manipulate arrays and work with structured data makes it an essential tool for administrators dealing with CSV data.