Using CSV files with your scripts

Every PowerSHell scripter has probably walked into this scenario. You make a perfect script, nothing could go wrong with it, it just requires a CSV as input to do something. And then you hand the script over to someone else, who has to create and fill the CSV.

Panic! The script “doesn’t work”! Except it works perfectly, but the person trying to use it used a different delimiter then you were expected, or left out a required header, or included too many headers, or included all the headers but renamed them.

The PowerSHell function we’re going to take a look at today solves all those headaches.
I’m not interested. Skip to the full function

Reading data correctly is important, or else your android might go bonkers!.

Get-Csvdata

Lets start by defining the function,

function Get-CSVdata {
    [CmdletBinding()]
    param (
        [string[]]$CorrectHeaders,

        [string]$filePath
    )

}

CmdletBinding makes it so that we can use Write-Verbose in the function. That way we can optionally show some context information to the user when the -Verbose parameter is set.
We go on to define two parameters, CorrectHeaders and filePath. CorrectHeaders will allow us to tell the function which headers we expect to be present in the CSV, if one of them is missing we throw an error. filePath allows us to set the filePath for the CSV. In some cases you will know the path in advance and there’s no need to show the (optional) file picker dialog.

if (-not $filePath) {
    # Open file select popup
    [void][System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = "::{20D04FE0-3AEA-1069-A2D8-08002B30309D}"
    $OpenFileDialog.filter = 'CSV (*.csv)|*.csv'
    $result = $OpenFileDialog.ShowDialog((New-Object System.Windows.Forms.Form -Property @{TopMost = $true }))

    if ($result -ne [System.Windows.Forms.DialogResult]::OK) {
        throw "No file selected."
    }

    $filePath = $OpenFileDialog.FileName
}

Next we will add some functionality to display a file picket dialog. This way the end user has a user friendly way to select the CSV file. We default the dialog to open to ::{20D04FE0-3AEA-1069-A2D8-08002B30309D} which is a so-called “special folder” GUID. It allows us to select “This Computer” as the start location of the dialog regardless of the language set in the OS.

if (Test-Path -Path $filePath) {
  # Our later code will go here
} else {
    throw "$($filePath) is not a valid (CSV) file"
}

It is important to test that the selected file actually exists. While the file picker dialog option already accounts for this, the filePath parameter does not so we wrap the rest of our code in an if condition with Test-Path as the condition

# Read the first few lines of the file
$sampleContent = Get-Content -Path $filePath -TotalCount 5

# Detect delimiter
$delimiters = @(',', ';', "`t", '|')
$delimiter = $delimiters | Where-Object {
    ($sampleContent[0] -split $_ | Measure-Object).Count -gt 1 -and
    ($sampleContent[1] -split $_ | Measure-Object).Count -eq ($sampleContent[0] -split $_ | Measure-Object).Count
} | Select-Object -First 1

# Fallback to comma if no clear delimiter is detected
if (-not $delimiter) {
    throw "Failed to detect delimiter."
} else {
    Write-Verbose "CSV file delimiter detected as '$delimiter'"
}

# Import CSV with detected delimiter
$csvContent = Import-Csv -Path $filePath -Delimiter $delimiter

Once we’re certain that the file exists we will use Get-Content to read the first few lines of data. We will use this data to attempt to detect the delimiter. Please note that the way we detect the delimiter here isn’t perfect, but for most cases it will work perfectly.
In case we can’t detect the delimiter we fall back on a comma, my assumption is that, as its a “comma separated values” file that most of the world will be using a comma. Once we’ve detected the delimiter we use Import-Csv to load in the data.

# Get the headers from the CSV file
$csvHeaders = $csvContent | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name

# Check if all correct headers are present in the CSV
$missingHeaders = $CorrectHeaders | Where-Object { $_ -notin $csvHeaders }
$extraHeaders = $csvHeaders | Where-Object { $_ -notin $CorrectHeaders }

if ($missingHeaders) {
    throw "Missing headers: $($missingHeaders -join ', ')"
}
if ($extraHeaders) {
    Write-Verbose "Extra headers found: $($extraHeaders -join ', ')"
}
if (-not $missingHeaders) {
    Write-Verbose "All required headers detected"
    return $csvContent
}

Next up we’re going to check the headers as defined in the “CorrectHeaders” parameter. If the csv headers don’t at least match what we’re expecting, we will throw an error because it is likely the file doesn’t contain all the data we require for the script.

Full function

As you can see once we put it all together we have a function that handles most of the scenarios where a user can make a mistake when using CSV data with your script.
We handle:

• Missing headers (and by extension misnamed headers)
• User friendly file dialog
• Automatic delimiter detection Personally I’ve seen that adding this to my scripts significantly reduces the incidince of “user error”.

Have fun!

function Get-CSVdata {
    [CmdletBinding()]
    param (
        [string[]]$CorrectHeaders,

        [string]$filePath
    )

    if (-not $filePath) {
        # Open file select popup
        [void][System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
        $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
        $OpenFileDialog.initialDirectory = "::{20D04FE0-3AEA-1069-A2D8-08002B30309D}"
        $OpenFileDialog.filter = 'CSV (*.csv)|*.csv'
        $result = $OpenFileDialog.ShowDialog((New-Object System.Windows.Forms.Form -Property @{TopMost = $true }))

        if ($result -ne [System.Windows.Forms.DialogResult]::OK) {
            throw "No file selected."
        }

        $filePath = $OpenFileDialog.FileName
    }

    if (Test-Path -Path $filePath) {
        # Read the first few lines of the file
        $sampleContent = Get-Content -Path $filePath -TotalCount 5

        # Detect delimiter
        $delimiters = @(',', ';', "`t", '|')
        $delimiter = $delimiters | Where-Object {
            ($sampleContent[0] -split $_ | Measure-Object).Count -gt 1 -and
            ($sampleContent[1] -split $_ | Measure-Object).Count -eq ($sampleContent[0] -split $_ | Measure-Object).Count
        } | Select-Object -First 1

        # Fallback to comma if no clear delimiter is detected
        if (-not $delimiter) {
            throw "Failed to detect delimiter."
        } else {
            Write-Verbose "CSV file delimiter detected as '$delimiter'"
        }

        # Import CSV with detected delimiter
        $csvContent = Import-Csv -Path $filePath -Delimiter $delimiter

        # Get the headers from the CSV file
        $csvHeaders = $csvContent | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name

        # Check if all correct headers are present in the CSV
        $missingHeaders = $CorrectHeaders | Where-Object { $_ -notin $csvHeaders }
        $extraHeaders = $csvHeaders | Where-Object { $_ -notin $CorrectHeaders }

        if ($missingHeaders) {
            throw "Missing headers: $($missingHeaders -join ', ')"
        }
        if ($extraHeaders) {
            Write-Warning "Extra headers found: $($extraHeaders -join ', ')"
        }
        if (-not $missingHeaders) {
            Write-Verbose "All required headers detected"
            return $csvContent
        }
    } else {
        throw "$($filePath) is not a valid (CSV) file"
    }
}