Excel File Data Validation for SQL Server Imports

add-type -path c:\tools\EPPlus\EPPlus.dll

<#
.SYNOPSIS
Validate data in Excel files (for Excel 2007 and later versions only)
.DESCRIPTION
Validate data in Excel files via Regular Expression or just common strings. The validation can be on
multiple columns, each with different validation criteria. 
.INPUTS
ExcelFile: The full UNC path to the Excel file;
Condition: Hashtable format, put ColumnName and its corresponding verification criteria in such format as
@{ColumnName='criteria'; ColumnName2='criteria2'; ...}
Sheet: indicates the Excel file sheet number, so that the verification will be carried on this sheet. The default value is 1
 
.OUTPUTS
System.Data.DataTable
    The data table will contain all abnormal data (i.e. failing the verification criteria), The data table will have 3 columns, Rows | ColumnName | Text, and this
    will assist quickly locating the abnormal data in the Excel sheet.
.EXAMPLE
Validate-ExcelData -ExcelFile C:\temp\Person.xlsx -Condition @{Phone='^\d{3}-\d{3}-\d{4}$'; Province='^\w{2}$'} -Sheet 1;
This command validates two columns in the first worksheet of c:\Temp\Person.xlsx, to verify that column [Phone] has the format of ddd-ddd-dddd (d is a digit number, 0 to 9).
It also verifies that column [Province] should have a value of two characters, like AB, ON, BC (these are all Canadian province name abbreviations)

#>

function Validate-ExcelData 
{

    param(
    [Parameter(Position=0, Mandatory=$true)] [ValidateScript ({test-path $_ -pathtype 'leaf'})]  [string]$ExcelFile,
    [Parameter(Position=1, Mandatory=$true)] [ValidateNotNullOrEmpty()] [hashtable]$Condition=@{}, # The format is "ColumnName='regular-expression'"
    [Parameter(Position=2, Mandatory=$false)] [ValidateScript ({ ($_ -gt 0) -and ($_ -le 100)})] [int]$Sheet=1
    )

    try 
    {    
        $result = New-Object "System.Data.DataTable"
        $c = New-Object "System.Data.DataColumn" ('Row', [System.Int32]);
        $result.Columns.Add($c);
        $c = New-Object "System.Data.DataColumn" ('Col', [System.String]);
        $result.Columns.Add($c);
        $c = New-Object "System.Data.DataColumn" ('Text', [System.String]);
        $result.Columns.Add($c);


    
        $xl = New-Object OfficeOpenXml.ExcelPackage $ExcelFile;

        $wb  = $xl.Workbook
        if ($wb.Worksheets.count -lt  $sheet) #$sheet cannot be beyond the existing WorkSheets
        {
            Write-Error "There is no [$sheet] sheet in this Excel file." -ea stop;
        }

        $ws=$wb.Worksheets[$Sheet]
        $dm=$ws.Dimension

        $Rows=$dm.Rows;
        $Columns=$dm.Columns;
    
        [string[]]$array_col= 1..$Columns| %{$ws.Cells[1, $_].Text.toupper()};

        [hashtable]$valid_cond=@{};
        $condition.keys | % {$valid_cond.add($_, $([System.Array]::indexof($array_col, $_.toupper())))}
        $valid_cond.GetEnumerator() | % {
            if ($_.Value -eq -1)
            {
                write-error "Column [$($_.Key)] cannot be found in Excel file [$ExcelFile]" -ea Stop; 
            }
        }

        foreach ($itm in $valid_cond.GetEnumerator())
        {
            foreach ($row in 2..$rows)
            {
                if ($ws.Cells[$row, ($itm.value+1)].text -notmatch $($Condition.get_item($itm.Key)))
                {
                    $r = $result.NewRow();

                    $r.Row = $row;
                    $r.Col = $itm.key;
                    $r.Text = $ws.Cells[$row, ($itm.value+1)].Text;
                    $result.rows.add($r);
                }
            }
        }#itm
        Write-Output $result;
    }#try
    catch
    {
        Write-Error $_;
    }

    finally 
    { 
        $result.Dispose();
        $result = $null;
        $xl.Dispose();
        $xl=$null;
    }
}
#uncomment and modify the following parameters to do you test in a PS ISE window 
#Validate-ExcelData -ExcelFile C:\temp\Person.xlsx -Condition @{Phone='^\d{3}-\d{3}-\d{4}$'; Province='^\w{2}$'} -Sheet 1;