baseModules/PPoShTools/0.1.21/Public/Csv/Get-CsvData.ps1

function Get-CsvData {

    <#
    .SYNOPSIS
        Reads CSV file using specific encoding and running optional Validation and Transformation rules.

    .DESCRIPTION
        After CSV file is read, Validation phase is run, that is for each row $CsvValidationRules scriptblock is invoked, which returns array of string:
            - if the array is empty it is assumed the row is valid.
            - if the array is non-empty, it is assumed the row is invalid and the strings will be displayed after the Validation phase.
        Then, Transformation phase is run, that is for each row $CsvTransformationRules scriptblock is invoked, which returns a hashtable that is then
        passed as a final result.
    
    .EXAMPLE
    function Get-ValidationRules {

        [CmdletBinding()]
        [OutputType([string[]])]
        param(
            [Parameter(Mandatory = $true)]
            [PSCustomObject]
            $CsvRow,

            [Parameter(Mandatory = $true)]
            [int]
            $CsvRowNum
        )

        $errors = @()
        $errors += Test-ColumnIsValid -Row $CsvRow -ColumnName 'Login' -NonEmpty -NotContains '?', ' '
        $errors += Test-ColumnIsValid -Row $CsvRow -ColumnName 'Name' -NonEmpty -NotContains '?'
        $errors += Test-ColumnIsValid -Row $CsvRow -ColumnName 'First Name' -NonEmpty -NotContains '?', ' '
        $errors += Test-ColumnIsValid -Row $CsvRow -ColumnName 'Last Name' -NonEmpty -NotContains '?'
    
        return $errors
    }

    function Get-TransformRules {

        [CmdletBinding()]
        [OutputType([hashtable])]
        param(
            [Parameter(Mandatory = $true)]
            [PSCustomObject]
            $CsvRow,

            [Parameter(Mandatory = $true)]
            [int]
            $CsvRowNum
        )

        $result = @{
            Name = Remove-DiacriticChars -String (($CsvRow.'Name'))
            FirstName = Remove-DiacriticChars -String (($CsvRow.'First Name'))
            LastName = Remove-DiacriticChars -String (($CsvRow.'Last Name'))
            Login = $CsvRow.Login
        }

        return $result
    }

    $csvParams = @{
        CsvPath = 'test.csv'
        CsvDelimiter = ';'
        CsvValidationRules = (Get-Command -Name Get-ValidationRules).ScriptBlock
        CsvTransformRules = (Get-Command -Name Get-TransformRules).ScriptBlock
        CustomEncoding = 'Windows-1250'
    }
    $employeeData = Get-CsvData @csvParams
    #>

    [CmdletBinding()]
    [OutputType([hashtable[]])]
    param(
        # Path to input CSV file.
        [Parameter(Mandatory = $true)]
        [string]
        $CsvPath,

        # CSV delimiter in input CSV file.
        [Parameter(Mandatory = $true)]
        [string]
        $CsvDelimiter,

        # If specified, CSV file will be first reencoded to UTF-8 (to a temporary file).
        [Parameter(Mandatory = $false)]
        [string]
        $CustomEncoding,

        # A scriptblock invoked for each row that accepts [PSCustomObject]$CsvRow and [int]$CsvRowNum. It returns array of string.
        [Parameter(Mandatory = $false)]
        [scriptblock]
        $CsvValidationRules,

        # A scriptblock ivoked for each row that accepts [PSCustomObject]$CsvRow and [int]$CsvRowNum. It returns hashtable.
        [Parameter(Mandatory = $false)]
        [scriptblock]
        $CsvTransformRules
    )

    if (!(Test-Path -LiteralPath $CsvPath)) {
        throw "Csv input file '$CsvPath' does not exist at $((Get-Location).Path)."
    }

    $tempFileName = ''
    try { 
        
        if ($CustomEncoding) {
            $inputEncodingObj = [System.Text.Encoding]::GetEncoding($CustomEncoding)
            $outputEncodingObj = [System.Text.Encoding]::GetEncoding('UTF-8')
            $tempFileName = [System.IO.Path]::GetTempFileName()
            Write-Log -Info "Converting file '$CsvPath' from $CustomEncoding to UTF-8 - saving as '$tempFileName'."
            $text = [System.IO.File]::ReadAllText($CsvPath, $inputEncodingObj)
            [System.IO.File]::WriteAllText($tempFileName, $text, $outputEncodingObj)
            $csvFileToRead = $tempFileName
        } 
        else {
            $csvFileToRead = $CsvPath
        }

        Write-Log -Info "Reading file '$csvFileToRead' using delimiter '$CsvDelimiter'"
        $inputData = Import-Csv -Path $csvFileToRead -Delimiter $CsvDelimiter -Encoding UTF8
        if (!$inputData) {
            throw "There is no data to import. Please check file '$CsvPath'."
        }
        Write-Log -Info "Read $($inputData.Length) rows."

        $emptyRows = @()
        $rowNum = 2
        foreach ($row in $inputData) { 
            $isRowEmpty = $true
            foreach ($prop in $row.PSObject.Properties.Name) {
                $row.$prop = $row.$prop.Trim()
                if ($row.$prop) {
                    $isRowEmpty = $false
                }
            }
            if ($isRowEmpty) {
                $emptyRows += $rowNum
            }
            $rowNum++
        }
    
        if ($emptyRows) {
            Write-Log -Info "Ignoring empty row numbers: $($rowNum -join ', ')."
        }

        # Run Validation Rules
        if ($CsvValidationRules) {
            $errorArray = New-Object -TypeName System.Collections.ArrayList
            $rowNum = 2

            Write-Log -Info 'Validating CSV data.'
            foreach ($row in $inputData) {
                if ($emptyRows -contains $rowNum) {
                    $rowNum++
                    continue
                }

                try { 
                    $errors = Invoke-Command -ScriptBlock $CsvValidationRules -ArgumentList $row, $rowNum
                } catch {
                    Write-ErrorRecord
                }
                foreach ($err in $errors) {
                    [void]($errorArray.Add("Validation error in row ${rowNum}: $err"))
                }
                $rowNum++
            }
    
            if ($errorArray) {
                $msg = "`r`n" + ($errorArray -join "`r`n")
                Write-Log -Error $msg
                throw "Input CSV file has not passed validation rules. Please fix the file and try again."
            }
        }

        $added = 0
        $ignored = 0
        # Run Transformation Rules
        if ($CsvTransformRules) {
            $resultArray = New-Object -TypeName System.Collections.ArrayList
            $rowNum = 2

            Write-Log -Info 'Transforming CSV data.'
            foreach ($row in $inputData) {
                if ($emptyRows -contains $rowNum) {
                    $rowNum++
                    $ignored++
                    continue
                }
                try { 
                    $resultRow = Invoke-Command -ScriptBlock $CsvTransformRules -ArgumentList $row, $rowNum
                } 
                catch {
                    Write-ErrorRecord -ErrorRecord $_
                }
                if ($resultRow) { 
                    [void]($resultArray.Add($resultRow))
                    $added++
                } 
                else {
                    $ignored++
                }
                $rowNum++
            }
            Write-Log -Info "CSV file read successfully ($added rows returned, $ignored rows ignored)."
            return $resultArray
        } 
        else {
            return $inputData
        }
    } finally {
        if ($tempFileName -and (Test-Path -Path $tempFileName)) {
            Remove-Item -Path $tempFileName -Force
        }
    }
}