Delete rows in a .CSV file containing specific character with Powershell

Question

I receive an automatic weekly export from a system in a .csv format. It contains a lot of usernames with the initials of the users (e.g. "fl", "nk"). A few of them have their first and last names, separated by coma (e.g. firstname.lastname). These are the ones, which have to be deleted from the .csv file.

My goal here is to write a Powershell script, which delete all rows, containing the character "." (dot) and then save the same .csv file by overwritting it.

Since I'm very new to Powershell, I'd highly appreciate a more detailed answer including the potential code. I tried various examples from similar issues, which I found here, but none of them worked and/or I am getting error messages, mostly because my syntax isn't correct.

Additional info. Here is a part of the table.

I tried this code:

Get-Content "D:\file.csv" | Where-Object {$_ -notmatch '\.'} | Set-Content "D:\File.csv"-Force -NoTypeInformation

Maybe if you showed us the code that isn't working we could try and help you fix it :) — Mathias R. Jessen, Aug 24 '21 at 08:41
Thanks for the reply! I added my effort to the original post above ;) — Embargo96, Aug 24 '21 at 09:24

score 1 · Accepted Answer · answered Aug 24 '21 at 08:56

1

As Mathias says, it is helpful to see what you have tried so we can help you come to a working result. It is easy to give you something like this:

$csv = Import-Csv -Path C:\Temp\temp.csv -Delimiter ";"
$newCSV = @()
foreach($row in $csv){
    if(!$row.username -or $row.username -notlike "*.*"){
        $newCSV += $row
    }
}
$newCSV | Export-Csv -Path C:\Temp\temp.csv -Delimiter ";" -NoTypeInformation

The above code eliminates rows that have a dot on the username field. It leaves rows with an empty username intact with the 'if(!$row.username' part. But I have no idea whether this is helpful since there is no example CSV file, also there is no way to know what you have tried so far ;)

Note that I always prefer using ";" as delimiter, because opening the file in Excel will already be correctly seperated. If the current file uses ',' as a delimiter, you will need to change that when importing the CSV.

answered Aug 24 '21 at 08:56

Bernard Moeskops

1,203
1
12
16

Thanks for the detailed reply! I will try this out. It definitely looks better than my one line of code ;) I added an update to the original post with more information. – Embargo96 Aug 24 '21 at 09:24
2

Try to [avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it is exponential expensive. – iRon Aug 24 '21 at 09:32
1

I just tried it and it works! Solved my issue! Thanks a lot!! – Embargo96 Aug 24 '21 at 09:43
Thanks @iRon for sharing your knowledge, I will look into this as I am using this a lot! Appreciate it :) – Bernard Moeskops Aug 24 '21 at 10:19

score 1 · Answer 2 · answered Aug 24 '21 at 13:10

You were very close! For this you don't need a loop, you just need to do it using the correct cmdlets:

(Import-Csv -Path 'D:\file.csv' -Delimiter ';') | 
    Where-Object { $_.Initials -notmatch '\.' } | 
    Export-Csv -Path 'D:\file.csv' -Delimiter ';' -Force -NoTypeInformation

Get-Content simply reads a text file and returns the lines as string array, whereas Import-Csv parses the structure and creates objects with properties from the header line.

The brackets around the Import-Csv are needed to ensure the importing/parsing of the file is completely done before piping the results through. Without that, the resulting file may become completely empty because you cannot read and overwrite the same file at the same time.

Delete rows in a .CSV file containing specific character with Powershell

2 Answers2