[SOLVED] Creating a Powershell script to check a .csv file for two strings and an integer on each line

Creating a Powershell script to check a .csv file for two strings and an integer on each line

I have a .csv that I am looking to perform checks on before sending it to an RPA tool to automate an ETL process. The purpose of this script will be to validate each .csv for two strings, separated by a comma, and the 3rd data value is an integer. The first row is column headers and should be ignored. The sheet looks like this (Note that there ARE commas inside of the second string).

Each row MUST contain two strings of text and a number at the end - if any data is missing from each row we'd expect that to be a failure

This is what we will consider an 'approved' input file

ProductName,Description,Value
"Product Number One","This is a description for product number 1, released 5/3/2024",3.4563
"A different Product Number","This is a description for product number 2, released 3/2/2025",5.3200
"This is another product","Another unique description, released 6/3/2022",5.0000

Here is a sheet that should fail and return exit code 2

ProductName,Description,Value
"Product Number One","This is a description for product number 1, released 5/3/2024",
"A different Product Number",,5.3200
""Another unique description, released 6/3/2022",5.0000

I attempted to use this script, but I couldn't get it to work with even one data set let alone 3. It seemed to return condition 0 below no matter what unless I changed it to match which would not work

$File = 'c:\test\FileNameHere.csv'
$Contents = 'ProductName'

If (Get-Content $File | foreach {$_ -like $Contents}) 
{
    echo "File check successful"
    EXIT 0
}
else
{
    echo "Error with source file - manual review is required to proceed"
    EXIT 2
}

Any ideas?

Solution

I'm guessing you're looking for something like the function below. You can check the details on the regex pattern here: https://regex101.com/r/SkX8FY/2.

function validatething {
    param($path)

    foreach ($row in Get-Content $path | Select-Object -Skip 1) {
        if ($row -notmatch '^(?:".+",){2}[0-9]+(?:\.[0-9]+?)$') {
            return $false
        }
    }
    return $true
}

$File = 'c:\test\FileNameHere.csv'

if (validatething $File) {
    'File check successful'
    exit 0
}
else {
    'Error with source file - manual review is required to proceed'
    exit 2
}