How to validate Excel using PowerShell?

ImportExcel - PowerShell module

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

The Microsoft Excel spreadsheet is very flexible, users can put whatever they want and it will accept it – even if it does not make much sense.

Data Validation in Excel

Luckily there is a feature called Data Validation (under Data > Data Tools).

Data Validation in Microsoft Excel

The feature helps to control the values in the spreadsheet, so for example we do not end up with negative age.

Validation rules

The Microsoft Excel offers a handful of rules for the data validation:

  • Any value – default with no restrictions
  • Whole number and Decimal
  • List
  • Date and Time
  • Text Length
  • Custom – limited by the user’s imagination

Additionally we can decide weather the blank cells should be included or not.

Input Message

That settings will result in a note-like pop-up when the cell is selected. It won’t stop the user from adding invalid data though.

Error Alert

To prevent putting in the wrong data the cells need to be protected by error alert once the data is entered.

Preparation

Let’s prepare our environment now to make sure the file does not exist.

# set location for the files - I am using the temporary folder in my user profile
Set-Location $env:TEMP

# that is the location of our files
Invoke-Item $env:TEMP

# cleanup any previous files - this helps in case the example has been already ran
$excelFiles = "ImportExcelHowTo006.xlsx"
Remove-Item $excelFiles -ErrorAction SilentlyContinue

In order to show the example we need some data, so let’s create a spreadsheet with some important data.

# create a file with some data
$data = ConvertFrom-Csv -InputObject @"
EmpID,Name,MiddleInitial,Email,Age,Hair,Planet
1,James Mary,A,[email protected],30,yes,Vogsphere
2,Patricia John,B,[email protected],20,blonde, Coruscant
3,Jennifer Robert,C,[email protected],10,dark,Krypton
4,Michael Linda,D,[email protected],40,no,Arda
5,Elizabeth William,E,[email protected],50,long,Melmac
"@
$data | Export-Excel -WorksheetName HowToValidate -Path $excelFiles

In addition, the data validation can use a lookup table for the lists therefore we will add one more tab to our file.

# add lookup sheet with values for the drop-down list
'Arda','Coruscant','Krypton','Melmac','Vogsphere' | Export-Excel -WorksheetName Lookup -Path $excelFiles

Data Validation with Powershell

In this section, I am going to put all the validation rules using Add-ExcelDataValidationRule function in one script, so it’s easier to copy and paste. Each part has it’s own description too. All examples include the -ShowErrorMessage switch so the rules actually prevent from adding invalid data.

# open the package
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# validation rule - TextLength
# allows strings with the number of characters between 2 and 100
Add-ExcelDataValidationRule -Worksheet $excel -Range "B2:B100" -ValidationType TextLength -Operator between -Value 2 -Value2 100 -ShowErrorMessage

# validation rule - TextLength + Prompt one selecting the cells
# allows single character to be entered
# when the cell is selected the user will get a pop-up
Add-ExcelDataValidationRule -Worksheet $excel -Range "C2:C100" -ValidationType TextLength -Operator equal -Value 1 -ShowPromptMessage -PromptTitle Initial -PromptBody 'Use one letter' -ShowErrorMessage

# validation rule - Integer + error
# only integers over 0, i.e. positive will be accepted
Add-ExcelDataValidationRule -Worksheet $excel -Range "E2:E100" -ValidationType Integer -Operator greaterThan -Value 0 -ShowErrorMessage -ErrorStyle stop -ErrorTitle 'Negative age' -ErrorBody 'Be a little more positive, use a value > 0'

# validation rule - internal list
# use the list configured inside the validation rule
Add-ExcelDataValidationRule -Worksheet $excel -Range "F2:F100" -ValidationType List -ValueSet @('yes','no','somewhat') -ShowErrorMessage -ErrorStyle warning -ErrorTitle 'Pick from the list' -ErrorBody 'Pick on value from the list'

# validation rule - a list from the lookup table
# use the list from the lookup worksheet
Add-ExcelDataValidationRule -Worksheet $excel -Range "G2:G100" -ValidationType List -Formula 'Lookup!a$1:a$10' -ShowErrorMessage

# validation rule - custom formula - valid emails
# use formula to validate data
# some string magic from https://www.extendoffice.com/documents/excel/4303-excel-data-validation-email-address.html
Add-ExcelDataValidationRule -Worksheet $excel -Range "D2:D100" -ValidationType Custom -Formula '=ISNUMBER(MATCH("*@*.?*",D2,0)) ' -ShowErrorMessage

# validation rule - custom formula - avoid duplicates
# another example of the formula
Add-ExcelDataValidationRule -Worksheet $excel -Range "A2:A100" -ValidationType Custom -Formula 'COUNTIF(A$2:A$100,A2) = 1' -ShowErrorMessage

# save the package and open the spreadsheet
Close-ExcelPackage $excelPackage -Show

Data validation in Excel

After we run the script, we will see this raw Excel spreadsheet:

Raw spreadsheet with hidden data validation

Before we go any deeper into the data validation rules, let’s have a look at one interesting feature (unfortunately I was not able to replicate it with PowerShell).

Circle Invalid Data

The feature is called Circle Invalid Data, and we can enable it from the same menu as the Data Validation.

Circle Invalid Data

When we select that our worksheet looks like a test marked by the teacher (at least that’s how it looked for me).

Invalid Data marked in the worksheet

After that, all the data that do not match the rules defined in the previous section are marked by red circle:

  • column A: duplicate values
  • column C: string is too long
  • column D: invalid email address
  • column F: values that are not on the list

It is a very interesting feature if we are validating the existing data. Unfortunately, after we close and re-open the file the red circles are gone. Let’s move on to the Error Alerts.

Errors Alerts

The most common result would be the Error Alert – Stop to prevent from entering invalid data

Data Validation Error Alert with a custom message

We can use a generic one or customize both the title and the message. It won’t allow us to enter the data until it passes the check.

Another type of the Error Alert is the Warning. It will let the user know what’s wrong, but won’t prevent from entering the invalid data. Similarly, the Information type also allows the user to keep invalid data.

Error Alert – Warning

Summary

In conclusion, this week we’ve learned how to add the data validation to the spreadsheet using PowerShell function Add-ExcelDataValidationRule. We could also apply the validation rules to the existing data which shows where it is wrong.

Thank you,
Mikey

1 Comment on "How to validate Excel using PowerShell?"


Leave a Reply

%d bloggers like this: