Powershell Replace characters in substring

Question

In formatting a CSV, I want to replace all the commas separating the values with the pipe character, which would be easy except I have a column of strings (have quotes around values) that occasionally has a comma in it.

How can I either replace all the commas with the pipe except ones within quotes? OR, alternatively, how can I replace only pipe characters inside quotes? Since it's easy enough to change all the commas to pipes.

Ansgar Wiechers · Accepted Answer · 2015-04-30 20:00:18Z

3

As always: use the right tool for the job. In this case the Import-Csv and Export-Csv cmdlets.

Import-Csv 'C:\input.csv' | Export-Csv 'C:\output.csv' -NoType -Delimiter '|'

answered Apr 30, 2015 at 20:00

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

3 Comments

TheMadTechnician Over a year ago

And here I thought gasoline and a book of matches was the right tool for this job... I guess that only works for getting rid of spiders. Superb answer as usual Ansgar. Hopefully @Hesed will come back and mark it as accepted.

Hesed Over a year ago

Thank you, @Ansger, that was it. It took me a little bit to get it to work, but I found out that Import-Csv cmdlet needed the file to be imported as a ".csv" file when used this way (I had previously saved it as a text file). So I just had to rearrange my lines.

Ansgar Wiechers Over a year ago

@Hesed The extension doesn't matter, but the file must have the correct delimiter. By default both Import-Csv and Export-Csv assume a comma, which can be overridden via the -Delimiter parameter.

Collectives™ on Stack Overflow

Powershell Replace characters in substring

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related