Using Powershell to drop multiple rows from a csv based on multiple conditions

Question

I need to drop from multiple rows from a CSV. I've seen this code:

Get-Content "file.csv" | Where {$_.Column -ne "Known Value" |Export new.csv

But I can't figure out how to get it to work for my purposes. I have imported the CSV as a system object array. I am comparing all cells in the CSV to a hashtable as @{"column": "values"}. Anyone can go in and change these depending on what they need to remove from the CSV.

Here is the hashtable:

#data_entry.ps1 #file name

$remove_value_row = @{
    "First_Name" = @("Benjamin","Ben");
    "Last_Name" = @("Armour");
}

Here is my code:

#drop_rows.ps1
. .\data_entry.ps1

$csv = import-csv "\droprows.csv" 




$csv |Where-Object {
    for($i=0;$i -lt $csv.length;$i++){
            foreach($key in $remove_value_row.Keys){
            -not($remove_value_row[$key] -contains $csv[$i].($key))
            #drop where a csv column 1. matches a key ,
            #2. the cell value is one of the key-values.
                }
            }

} | Export "out.csv"

Hmmm. I'm curious to what the answer of this will be. I instantiate an instance of the C# File Reader in Powershell when doing tasks like this so I can read line for line instead of using Get-Content. — Kellen Stuart
– Kellen Stuart, Commented Feb 1, 2017 at 19:01
Hi, your code is broken, please fix it. At least an if and a few brackets. I formatted the code so you can see it but your edit overwrote mine. — sodawillow
– sodawillow, Commented Feb 1, 2017 at 19:06
Thank you, I have added the missing brackets and taken away the extra parenthesis. — user6741414
– user6741414, Commented Feb 1, 2017 at 19:09
@sodawillow I see what you mean. I've fixed the grammar and made the code clearer. — user6741414
– user6741414, Commented Feb 1, 2017 at 20:07

woxxom · Accepted Answer · 2017-02-01 21:49:46Z

1

Your code enumerates the entire CSV per each row so for 10k rows it'll do 100 million iterations...

Let's start by simplifying the hashtable definition a bit since the field names don't contain spaces:

$remove_value_row = @{
    First_Name = "Benjamin", "Ben"
    Last_Name  = "Armour"
}

Now let's organize the pipeline properly: import - conditional filter - export

Import-Csv 'R:\input.csv' |
    Where {
        foreach ($remove in $remove_value_row.GetEnumerator()) {
            if ($remove.value -contains $_.($remove.name)) {
                return $false
            }
        }
        return $true
    } |
    Export-Csv 'R:\output.csv' -NoTypeInformation -Encoding UTF8

answered Feb 1, 2017 at 21:49

woxxom

75.2k15 gold badges161 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Powershell to drop multiple rows from a csv based on multiple conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related