Public/Csv/Test-ColumnIsValid.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
function Test-ColumnIsValid {
    <#
      .SYNOPSIS
      Validates a column value in a single CSV row.

      .DESCRIPTION
      It is useful in Get-CsvData / Get-ValidationRules to validate columns read from CSV row.
      It returns empty array if the value is valid, or array of error messages if it's invalid.
    
      .EXAMPLE
      $errors += Test-ColumnIsValid -Row $CsvRow -ColumnName 'Login' -NonEmpty -NotContains '?', ' '
      $errors += Test-ColumnIsValid -Row $CsvRow -ColumnName 'Name' -NonEmpty -NotContains '?'
      $errors += Test-ColumnIsValid -Row $CsvRow -ColumnName 'StartDate' -DateFormat 'yyyy-MM-dd'
      $errors += Test-ColumnIsValid -Row $CsvRow -ColumnName 'Gender' -ValidSet '', 'F', 'M'
    #>

    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSPlaceCloseBrace', '')]
    [CmdletBinding()]
    [OutputType([string[]])]
    param (
        # CSV row (or any other PSCustomObject).
        [Parameter(Mandatory = $true)]
        [PSCustomObject]$Row,
        
        # Name of the column which will be validated.
        [Parameter(Mandatory = $true)]
        [string]$ColumnName,
        
        # If $true, it will be asserted the column value is not empty.
        [Parameter(Mandatory = $false)]
        [switch]$NonEmpty,
        
        # If specified, it will be asserted the column value does not contain any of the specified string.
        [Parameter(Mandatory = $false)]
        [string[]]$NotContains,
        
        # If specified, it will be asserted the column value contain any of the specified string.
        [Parameter(Mandatory = $false)]
        [string[]]$Match,
        
        # If specified, it will be asserted the column value is one of the specified string.
        [Parameter(Mandatory = $false)]
        [string[]]$ValidSet,
        
        # If specified, it will be asserted the column value can be converted to a date using specified format.
        [Parameter(Mandatory = $false)]
        [string]$DateFormat,
        
        # If specified, it will be asserted the column value is specified lenght.
        [Parameter(Mandatory = $false)]
        [int]$LengthMax,
        
        # If specified, it will be asserted the column value does not starts with any of the specified string.
        [Parameter(Mandatory = $false)]
        [string[]]$NotStartsWith,
        
        # If specified, it will be asserted the column value does not ends with any of the specified string.
        [Parameter(Mandatory = $false)]
        [string[]]$NotEndsWith
    )
    
    $errors = @()
    
    try {
        $value = $Row.$ColumnName
    } catch {
        $value = $null
    }
    
    if (!$value) {
        if ($NonEmpty) {
            $errors += "$ColumnName is missing"
        }
        return $errors
    }
    
    if ($NotContains) {
        foreach ($illegalChar in $NotContains) {
            if ([char[]]$value -icontains $illegalChar) {
                $errors += "$ColumnName has invalid value ('$value') - contains illegal character: '$illegalChar'"
            }
        }
    }
    
    if ($Match) {
        $ok = $false
        foreach ($legalString in $Match) {
            if ($value -imatch $legalString) {
                $ok = $true
                break
            }
        }
        if (!$ok) {
            $errors += "$ColumnName has invalid value ('$value') - should be one of '{0}'." -f ($Match -join "', ")
        }
    }
    
    if ($ValidSet) {
        $ok = $false
        foreach ($validValue in $ValidSet) {
            if ($value -ieq $validValue) {
                $ok = $true
                break
            }
        }
        if (!$ok) {
            $errors += "$ColumnName has invalid value ('$value') - should be one of '{0}'." -f ($ValidSet -join "', ")
        }
    }
    
    if ($DateFormat) {
        [datetime]$date = New-Object -TypeName DateTime
        $success = [DateTime]::TryParseExact($value, $DateFormat, [System.Globalization.CultureInfo]::InvariantCulture, [System.Globalization.DateTimeStyles]::None, [ref]$date)
        
        if (!$success) {
            $errors += "$ColumnName has invalid value ('$value') - should be a date in format '$DateFormat'"
        }
    }
    
    if ($LengthMax) {
        if ($value.Length -gt $LengthMax) {
            $errors += "$ColumnName has invalid value ('$value') - string length is greater then: '$LengthMax'"
        }
    }
    
    if ($NotStartsWith) {
        $valueTemp = [char[]]$value
        foreach ($illegalChar in $NotStartsWith) {
            if ($valueTemp[0] -ieq $illegalChar) {
                $errors += "$ColumnName has invalid value ('$value') - starts with illegal character: '$illegalChar'"
            }
        }
    }
    
    if ($NotEndsWith) {
        $valueTemp = [char[]]$value
        foreach ($illegalChar in $NotEndsWith) {
            if ($valueTemp[-1] -ieq $illegalChar) {
                $errors += "$ColumnName has invalid value ('$value') - ends with illegal character: '$illegalChar'"
            }
        }
    }
    
    return $errors
}