private/ConvertTo-NormalizedModelTable.ps1
|
function ConvertTo-NormalizedModelTable { <# .SYNOPSIS Validates and normalizes a table of imported rows against a known column schema. #> [CmdletBinding()] param ( # Rows as returned by Import-Csv or Import-Excel. $Rows, # Column schema as returned by Import-DataDictionarySchema for a single table. [PSCustomObject[]] $TableSchema, # Source identifier for error messages, e.g. 'path/to/file.csv' or 'path/to/file.xlsx|_Fields'. [string] $Source ) $ErrorActionPreference = 'Stop' [bool] $IsDebug = $DebugPreference -ne 'SilentlyContinue' [bool] $IsVerbose = $VerbosePreference -ne 'SilentlyContinue' # --- derive column sets from schema. @( prefix force-casts to array even if empty. [string[]] $MandatoryColumns = @($TableSchema | Where-Object { $_.ColumnType -eq 'M' } | Select-Object -ExpandProperty ColumnName) [string[]] $OptionalColumns = @($TableSchema | Where-Object { $_.ColumnType -eq 'O' } | Select-Object -ExpandProperty ColumnName) [string[]] $DynamicColumnPatterns = @($TableSchema | Where-Object { $_.ColumnType -eq 'D' } | Select-Object -ExpandProperty ColumnName) # --- derive column names from first row (schema check is column-level, not row-level) [string[]] $AllColumns = @($Rows | Select-Object -First 1 | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name) # --- only underscore-prefixed columns are import-relevant [string[]] $PrefixedColumns = $AllColumns | Where-Object { $_ -like '_*' } Write-Debug "MandatoryColumns: $($MandatoryColumns -join ', ')" Write-Debug "OptionalColumns: $($OptionalColumns -join ', ')" Write-Debug "DynamicColumnPatterns: $($DynamicColumnPatterns -join ', ')" Write-Debug "AllColumns: $($AllColumns -join ', ')" Write-Debug "PrefixedColumns: $($PrefixedColumns -join ', ')" # --- classify each prefixed column; collect dynamic columns; unknown columns are a terminating error [Collections.Generic.List[string]] $DynamicColumns = @() foreach ($Col in $PrefixedColumns) { $IsMandatory = $Col -in $MandatoryColumns $IsOptional = $Col -in $OptionalColumns $IsDynamic = $DynamicColumnPatterns | Where-Object { $Col -match $_ } if (-not ($IsMandatory -or $IsOptional -or $IsDynamic)) { Write-Warning "[$Source] Unknown column '$Col' — schema violated." } if ($IsDynamic) { $DynamicColumns.Add($Col) } } # --- check all mandatory columns are present foreach ($Col in $MandatoryColumns) { if ($Col -notin $PrefixedColumns) { Write-Warning "[$Source] Mandatory column '$Col' is missing." } } # --- determine final column set [string[]] $FinalColumns = @($MandatoryColumns) + @($OptionalColumns) + $DynamicColumns | Select-Object -Unique # --- pre-build ValidValues and ContentType lookups: column name -> valid values / content-type requirement [hashtable] $ValidValuesMap = @{} [hashtable] $ContentTypeMap = @{} foreach ($Col in $FinalColumns) { $SchemaDef = $TableSchema | Where-Object { ($_.ColumnType -ne 'D' -and $_.ColumnName -eq $Col) -or ($_.ColumnType -eq 'D' -and $Col -match $_.ColumnName) } $ValidValuesMap[$Col] = $SchemaDef.ValidValues $ContentTypeMap[$Col] = $SchemaDef.ContentType } # --- select final columns once for all rows [array] $NormalizedRows = $Rows | Select-Object -Property $FinalColumns # --- select final columns, normalize null to empty string, validate values for ($i = 0; $i -lt $NormalizedRows.Count; $i++) { $NormalizedRow = $Rows[$i] | Select-Object -Property $FinalColumns foreach ($Col in $FinalColumns) { if ($null -eq $NormalizedRow.$Col) { $NormalizedRow.$Col = '' } else { $NormalizedRow.$Col = [string] $NormalizedRow.$Col } if ([string]::IsNullOrEmpty($NormalizedRow.$Col)) { if ($ContentTypeMap[$Col] -eq 'M') { Write-Warning "[$Source] Row $($i + 1): Mandatory column '$Col' is empty." } } elseif ($null -ne $ValidValuesMap[$Col] -and $NormalizedRow.$Col -notin $ValidValuesMap[$Col]) { Write-Warning "[$Source] Row $($i + 1): Invalid value '$($NormalizedRow.$Col)' in column '$Col' — expected one of: $($ValidValuesMap[$Col] -join ', ')." } } Write-Output $NormalizedRow } } |