How to search imported CSV cell values for each row

Question

I'm attempting to import a csv, search the cell values(columns) in each row and then find and count any null or blank values. Then if the count hits 13, do X. However, when I run this code it appears the cell/column values are a single object and not the individual values for each column? How can I search the individual cell values in the row?

SAMPLE CODE

$DataFileLocation = "\\Server\Output.csv"
$sheet = import-csv $DataFileLocation
$count = 0
foreach ($row in $sheet) {
    foreach ($column in $row) {
        Write-Host "Searching value: $column"
        if ($column -eq $null -or " ") {
            Write-Host "Found a blank!"
            $count++
        }
    }
    $count
    if ($count -eq 13) {
        Write-Host "Found match!" -ForegroundColor Red
    }
    $count = 0
}

score 5 · Accepted Answer · edited Sep 06 '18 at 07:56

5

Each $row is a PSCustomObject:

PS > $row.GetType().Name
PSCustomObject

Access the underlying property values:

foreach ($column in $row.psobject.Properties.Value){
...
}

Check if $column is $null, empty or white space:

if ([System.String]::IsNullOrWhiteSpace($column)) { ... }

edited Sep 06 '18 at 07:56

Clijsters

4,031
1
27
37

answered Sep 05 '18 at 21:14

Nas

1,243
6
7

That appears to have worked, thank you! However, I did run into an error "system.outofmemoryException". Likely because my data set is so huge, any ideas on how to address this without splitting up the original file? One other thing (bonus points) how can I export the results to a csv and have it be in the same format e.g. same column headers and then append each row? – MrMr Sep 06 '18 at 08:44
Forgot to mention the outofmemoryexception occurs when I import the csv. It's like 500 MB, it's a beast. – MrMr Sep 06 '18 at 09:29
Good news, I switched to the 64 bit version and that solved the memory exception issue. Now just to export the data back into a csv format. =) – MrMr Sep 06 '18 at 09:37
before the `foreach` loop define your output array, ex `$outputsheet = @()` In the loop `if ($count -eq 13) {$outputsheet += $row}` After the loop `$outputsheet | Export-Csv "\\Server\Outputsheet.csv"` – Nas Sep 06 '18 at 19:29

score 1 · Answer 2 · answered Sep 05 '18 at 21:17

1

Or something like

$DataFileLocation = "\\Server\Output.csv"
$sheet = import-csv $DataFileLocation
$count = 0
foreach ($row in $sheet){
  ($row | Get-Member) | ? { $_.MemberType -eq "NoteProperty" } | % { 
    $n = $_.Name; "$n = $($row."$n")" 
  }
}

answered Sep 05 '18 at 21:17

No Refunds No Returns

8,092
4
32
43

I voted for @nas but this was my feeble attempt before his post was visible. – No Refunds No Returns Sep 05 '18 at 21:30

How to search imported CSV cell values for each row

2 Answers2