how to remove specific characters from variable in powershell

Question

how to remove specific characters from variable in powershell

Stephen Peterson 36

I have a CSV file and what to replace certain characters by using a hash table.

Here is the hash table
$hash = @{}
$hash."'" = ''
$hash."&" = ''
$hash."(" = ''
$hash.")" = ''
$hash." " = ''
$hash."/" = ''
$hash."\" = ''
$hash."Â" = ''
$hash."â€“ = ''

and here is the line that mostly works
Foreach ($key in $hash.Keys) {
$update = $update.Replace($key, $hash.$key)
}

The problem is with the last two entries in the hash table. Those symbols are not being found and are not being replaced. The other elements are working. (The A has a carrot over the top of it)

Any idea how I can detect and replace those elements and others like them?

I cant use the standard regex
$pattern = '[^a-zA-Z]'
$update = $update -replace $pattern, ''
because it takes out @ and ? and other characters I need.

Thanks for the help!
Steve

Accepted answer

5 additional answers

Your answer

Answer 1

Rich Matheisen 47,901

If I've interpreted your problem correctly, this should result in (mostly) legitimate SMTP addresses:

# Assuming no quoted strings are used in the addresses
# and no comments are used in the addresses
# and no "international" characters are used in the addresses
# domain isn't expressed as an IP address "[xxx.xxx.xxx.xxx]"
# THESE are what's valid in a SMTP address. Note that the range is INVERTED by the "^" at the beginning

$InvertedValidCharactersRange = "[^A-Za-z0-9!#$%&'*+-/=?^_``{|}~.(),:;<>@[\]-]+"

Import-Csv c:\Junk\BadIdea.csv -Encoding UTF8 |
    ForEach-Object{
        $_.Value -replace $InvertedValidCharactersRange, "" # remove everything that's NOT a valid character
}

NOTE: There is no attempt to actually validate the SMTP address. This is purely a character deletion bit of code!

Stephen Peterson 36 Reputation points

2021-11-14T22:28:29.813+00:00

Good ol regex...

Here is what I ended up with that appears to work:

$hash = [ordered]@{
# '"' = '' # There's a " character in the CN=cont.... line the "ERROR" column
"'" = ''
"&" = ''
"(" = ''
")" = ''
" " = '' # are you sure you want to replace spaces???? It will change the OU and CN names!!!
"/" = ''
"\" = ''
'–' = ''
' ΓÇô ' = ''
"[\u0080-\u00FF]" = "" # "Latin-1 Supplement" code block
"[\u0100-\u017F]" = "" # "Latin-Extended A Supplement" code block
"[\u0180-\u01BF]" = "" # "Latin-Extended B Supplement" code block
"[\u0370-\u03FF]" = "" # "Greek and Coptic" code block
"[\u0400-\u04FF]" = "" # "Cyrillic Supplement" code block
"[\u2500-\u257F]" = "" # "Box Drawing" code block
}

Everything was working except for ΓÇô which appears to be a special hyphen. Putting space before and after seemed to finally do the trick.

I like your regex string and have that in my back pocket too if we run into something else.

Thanks again for everything. I really appreciate all of your help. Thanks for teach me a few new things too.

Answer 2

MotoX80 36,291

I have a CSV file

Probably an encoding problem. Try this.

$update = Get-Content -Encoding UTF8 -Path "c:\temp\test1.csv" -raw    
$update  
  
$hash = @{}  
$hash."'" = ''  
$hash."&" = ''  
$hash."(" = ''  
$hash.")" = ''  
$hash." " = ''  
$hash."/" = ''  
$hash."\" = ''  
$hash."Â" = ''  
$hash."â€“ = ''  
  
Foreach ($key in $hash.Keys) {  
    $update = $update.Replace($key, $hash.$key)  
}  
""  
$update  
  
$update | Out-File "c:\temp\test2.csv"

Stephen Peterson 36 Reputation points

2021-11-12T18:06:43.663+00:00

Thanks for the suggestion. Unfortunately that didnt work.

If I go to a command prompt and just type out the file, I see "┬áAsset"
If I open the file in notepad ++ its a blank
If I open the file in Excel, it looks like a question mark.

another example is at a command prompt, I type it out and see ΓÇô 17 ΓÇô
both notepadd ++ and Excel show it as – 17 –

Strange....

any other thoughts are greatly appreciated.

Thanks,
Steve
MotoX80 36,291 Reputation points

2021-11-12T18:21:39.393+00:00
Try setting the output encoding. Either ascii or UTF-8.

$update | Out-File -Encoding ascii "c:\temp\test2.csv"

Here are the files that I was testing with.
Stephen Peterson 36 Reputation points

2021-11-14T22:32:15.73+00:00

wanted to say thanks again for responding and all the suggestions.
There are a couple of solutions, one that I posted with yours and Rich's assistance and one that Rich posted using Regex.
The encoding during input and output is definitely something Ill remember.
The code blocks that Rich posted where great too.

Thank you again!

Answer 3

Stephen Peterson 36

After I run it through the script, the output file typed at the command prompt show some of these as a question mark.
I may be able to just run it through the script again to see if I can eliminate the "?"

Ill let you know how that goes

Steve

Answer 4

Rich Matheisen 47,901

Your problem seems to be with the interpretation of the characters. That plus the fact that the characters "(", ")", and "\" all need to be escaped (they're regular expression meta characters).

$hash = [ordered]@{
    "'"  = ''
    "&"  = ''
    "\(" = ''
    "\)" = ''
    " "  = ''
    "/"  = ''
    "\\" = ''
    "Â"  = ''   # ALT + 0194
    "Æ"  = ''  # ALT + 0196
}

"1'3","1&3","1(3","1)3","1 3","1/3","1\3","1Â3","1Æ3" |
    ForEach-Object{
        $update = $_
        Foreach ($key in $hash.Keys) {
            $update = $update -Replace $key, "$($hash.$key)"
        }
        "`$update = $update"
    }

That bit of code produced this output:

$update = 13
$update = 13
$update = 13
$update = 13
$update = 13
$update = 13
$update = 13
$update = 13
$update = 13

The character "Æ" (which I'm assuming is the last key in the your hash) appears in your example as $hash."â€“. On my machine the value 0xC6 is showing as "Ã†". If that's incorrect, can you tell me the hex value (or the decimal value, or the Unicode codepoint) for the character?

Stephen Peterson 36 Reputation points

2021-11-12T21:12:03.327+00:00

Rich,
Thanks for the code. I learned a new way of doing the hash. Thanks.

I agree it does appear to be interpretation of the characters.

How do I find the hex, decimal, or unicode value?
I copied and pasted from a cmd prompt and pasted into my hash table, Im still not detecting the correct characters.

I liked MotoX suggestion of encoding the input and export files but that hasnt helped yet.

this is what the original csv file looks like
DISTINGUISHEDNAME,OBJECTCLASS,ATTRIBUTE,ERROR,VALUE,UPDATE
"CN=AssetInfo,OU=Exchange Users,OU=Generic Accounts,DC=contoso,DC=com",user,userPrincipalName,Character,┬áAssetInfo@Company portal .com,
"CN=cont IMG ΓÇô 17 ΓÇô ADM CONF ROOM,OU=Staff,DC=contoso,DC=com",user,userPrincipalName,"Character, cont IMG ΓÇô 17 ΓÇô ADM CONF ROOM@Company portal .com,

Then there are an additional 44000 lines in the csv some of which also have characters that need to be removed.

Id like the first update value to be converted to just Assetinfo@Company portal .com
The second value should end up converted to IMG17ADMCONFROOM@Company portal .com
etc.

Thanks to you and to MotoX80
Rich Matheisen 47,901 Reputation points

2021-11-12T22:13:59.587+00:00
Run this and, assuming you're using the same culture as the the system that created those strings, post the result. If you're using different cultures post yours and the on in which the DC is located. They'll look like this:

> PS C:\Users\richm> get-culture > > > LCID Name DisplayName > ---- ---- ----------- > 1033 en-US English (United States)

Also, from your machine and the DC, get the system default encoding and pay attention to the WindowsCodePage property:

[System.Text.Encoding]::Default
Stephen Peterson 36 Reputation points

2021-11-12T22:17:18.267+00:00

working on this for a colleague. He has access to the systems.

Ill ask him to run the code you sent and will post it asap.

Thank you
Rich Matheisen 47,901 Reputation points

2021-11-12T23:50:27.033+00:00

Looking at just the string "ΓÇô" :

The character "Γ" is the Greek letter Capital Gamma. It's Unicode code-point is 0x0393. It's in the "Greek and Coptic" code block. That block includes the range \u0370-\u03FF (0x370 - 0x3FF).
The character "Ç" is the "Latin Capital Letter C with Cedilla". It's Unicode code-point is 0x00C7. It's in the "Latin-1 Supplement" code block. That block includes the range \u0080-\u00FF (ox80 - 0xFF).
The character "ô" is the "Latin small letter O with circumflex". It's Unicode code-point is 0x00F7. It's in the "Latin-1 Supplement" code block. That block includes the range \u0080-\u00FF (ox80 - 0xFF).

And, in the string "┬á":

The character "┬" is the Box Drawings Light Down And Horizontal. It's Unicode code-point is 0x252C. It's in the "Box Drawing" code block. That block includes the range \u2500-\u257F (0x2500 - 0x257F).
The character "á" is the "Latin Small Letter A With Acute". It's Unicode code-point is 0x00C7. It's in the "Latin-1 Supplement" code block. That block includes the range \u0080-\u00FF (ox80 - 0xFF).

That's a curious mix of characters!
Stephen Peterson 36 Reputation points

2021-11-13T12:15:17.873+00:00

Thanks for finding all of those.
Im not sure what is generating the data or why there are so many random characters in the file.
If it wasnt for those strange characters, this would have been much easier.
Good thing is its another learning experience.

Your one reply suggested escaping some of the characters. The script was finding those without the escape. Im going to try escaping them anyhow and switch my hash table to the method you showed.

Ill keep you posted.

Thanks for all the suggestions and time.

Steve

Answer 5

Here is the code Im using

$idfixfile = Import-Csv -encoding UTF8 $home\Documents\orignal.csv
$outfile = "$home\documents\fixed_file.csv"
$output = @()
$lastoutput = @()

                                $hash = @{}
                                $hash."'" = ''
                                $hash."&" = ''
                                $hash."(" = ''
                                $hash.")" = ''
                                $hash." " = ''
                                $hash."/" = ''
                                $hash."\" = ''
                                $hash."Â" = ''
                                $hash."â€“ = ''
                                $hash."┬á" = ''
                                $hash."ΓÇô" = ''
                                $hash."-" = ''

Foreach ($entry in $idfixfile){
                                $distinguishedname=$entry.distinguishedname
                                $objectclass = $entry.objectclass
                                $attribute = $entry.attribute
                                $newerror = $entry.error
                                [string]$oldvalue = $entry.value 
                                $update = $oldvalue   

                                Foreach ($key in $hash.Keys) {                                                              
                                                              $update = $update.Replace($key, $hash.$key)
                                                             }

                                $obj = new-object psobject
                                $obj | Add-Member NoteProperty 'distinguishedname' $distinguishedname
                                $obj | Add-Member NoteProperty 'objectclass' $objectclass
                                $obj | Add-Member NoteProperty 'attribute' $attribute
                                $obj | Add-Member NoteProperty 'error' $newerror
                                $obj | Add-Member NoteProperty 'value' $oldvalue
                                $obj | Add-Member NoteProperty 'update' $update    
                                $output += $obj                                                                                                              
                             }

                             $output | export-csv -Encoding ASCII $outfile -nti

MotoX80 36,291 Reputation points

2021-11-12T23:45:48.987+00:00

Change the last line to use UTF-8 encoding.

$output | export-csv -Encoding UTF8 $outfile -nti

Your sample data has an extra quote.

I fixed it to this.

With the UTF8 update, it looks ok to me in Libre Office.
Stephen Peterson 36 Reputation points

2021-11-14T12:18:54.187+00:00

I thought Id tried the $output | export-csv -Encoding UTF8 $outfile -nti before the ascii attempt.
I tried it again. When I open the file in Excel, it looks like a blank space. I didnt look at it from just a cmd prompt. Should have.
The ASCII makes it look like a ? in Excel.

Your example is what im looking for. I think we are close.

I appreciate all your responses and suggestions.
Once I get it working, Ill make sure I update you on any solution.

Also, I have some X500 addresses that I need to account for. The format of those contains valid back slashes and other formatting but, I have a few with invalid characters too.

This has been a fun challenge.

Thanks again.

Share via

how to remove specific characters from variable in powershell

5 additional answers

Your answer