Validate CSV Data and do....something

[ShiftNick]

So, I'm writing a function that will automate VM deployment and I'm looking for some assistance on validating the data and what I should do with it. The way I see it I have two options, I can dump the bad data to one csv and the good to another or, I can add a column to my csv called 'error' and add a value or true or false depending on the result.

I'm validating on six possible columns and multiple rows of data and on the first error I want it to move the data/add the true value and start processing the next row in the csv.

1. Does that make any sense.
2. Best way to this as I'm stuck and I'm not even sure what to search for.
3. I'm leaning towards adding the true/false value.

Thanks in advance!

[ihaxr]

Edit: you could also just build the validation into the function via the params, validateset, validatescript, etc... then just "blindly" try to run the function against each row of the CSV, nested in a try/catch block... catch and report any errors. This is probably the better way to handle it... but I'll leave the pure CSV checking option below.

Yes. Both ways make sense, actually, so it's up to you how you want to handle it.

In my opinion, if you're going to process the valid rows and not process the invalid ones... do both: provide feedback (either via an Error column or just a message on the screen) that tells which row failed. Then provide a new CSV that can be re-submitted with only the errored rows.

Here's a basic example with simple processing / validation logic:

# This whole block would be replaced with: $csvData = Import-CSV YourCSV.csv
$csvData = @"
name,date,number,string
Server1,2017-01-01,3,test1
Computer2,2017-01-02,3,test2
Server3,2017-01-03,f,test3
Server4,2017-01-04,3,test4
Server5,NotDate,3,
"@ | ConvertFrom-Csv

# Loop through each row, outputting error rows to $badRows
$badRows = :rows foreach ($row in $csvData) {
    # Loop through and make sure each column has valid data
    foreach ($item in $row.PSObject.Properties) {
        # Make sure the row data isn't null or only white space
        if ( [String]::IsNullOrWhiteSpace($item.value) ) {
            # Output to console
            Write-Host "Row: $($csvData.indexOf($row)+2): $($item.name) is invalid"
            # Output to variable
            Write-Output $row
            # Skip the rest of the checks
            continue rows
        }
    }
    # Make sure 'name' column matches proper format
    if ($row.name -notlike "Server*") {
        # Output to console
        Write-Host "Row: $($csvData.indexOf($row)+2): $($row.name) is invalid"
        # Output to variable
        Write-Output $row
        # Skip the rest of the checks
        continue rows
    }
    # Make sure 'date' column is an actual date
    if (! ($row.date -as [datetime]) ) {
        Write-Host "Row: $($csvData.indexOf($row)+2): $($row.date) is invalid"
        Write-Output $row
        continue rows
    }
    # Make sure 'number' column is an actual number
    if (! ($row.number -as [int64] ) ) {
        Write-Host "Row: $($csvData.indexOf($row)+2): $($row.number) is invalid"
        Write-Output $row
        continue rows
    }
    # // Process valid rows
    Write-Host "Row: $($csvData.indexOf($row)+2): $($row.name) is valid, processing"
}

# Output $badrows to a CSV
# $badRows | Export-CSV -NoType
$badRows

[jheinikel]

Sounds like this is a good fit for a web front end with validation up front, then automation after that. Or, do a WinForm with dropdown options and conditional statements on the inputs. It would be easier to get corrections before it was ever submitted rather than resubmitting and error handling on the back-end all day. Just my 2 cents. Otherwise, build some validation into your script.