Remove commas from a csv column using powershell

Question

I have the following code which removes all the commas in my csv file but there is an issue:

I only want to remove commas on Column P and and the remaining data should be untouched. Currently it appends data underneath the csv data.

$inform = Get-Content C:\Users\bmd\Desktop\report.csv
$inform.ToString()
$y=$inform -replace ',' -replace ''
$y  | Out-file C:\Users\bmd\Desktop\report.csv -Append

Does your CSV use the comma as the column separator? Does your csv have a header row? — AdminOfThings
– AdminOfThings, Commented Sep 28, 2020 at 21:42
What type of information is in column P? It would be helpful for you to provide this. I would use import-csv rather than get-content if the csv isn't comma delimited. — shadow2020
– shadow2020, Commented Sep 28, 2020 at 21:48
-Append adds the output to the end of an existing file. I think what you are looking for is a way to first manipulate the content of the CSV then write back that (entire) content to the same file? — Rno
– Rno, Commented Sep 28, 2020 at 21:53
Hi,yes my csv has headers.. Basically the columns A through O in my csv file are all separate columns and the data is structured.. it’s only the data in column P which is comma delimited with quotes which I would like to replace comma with nothing. Overall goal is to just replace comma in column p with nothing and save the csv as it is.. — AJ SCHUMAKER
– AJ SCHUMAKER, Commented Sep 29, 2020 at 2:44

AdminOfThings · Accepted Answer · 2020-09-29 14:00:06Z

1

Using Import-Csv and Export-Csv is usually going to be easier than trying to manipulate strings in a CSV. With no sample of the CSV file, we can only make assumptions. Assuming it contains true and qualified as needed CSV data, you can try the following:

$csv = Import-Csv C:\Users\bmd\Desktop\report.csv
foreach ($row in $csv) {
    $row.UNSTRUCTURED_VARS = $row.UNSTRUCTURED_VARS -replace ','
}
$csv | Export-Csv C:\Users\bmd\Desktop\report.csv -NoType

Note that if you are on PowerShell Core or PowerShell 7, you can simply use Export-Csv with -UseQuotes AsNeeded and only the required fields will be qualified.

edited Sep 29, 2020 at 14:00

answered Sep 28, 2020 at 22:42

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

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

4 Comments

AJ SCHUMAKER Over a year ago

Hi @AdminOfThingsI Can you please check if the code below is right as it does nothing.

AdminOfThings Over a year ago

Then you should post a sample of your CSV file so we don't have to guess. You also need to remove -Header $headers if your file has headers.

AJ SCHUMAKER Over a year ago

You can download the file here. Colum P I don't need commas: esquirebank.box.com/s/y187a3oqz4u0fhpz3mq1sdit02pepusk

AdminOfThings Over a year ago

I made a new edit that will exclusively use the *-Csv commands.

Collectives™ on Stack Overflow

Remove commas from a csv column using powershell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related