CODEWITHSUNDEEP
stringpowershellcsvtextquotes
Bryan W
Bryan W

Reputation: 1

How to maniuplate text in first column of CSV file with script

Have a CSV file with multiple columns with information. Need to remove the opening and closing " in the Employee Name as well as the , as seen below. 

Employee Name,Employee #,column3, column4 etc. <br>
"Lastname, Firstname",123,abc,xyz<br>
"Lastname, Firstname",123,abc,xyz<br>

Result:

Employee Name,Employee #,column3, column4 etc.<br>
Lastname Firstname,123,abc,xyz<br>
Lastname Firstname,123,abc,xyz<br>

Tried using the following Powershell script:

(gc C:\pathtocsv.csv) | % {$_ -replace '"', ""} | out-file C:\pathtocsv.csv -Fo -En ascii

This only removes the " " around Lastname , Firstname but the comma is still present when opening the csv file in a text editor. Need this format to send to data to another company. Everything I have tried removes every comma. Novice in powershell and other languages, I am sure this is an easy fix. Please help!

Upvotes: 0

Views: 418

Answers (2)

Paweł Dyl
Paweł Dyl

Reputation: 9143

Warning: quotes are important if text contains special characters (i.e. comma, quote)

If you really want to strip lines, you can process your csv as regular text file:

#sample data
@'
"Lastname, Firstname",123,abc,xyz
"Lastname, Firstname",123,abc,xyz
'@ | out-file c:\temp\test.csv

Get-Content c:\temp\test.csv | % {
    $match = [Regex]::Match($_,'"([^,]*), ([^"]*)"(.*)')
    if ($match.Success) {
        $match.Groups[1].Value+' '+$match.Groups[2].Value+$match.Groups[3].Value
    } else {
        $_ #skip processing if line format do not match pattern
    }
}

Upvotes: 1

colsw
colsw

Reputation: 3336

Powershell has a lot of built-in handling for CSV files, instead of trying to treat is as a text file you can use the following to remove just the comma you want:

Import-Csv .\a.csv | % {
    $_."Employee Name" = ($_."Employee Name" -replace ',','')
    $_ #return modified rows
} | Export-Csv .\b.csv -notype -delim ','

this will by default export everything with double quotes, so you may need to go back and run something like:

(gc .\b.csv -raw) -replace '"','' | Out-File .\c.csv

to also remove all the double quotes.

Upvotes: 1

Related Questions