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 { $_.Type -eq 'M' } | Select-Object -ExpandProperty Name) [string[]] $OptionalColumns = @($TableSchema | Where-Object { $_.Type -eq 'O' } | Select-Object -ExpandProperty Name) [string[]] $DynamicColumnPatterns = @($TableSchema | Where-Object { $_.Type -eq 'D' } | Select-Object -ExpandProperty Name) # --- 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 lookup: column name -> valid values (or $null if unconstrained) [hashtable] $ValidValuesMap = @{} foreach ($Col in $FinalColumns) { $SchemaDef = $TableSchema | Where-Object { ($_.Type -ne 'D' -and $_.Name -eq $Col) -or ($_.Type -eq 'D' -and $Col -match $_.Name) } $ValidValuesMap[$Col] = $SchemaDef.ValidValues } # --- select final columns, normalize null to empty string, validate values for ($i = 0; $i -lt $Rows.Count; $i++) { $NormalizedRow = $Rows[$i] | Select-Object -Property $FinalColumns foreach ($Col in $FinalColumns) { if ($null -eq $NormalizedRow.$Col) { $NormalizedRow.$Col = '' } if ($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 } } |