commas in cells in a csv, remove all

ryanstanford2 · 2018-01-17T19:39:17.000Z

Good Afternoon,

I’m working with an insanely huge csv file that I’m in process of trimming out the extra columns that aren’t needed via powershell. My issue is that there are multiple address cells, which can have commas in the individual cells.

I’d like to have a more elegant solution than:

$FileData | ForEach-Object{if($_.Address116){$_.Address116 = $_.Address116.replace(',','')}}
$FileData | ForEach-Object{if($_.Address217){$_.Address217 = $_.Address217.replace(',','')}}
$FileData | ForEach-Object{if($_.Address117){$_.Address117 = $_.Address117.replace(',','')}}
$FileData | ForEach-Object{if($_.Address130){$_.Address130 = $_.Address130.replace(',','')}}
$FileData | Foreach-object{if($_.PartnerPlanID155){$_.PartnerPlanID155 = $_.PartnerPlanID155.replace(',','')}}

This works, but I need to individually identify each column…

I’d like something like this, but it doesn’t work:

$ENHeaders = [System.Collections.ArrayList]@('Address116';'Address217';'Address117')

$FileData | foreach($x -in $enheaders){ForEach-Object{$_.($x) = $_.($x) -replace ",",""}}

$ENHeaders is a lot longer, trimmed it down for example purposes.

What am I missing here? I think it’s where I’m trying to identify the $x in the foreach, and using it as the column name in the foreach-object.

ryanstanford2 · 2018-01-17T20:28:02.000Z

I realized I had it out of order some… I got it to work with the code below:

foreach($x in $enheaders){
$FileData | ForEach-Object{if(($_.($x))){($_.($x)) = ($_.($x)).replace(',','')}}
}