re: Hey, Scripting Guy! How Can I Remove Specified Commas from a Comma-Separated Values File?

jrv — Thu, 21 Jun 2012 19:40:58 GMT

The thing is that embedded commas are not an issue with CSV file types when they use quoted strings.

The ADO text provider and PowerSHell read files like this with no problems and no conversion needed.

In PosH just do this:

import-csv withcommas.csv -header F1,F2,F3,F4,F5

DM Clark — Thu, 21 Jun 2012 15:52:00 GMT

If you are working with standard files etc. like you get from prime brokers et al you can simplify this greatly. Example:

In file

"2012-06-20","MAC02TD34","03811111","MY TEXT WITH, WITH A COMMA.",2379.0000

"2012-06-20","MAC02TD34","03811112","MY TEXT WITH, WITH A COMMA.",2379.0000

Command

(Get-Content myFile.txt) -match '"03811111"' -replace '[A-Z],', '' -replace '"', '' > newFile.txt