Remove unnecessary commas in a column in csv file by using PowerShell

Question

I am trying to Remove unnecessary commas in a column in the CSV file. For now, I know a few issues and hard-coded it, But I wanted the code to be dynamic. Any suggestions are greatly appreciated.

$FilePath = "C:\Test\"
Get-ChildItem $FilePath -Filter .csv | ForEach-Object {
(Get-Content $_.FullName -Raw) | Foreach-Object {
$_ -replace  ',"Frederick, Fred",' , ',"Frederick Fred",' `
-replace  ',"Brian, Josiah",' , ',"Brian Josiah",' ` 
-replace  ',"Lisinopril ,Tablet / 20MG",' , ',"Lisinopril Tablet / 20MG",'
} | Set-Content $_.FullName
}

Can you edit your question to provide the correct PowerShell version you're using, as well as a sanitized CSV list we can use to assist you with? — Abraham Zinala, Feb 17 '22 at 22:17
can you provide a sanitized CSV list we can use to work with? — Abraham Zinala, Feb 18 '22 at 13:49
@user10260125 I agree with above we need a sanitized csv. Otherwise we are going to give you bad answers. I have like 5 right now that might all put you in the wrong direction. — dcaz, Feb 18 '22 at 14:29

score 0 · Answer 1 · answered Feb 19 '22 at 10:40

Try this, also note that I worked with the csv sample that you gave here.It might not work with other csv files.

also make sure that you change the path of %YOURCSVFILE% to the real path of your file

#import the csv
$csv = Import-Csv -Path %YOURCSVFILE% -Delimiter ','

#going each row and replacing commas
foreach ($desc in $csv){
    $desc.Desc = $desc.Desc -replace ',',''
}
#exporting the csv
$csv | Export-csv -NoTypeInformation "noCommas.csv"

score 0 · Answer 2 · answered Feb 19 '22 at 13:37

Here's a few more alteratives for you:

Method 1. Loop through the rows with foreach(..) and capture the output:

$result = foreach ($row in (Import-Csv -Path 'D:\Test\FileWithCommasInDescription.csv')) {
    $row.Desc = $row.Desc -replace ','
    $row  # output the updated item
}
$result | Export-Csv -Path 'D:\Test\FileWithoutCommasInDescription.csv' -NoTypeInformation

Method 2. Use ForEach-Object and the automatic variable $_. Pipe the results through:

Import-Csv -Path 'D:\Test\FileWithCommasInDescription.csv' | ForEach-Object {
    $_.Desc = $_.Desc -replace ','
    $_  # output the updated item
} | Export-Csv -Path 'D:\Test\FileWithoutCommasInDescription.csv' -NoTypeInformation

Method 3. Use a calculated property:

Import-Csv -Path 'D:\Test\FileWithCommasInDescription.csv' | 
Select-Object ID, @{Name = 'Desc'; Expression = {$_.Desc -replace ','}}, Nbr  -ExcludeProperty Desc |
Export-Csv -Path 'D:\Test\FileWithoutCommasInDescription.csv' -NoTypeInformation

All will result in a new CSV file

"ID","Desc","Nbr"
"12","Frederick Fred","11"
"21","Brian Josiah","31"
"13","Lisinopril Tablet / 20MG","17"

Remove unnecessary commas in a column in csv file by using PowerShell

2 Answers2