PowerShell advanced function (cmdlet) to import CSV, convert values into correct types and split all serialized arrays and collections

There are situations where you need to import data from CSV but some properties (columns in CSV) should be in different type (bool, DateTime, int) and some properties are arrays and lists (collections) with another delimiter than delimiter that was used for CSV.

The problem is that CSV is a flat file. Information are not hierarchical like in JSON (JavaScript Object Notation) or XML.

Such situation could be solved for example like this:

Import-Csv `
    -Path 'C:\Temp\My Users from Active Directory.csv' `
    -Delimiter "`t" `
    -Encoding UTF8 |
    Select-Object -Property `
        SamAccountName,
        DistinguishedName,
        @{ Expression = { $(if ($_.CannotChangePassword  -eq 'True') { $true } else { $false }) }; Label = 'CannotChangePassword' },
        Description,
        DisplayName,
        @{ Expression = { $(if ($_.Enabled  -eq 'True') { $true } else { $false }) }; Label = 'Enabled' },
        GivenName,
        Initials,
        Name,
        Surname
        @{ Expression = { $_.MemberOf -split ';' }; Label = 'MemberOf' } |
    Sort-Object -Property SamAccountName

or you can use function that I wrote.

Possibilities

It is possible to use ComputerName (invoke all operations) or Session (use established session) to import CSV on a remote computer.
It is possible to define names of properties (columns in CSV) and their types and do the conversion (Cast()).
It is possible to convert all True and False strings in all properties (CSV columns) into Boolean.
It is possible to split all or defined properties (values in defined CSV columns) into arrays.

Examples

Convert (cast) defined properties (values in defined CSV columns) into defined data types

$propertyCast =
@(
    @{
        PropertyName  = 'primaryGroupID'
        TypeName      = 'int'
    },
    @{
        PropertyName  = 'sAMAccountType'
        TypeName      = 'int64'
    }
)

Import-RvCsv `
    -Path 'D:\Temporary\Data from Export-RvCsv.csv' `
    -PropertyCast $propertyCast `
    -OutVariable output |
    Select-Object -Property SamAccountName, primaryGroupID, sAMAccountType

$output[0].primaryGroupID.GetType().FullName
# Output: System.Int32
$output[0].sAMAccountType.GetType().FullName
# Output: System.Int64

Convert defined column in CSV into boolean; everything else than string “True” is $false

Import-RvCsv `
    -Path 'D:\Temporary\Data from Export-RvCsv.csv' `
    -PropertyCastBool isCriticalSystemObject `
    -OutVariable output |
    Select-Object -Property Created, SamAccountName, isCriticalSystemObject, MemberOf

$output[0].isCriticalSystemObject.GetType().FullName
# Output: System.Boolean

Convert all occurrences of “True” and “False” strings into boolean

Import-RvCsv `
    -Path 'D:\Temporary\Data from Export-RvCsv.csv' `
    -PropertyCastBoolAll `
    -OutVariable output |
    Select-Object -Property Created, SamAccountName, Enabled, MemberOf

$output[0].Enabled.GetType().FullName
# Output: System.Boolean

Split values in defined properties (values in defined CSV columns) into arrays

Import-RvCsv `
    -Path 'D:\Temporary\Data from Export-RvCsv.csv' `
    -PropertySplit MemberOf `
    -SplitDelimiter ';' `
    -OutVariable output |
    Select-Object -Property SamAccountName, MemberOf

$output[0].MemberOf.GetType().FullName
# Output: System.String[]

Import CSV on remote computer

$items = Import-RvCsv `
    -Path 'D:\Temporary\file_on_remove_device.csv'
    -ComputerName cont2test0.ad1.contoso.com, cont2test0.ad1.contoso.com

Import CSV on remote computer and reuse existing session

$sessionItem = New-PSSession -ComputerName cont2test0.ad1.contoso.com

$items = Import-RvCsv `
    -Path 'D:\Temporary\file_on_remove_device.csv' `
    -Session $sessionItem

Disconnect-PSSession -Session $sessionItem | Remove-PSSession

Code

Function Import-RvCsv
{
    .SYNOPSIS
        Similar to Import-RvCsv with different default settings and with additional functions.

    .DESCRIPTION
        Developer
            Developer: Rudolf Vesely, http://rudolfvesely.com/
            Copyright (c) Rudolf Vesely. All rights reserved
            License: Free for private use only

            "RV" are initials of the developer's name Rudolf Vesely and distingue names of Rudolf Vesely's cmdlets from the other cmdlets.

        Description
            Similar to Import-RvCsv with different default settings and with additional functions.

        Requirements
            Developed and tested using PowerShell 4.0.

    .PARAMETER PropertyCast
        It is possible to use all -Property* parameters but only a single action will be perfomed on each property in the following order: -PropertyCastBool, -PropertyCast, -PropertyCastBoolAll, -PropertySplit, -PropertySplitAll, -SplitDelimiter

        Define names of properties (columns in CSV) and their final type.

        Example of input:
            @(
                @{
                    PropertyName  = 'SmallNumber'
                    TypeName      = 'int'
                },
                @{
                    PropertyName  = 'BigNumber'
                    TypeName      = 'int64'
                }
            )

    .PARAMETER PropertyCastBool
        Names of properties (columns in CSV) that should be converted from string to boolean. "True" is $true and everything else is $false.

    .PARAMETER PropertyCastBoolAll
        Values in any column that contain text "True" or "False" (case sensitive) will be converted to boolean.

    .PARAMETER PropertySplit
        Names of properties (columns in CSV) that should be converted into array (splitted).

    .PARAMETER PropertySplitAll
        Try to split all values.

    .PARAMETER SplitDelimiter
        Delelimeter for split operations.

    .EXAMPLE
        'Local: Use default settings for import'
        Get-ChildItem -Path C:\Temp -Filter *.csv |
            Import-RvCsv `
                -ComputerName cont2test0.ad1.contoso.com, cont2test0.ad1.contoso.com |
            Format-Table -Property Created, SamAccountName, Enabled, MemberOf -Autosize

    .EXAMPLE
        'Local: Convert defined properties (values in defined CSV columns) into defined types'
        $propertyCast =
        @(
            @{
                PropertyName  = 'primaryGroupID'
                TypeName      = 'int'
            },
            @{
                PropertyName  = 'sAMAccountType'
                TypeName      = 'int64'
            }
        )

        Import-RvCsv `
            -Path 'C:\Temp\Data from Export-RvCsv.csv' `
            -PropertyCast $propertyCast `
            -OutVariable output |
            Select-Object -Property SamAccountName, primaryGroupID, sAMAccountType

        $output[0].primaryGroupID.GetType().FullName
        # Output: System.Int32
        $output[0].sAMAccountType.GetType().FullName
        # Output: System.Int64

    .EXAMPLE
        'Local: Convert defined column into boolean; Everything else then string "True" is $false'
        Import-RvCsv `
            -Path 'C:\Temp\Data from Export-RvCsv.csv' `
            -PropertyCastBool isCriticalSystemObject `
            -OutVariable output |
            Select-Object -Property Created, SamAccountName, isCriticalSystemObject, MemberOf

        $output[0].isCriticalSystemObject.GetType().FullName
        # Output: System.Boolean

    .EXAMPLE
        'Local: Convert all occurrences of "True" and "False" strings into boolean'
        Import-RvCsv `
            -Path 'C:\Temp\Data from Export-RvCsv.csv' `
            -PropertyCastBoolAll `
            -OutVariable output |
            Select-Object -Property Created, SamAccountName, Enabled, MemberOf

        $output[0].Enabled.GetType().FullName
        # Output: System.Boolean

    .EXAMPLE
        'Local: Split values in defined properties (values in defined CSV columns) into arrays'
        Import-RvCsv `
            -Path 'C:\Temp\Data from Export-RvCsv.csv' `
            -PropertySplit MemberOf `
            -SplitDelimiter ';' `
            -OutVariable output |
            Select-Object -Property SamAccountName, MemberOf

        $output[0].MemberOf.GetType().FullName
        # Output: System.String[]

    .INPUTS

    .OUTPUTS

    .LINK
        https://techstronghold.com/
    #>

    [CmdletBinding(
        DefaultParametersetName = 'Path',
        SupportsShouldProcess = $true,
        PositionalBinding = $false,
        HelpURI = 'https://techstronghold.com/',
        ConfirmImpact = 'Medium'
    )]

    Param
    (
        [Parameter(
            Mandatory = $true,
            Position = 0,
            ParameterSetName = 'Path',
            ValueFromPipelineByPropertyName = $true
        )]
        [ValidateLength(1, 255)]
        [Alias('FullName')]
        [string]$Path,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [AllowEmptyString()]
        [ValidateLength(0, 10)]
        [string]$Delimiter = "`t",

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [System.Collections.Hashtable[]]$PropertyCast,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [ValidateLength(1, 127)]
        [string[]]$PropertyCastBool,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [switch]$PropertyCastBoolAll = $false,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [ValidateLength(1, 127)]
        [string[]]$PropertySplit,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [switch]$PropertySplitAll,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [ValidateLength(1, 10)]
        [string]$SplitDelimiter = ';',

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [ValidateSet(
            'ASCII',
            'UTF8'
        )]
        [string]$Encoding = 'UTF8',

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [string[]]$Header,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [AllowNull()]
        [ValidateLength(1, 255)]
        [string[]]$ComputerName,

        [Parameter(
            Mandatory = $false
            # Position = ,
            # ParameterSetName = ''
        )]
        [AllowNull()]
        [System.Management.Automation.Runspaces.PSSession[]]$Session
    )

    Begin
    {
        # Configurations
        $ErrorActionPreference = 'Stop'
        if ($PSBoundParameters['Debug']) { $DebugPreference = 'Continue' }
        Set-PSDebug -Strict
        Set-StrictMode -Version Latest

        #region Functions
        Function Import-RvCsvProcess
        {
            [CmdletBinding(
                DefaultParametersetName = 'Path',
                SupportsShouldProcess = $true,
                PositionalBinding = $true,
                HelpURI = 'https://techstronghold.com/',
                ConfirmImpact = 'Medium'
            )]

            Param
            (
                [Parameter(
                    Mandatory = $true,
                    Position = 0,
                    ParameterSetName = 'Path'
                )]
                [string[]]$Path,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [string]$Delimiter,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [AllowNull()]
                [System.Collections.Hashtable[]]$PropertyCast,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [AllowNull()]
                [string[]]$PropertyCastBool,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [bool]$PropertyCastBoolAll,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [AllowNull()]
                [string[]]$PropertySplit,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [bool]$PropertySplitAll,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [string]$SplitDelimiter,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [string]$Encoding,

                [Parameter(
                    Mandatory = $true
                    # Position = ,
                    # ParameterSetName = ''
                )]
                [AllowNull()]
                [string[]]$Header
            )

            Begin
            {
                # Configurations
                $ErrorActionPreference = 'Stop'
                if ($PSBoundParameters['Debug']) { $DebugPreference = 'Continue' }
                Set-PSDebug -Strict
                Set-StrictMode -Version Latest
            }

            Process
            {
                foreach ($pathItem in $Path)
                {
                    $parametersAndArguments =
                    @{
                        Path       = $pathItem
                        Delimiter  = $Delimiter
                        Encoding   = $Encoding
                    }
                    if ($Header) { $parametersAndArguments.Add('Header', $Header) }

                    $items = Import-Csv @parametersAndArguments

                    if ($PropertyCast -or $PropertyCastBool -or $PropertyCastBoolAll -or $PropertySplit -or $PropertySplitAll)
                    {
                        $outputItems = @()

                        foreach ($item in $items)
                        {
                            $itemModified = New-Object -TypeName PSObject

                            foreach ($property in $item.PSObject.Properties)
                            {
                                $name     = $property.Name
                                $dataType = $property.TypeNameOfValue
                                $value    = $property.Value

                                Write-Verbose -Message ('    - {0} ({1}): {2}' -f $name, $dataType, $value)

                                if ($value -ne $null)
                                {
                                    $castItem = $(if ($PropertyCast) { $PropertyCast | Where-Object -Property PropertyName -EQ -Value $name })

                                    if ($castItem)
                                    {
                                        if ($castItem.TypeName -eq 'bool')
                                        {
                                            if     ($value -ceq 'True')  { $value = $true }
                                            else                         { $value = $false }
                                        }
                                        elseif ($castItem.TypeName -eq 'int' -or $castItem.TypeName -eq 'int64')
                                        {
                                            try
                                            {
                                                if ($castItem.TypeName -eq 'int') { $cast  = [int]$value }
                                                else                              { $cast  = [int64]$value }
                                                $value = $cast
                                            }
                                            catch
                                            {
                                                Write-Warning -Message ('Parameter: PropertyCast; Property: Name: "{0}"; Value: "{1}"; Type: {2}; Cannot cast' -f $name, $value, $castItem.TypeName)
                                            }
                                        }
                                        else
                                        {
                                            Write-Warning -Message ('Parameter: PropertyCast; TypeName is not defined: {0}' -f $castItem.TypeName)
                                        }
                                    }

                                    if ($PropertyCastBool -contains $name)
                                    {
                                        if     ($value -ceq 'True')  { $value = $true }
                                        else                         { $value = $false }
                                    }

                                    if ($PropertyCastBoolAll)
                                    {
                                        if     ($value -ceq 'True')  { $value = $true }
                                        elseif ($value -ceq 'False') { $value = $false }
                                    }

                                    if ($PropertySplitAll -or $PropertySplit -contains $name)
                                    {
                                        $value = $value -split $SplitDelimiter
                                    }
                                }

                                $itemModified |
                                    Add-Member -MemberType NoteProperty `
                                    -Name $name `
                                    -Value $value
                            }

                            $outputItems += $itemModified
                        }
                    }
                    else
                    {
                        $outputItems = $items
                    }

                    # Return
                    $outputItems
                }
            }

            End
            {
            }
        }
        #endregion
    }

    Process
    {
        # Remote device: Session
        if ($Session)
        {
            foreach ($sessionItem in $Session)
            {
                Invoke-Command `
                    -Session $sessionItem `
                    -ArgumentList $Path, $Delimiter, $PropertyCast, $PropertyCastBool, $PropertyCastBoolAll, $PropertySplit, $PropertySplitAll, $SplitDelimiter, $Encoding, $Header `
                    -ScriptBlock ${Function:Import-RvCsvProcess}
            }
        }
        else
        {
            foreach ($computerNameItem in $(if ($ComputerName) { $ComputerName } else { '.' } ))
            {
                # Local device
                if (!$computerNameItem -or $computerNameItem -eq '.' -or $computerNameItem -eq $env:COMPUTERNAME)
                {
                    Import-RvCsvProcess `
                        -Path                $Path `
                        -Delimiter           $Delimiter `
                        -PropertyCast        $PropertyCast `
                        -PropertyCastBool    $PropertyCastBool `
                        -PropertyCastBoolAll $PropertyCastBoolAll `
                        -PropertySplit       $PropertySplit `
                        -PropertySplitAll    $PropertySplitAll `
                        -SplitDelimiter      $SplitDelimiter `
                        -Encoding            $Encoding `
                        -Header              $Header
                }

                # Remote device: ComputerName
                else # if ($ComputerName)
                {
                    Invoke-Command `
                        -ComputerName $computerNameItem `
                        -ArgumentList $Path, $Delimiter, $PropertyCast, $PropertyCastBool, $PropertyCastBoolAll, $PropertySplit, $PropertySplitAll, $SplitDelimiter, $Encoding, $Header `
                        -ScriptBlock ${Function:Import-RvCsvProcess}
                }
            }
        }
    }

    End
    {
    }
}

Technology Stronghold