Powershell remove commas from array

[ADMaster]

I am pulling data from the SIS again with sql. If there are report on it, but also fix it so the comma doesn't carry to the next system.

I can test the array for a comma, and echo a statement of what record has the comma, but I can't get it to replace it.

 

If I am working with a simple array, the replace works fine. If I write out each row property such as

$rec.scity = $rec.scity -replace ',','' It works.

I've considered two methods to fix this, but hoping there is a better way.

1. write out all fields with a $rec.field = $rec.field -replace

2. write to a tab delimited file then use gc -replace and write back to a file.

 

Is there a simple syntax / logical error I have in trying to do the replace in the foreach loop?

 

Thanks,

 

This simple array code works.

[color=#000000][font=Consolas]clear[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]$[/color]test =  [color=#0000ff]@[/color]([color=#a31515]'black'[/color],[color=#a31515]'blue,'[/color],[color=#a31515]'red'[/color],[color=#a31515]'green'[/color],[color=#a31515]'yellow'[/color])[/font][/color]
[color=#000000][font=Consolas]echo [color=#0000ff]$[/color]test[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]$[/color]test = [color=#0000ff]$[/color]test -replace [color=#a31515]','[/color],[color=#a31515]''[/color][/font][/color]
[color=#000000][font=Consolas]echo [color=#a31515]"corrected array[/color][/font][/color]
[color=#000000][font=Consolas][color=#a31515]"[/color][/font][/color]
[color=#000000][font=Consolas]echo [color=#0000ff]$[/color]test [/font][/color]

 

This simple array code mostly works. I can replace the comma from the entire array, but not individually.

[color=#000000][font=Consolas]clear[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]$[/color]test =  [color=#0000ff]@[/color]([color=#a31515]'black'[/color],[color=#a31515]'blue,'[/color],[color=#a31515]'red'[/color],[color=#a31515]'green'[/color],[color=#a31515]'yellow'[/color])[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]foreach[/color] ([color=#0000ff]$[/color]c [color=#0000ff]in[/color] [color=#0000ff]$[/color]test){[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]if[/color]([color=#0000ff]$[/color]c -like [color=#a31515]'*,*'[/color]){[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]$[/color]c = [color=#0000ff]$[/color]c -replace [color=#a31515]','[/color],[color=#a31515]''[/color] [color=#008000]#this line does not work[/color][/font][/color]
[color=#000000][font=Consolas]echo [color=#a31515]"[/color][color=#0000ff]$[/color][color=#a31515]c has an extra comma"[/color][/font][/color]
[color=#000000][font=Consolas]}[/font][/color][color=#000000][font=Consolas]}[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]$[/color]test = [color=#0000ff]$[/color]test -replace [color=#a31515]','[/color],[color=#a31515]''[/color] [color=#008000]#this line works[/color][/font][/color]
[color=#000000][font=Consolas]echo [color=#a31515]"Corrected array[/color][/font][/color]
[font=Consolas][color=#a31515]"[/color][/font]
[color=#000000][font=Consolas][color=#0000ff]$[/color]test [/font][/color]

 

This is the code set I'm working with $rec will have properties such as street and city, which a common places for extra commas to be.

[color=#000000][font=Consolas][color=#0000ff]foreach[/color] ([color=#0000ff]$[/color]rec [color=#0000ff]in[/color] [color=#0000ff]$[/color]ResultsArray){[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]if[/color] ([color=#0000ff]$[/color]rec -like [color=#a31515]'*,*'[/color])[/font][/color]
[color=#000000][font=Consolas]{[/font][/color]
[color=#000000][font=Consolas][color=#008000]#$rec = $rec -replace ',','' #does not work[/color]
[color=#0000ff]$[/color]rec.scity = [color=#0000ff]$[/color]rec.scity -replace [color=#a31515]','[/color],[color=#a31515]'' [/color][color=#008000]#works[/color]
[/font][/color][color=#000000][font=Consolas][color=#0000ff]$[/color]sfirst = [color=#0000ff]$[/color]rec.sfirst[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]$[/color]slast = [color=#0000ff]$[/color]rec.slast[/font][/color]
[color=#000000][font=Consolas][color=#0000ff]$[/color]grade = [color=#0000ff]$[/color]rec.grade[/font][/color]
[color=#000000][font=Consolas]echo [color=#a31515]"[/color][color=#0000ff]$[/color][color=#a31515]sfirst [/color][color=#0000ff]$[/color][color=#a31515]slast Grade [/color][color=#0000ff]$[/color][color=#a31515]grade has extra commas"[/color][/font][/color]
[color=#000000][font=Consolas]}[/font][/color][color=#000000][font=Consolas]}[/font][/color]
[color=#000000][font=Consolas][color=#008000]#$ResultsArray |Select-Object -Property sid,slast,sfirst,grade,schoolid,dob,sphone,sstreet,scity,sstate,szip,homeroom,name,email| export-csv -NoTypeInformation $guardiansfile [/color][/font][/color]
[font=Consolas][color=#000000]([/color][color=#0000ff][color=#000000]$[/color][/color][color=#000000]ResultsArray) -replace ([/color][color=#a31515][color=#000000]','[/color][/color][color=#000000],[/color][color=#a31515][color=#000000]''[/color][/color][color=#000000]) [/color][color=#008000]#does not work[/color][/font]
[color=#000000][font=Consolas][color=#0000ff]$[/color]ResultsArray |Select-Object -Property sid,slast,sfirst,grade,schoolid,dob,sphone,sstreet,scity,sstate,szip,homeroom,name,email| export-csv -Delimiter [color=#a31515]"`t"[/color]   -NoTypeInformation [color=#0000ff]$[/color]guardiansfile [/font][/color]
[color=#000000][font=Consolas](gc [color=#0000ff]$[/color]guardiansfile) -replace([color=#a31515]'"'[/color],[color=#a31515]''[/color]) | Out-File [color=#0000ff]$[/color]guardiansfile -Force -Encoding ascii[/font][/color]

 

 

 

[stevec_]

$newArray = @{}

$ResultsArray.Keys | % {
   $newArray[$_] = $ResultsArray[$_] -Replace(",", "")
}

 

$newArray should contain the fixed data with no commas

Edited October 31, 2018 by stevec_

[ADMaster]

That looks promising, but I've tried a few variations and get this every time.

 

Index operation failed; the array index evaluated to null.

[ADMaster]

Instead of .keys I had to call the .psobject.Properties method.

 

I expanded out the foreach loop just for ease of reading, here is my final working code.

 

[color=#000000][font=Consolas][color=#af00db]foreach[/color] ([color=#0000ff]$[/color][color=#001080]rec[/color] [color=#af00db]in[/color] [color=#0000ff]$[/color][color=#001080]ResultsArray[/color]){
[color=#af00db]if[/color] ([color=#0000ff]$[/color][color=#001080]rec[/color] -like [color=#a31515]'*,*'[/color])
{
[color=#0000ff]$[/color][color=#001080]sfirst[/color] = [color=#0000ff]$[/color][color=#001080]rec[/color][color=#795e26].sfirst[/color]
[color=#0000ff]$[/color][color=#001080]slast[/color] = [color=#0000ff]$[/color][color=#001080]rec[/color][color=#795e26].slast[/color]
[color=#0000ff]$[/color][color=#001080]grade[/color] = [color=#0000ff]$[/color][color=#001080]rec[/color][color=#795e26].grade[/color]
[color=#008000]#Write-Output "$sfirst $slast Grade $grade has extra commas"[/color]
[color=#0000ff]$script[/color]:[color=#001080]body[/color] += [color=#a31515]"[/color][color=#0000ff]$[/color][color=#001080]sfirst[/color][color=#0000ff]$[/color][color=#001080]slast[/color][color=#a31515] Grade [/color][color=#0000ff]$[/color][color=#001080]grade[/color][color=#a31515] has extra commas[/color][color=#ff0000]`n[/color][color=#a31515]"[/color]
[color=#af00db]foreach[/color] ([color=#0000ff]$[/color][color=#001080]val[/color] [color=#af00db]in[/color] [color=#0000ff]$[/color][color=#001080]rec[/color][color=#795e26].psobject.Properties[/color]){
   [color=#af00db]if[/color]([color=#0000ff]$[/color][color=#001080]val[/color] -like [color=#a31515]"*,*"[/color]){
     [color=#0000ff]$[/color][color=#001080]val[/color][color=#795e26].value[/color] = [color=#0000ff]$[/color][color=#001080]val[/color][color=#795e26].value[/color] -replace ([color=#a31515]","[/color],[color=#a31515]""[/color])
   }}}}
[/font][/color]