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
– Mathias R. Jessen, Commented Aug 24, 2021 at 8:41
Thanks for the reply! I added my effort to the original post above ;) — Embargo96
– Embargo96, Commented Aug 24, 2021 at 9:24

Bernard Moeskops · Accepted Answer · 2021-08-24 08:56:08Z

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, 2021 at 8:56

Bernard Moeskops

1,2931 gold badge14 silver badges18 bronze badges

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

4 Comments

Embargo96 Over a year ago

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.

iRon Over a year ago

Try to avoid using the increase assignment operator (+=) to create a collection as it is exponential expensive.

Embargo96 Over a year ago

I just tried it and it works! Solved my issue! Thanks a lot!!

Bernard Moeskops Over a year ago

Thanks @iRon for sharing your knowledge, I will look into this as I am using this a lot! Appreciate it :)

Theo · Accepted Answer · 2021-08-24 13:10:29Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related