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

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
}

CodePudding user response：

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"

CodePudding user response：

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"