locked
Powershell Need help in replacing Strings between 2 special Characters RRS feed

  • Question

  • Hello All,
    I'm kinda stuck solving this puzzle where in need to remove any spaces between 2 characters.
    Below is the sample data that I have in .txt file. 

    
    
    EUROPE\asd <EUROPE\asd@QWC> (FirstName LastName) accessed 2016/11/23 00:17:39
    EUROPE\qwe <EUROPE\qwe@ASD> (FirstName LastName) accessed 2016/11/23 00:56:25
    EUROPE\zxc <EUROPE\zxc@zvb> (FirstName LastName) accessed 2016/11/22 23:55:05
    EUROPE\abc <EUROPE\abc@ABC> (FirstName LastName) accessed 2016/11/22 00:51:25

    So in the above 4 rows of data, I have 5 columns separated by single space in terms of SQL Table. In the 3rd column, I need to remove spaces which are between "(FirstName LastName)"

    Desired Output:
    EUROPE\asd <EUROPE\asd@QWC> (FirstNameLastName) accessed 2016/11/23 00:17:39
    EUROPE\qwe <EUROPE\qwe@ASD> (FirstNameLastName) accessed 2016/11/23 00:56:25
    EUROPE\zxc <EUROPE\zxc@zvb> (FirstNameLastName) accessed 2016/11/22 23:55:05
    EUROPE\abc <EUROPE\abc@ABC> (FirstNameLastName) accessed 2016/11/22 00:51:25

    Friday, January 25, 2019 1:38 AM

Answers

  • OK, I found the .NET Match function, and got this to work:

    $File = ".\InputFile.txt"
    $Lines = Get-Content -Path $File
    ForEach ($Line In $Lines)
    {
        # Break up the line into 3 strings.
        $Index1 = [regex]::match($Line,"> ").Index
        $Index2 = [regex]::match($Line," accessed").Index
        $String1 = $Line.SubString(0, $Index1 + 2)
        $String2 = $Line.SubString($Index1 + 2, $Index2 - $Index1 - 2)
        $String3 = $Line.SubString($Index2)
        # Remove spaces between "> " and " accessed".
        $String4 = $String2.Replace(" ", "")
        # Put the line back together.
        $Final = "$String1$String4$String3"
        # Output.
        $Final
    }
    


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, January 25, 2019 9:15 PM

All replies

  • I assume that FirstName and LastName are not the actual values, that each line will have different names.

    Your text file may have 5 columns, but each line has 6 spaces. If we use the PowerShell Split function on each line to split on the space character, we get an array of 7 values. The array will index from 0 to 6. A simple solution would be to concatenate the 7 values, but skip the one space. For example:

    $File = ".\InputFile.txt"
    $Lines = Get-Content -Path $File
    ForEach ($Line In $Lines)
    {
        $Fields = $Line.Split(" ")
        $Fields[0] + " " + $Fields[1] + " " + $Fields[2] + $Fields[3] + " " + $Fields[4] + " " + $Fields[5] + " " + $Fields[6]
    }
    

    This script outputs the new lines (with the one space removed from each), and the output can be redirected to a new text file. There are other possible solutions, using regular expressions, where you would remove all spaces between the "(" character and the ")".


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, January 25, 2019 3:06 AM
  • The key to his is how you manage string with single or double quotes

    $a = "aaa"
    $b = "bbb"
    $c = $a + $b
    $c

    $a = 'aaa'
    $b = 'bbb'
    $d = "$A$b"
    $d


    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website http://www.WhartonComputer.com <br/> Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Friday, January 25, 2019 7:11 PM
  • Thanks for response. Can you possibly tell me how to select and replace spaces between the characters starting from  "> (" and "accessed"

    EUROPE\abc <EUROPE\abc@ABC> (FirstName LastName) accessed 2016/11/22 00:51:25



    Friday, January 25, 2019 7:39 PM
  • I don't think you want to remove all spaces between ">" and "accessed", since that will remove two extra spaces, resulting in

    EUROPE\abc <EUROPE\abc@ABC>(FirstNameLastName)accessed 2016/11/22 00:51:25

    I believe you want to find all spaces between the strings "> (" and ") a", or perhaps between "(" and ")". I don't know how to do that in PowerShell. Hence the solution I suggested earlier. I tested it and it worked for me.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, January 25, 2019 8:22 PM
  • Thanks Richard. I cannot search between characters "( )" as sometimes, I'm getting them between the firstName and LastName. the only thing consistent is "> (" and "accessed". If I can remove all spaces between those 2 characters, that will solve my problem and then i run Bulk insert

    I'll continue to search online to see if I can select data between specific characters.




    Friday, January 25, 2019 8:31 PM
  • OK, I found the .NET Match function, and got this to work:

    $File = ".\InputFile.txt"
    $Lines = Get-Content -Path $File
    ForEach ($Line In $Lines)
    {
        # Break up the line into 3 strings.
        $Index1 = [regex]::match($Line,"> ").Index
        $Index2 = [regex]::match($Line," accessed").Index
        $String1 = $Line.SubString(0, $Index1 + 2)
        $String2 = $Line.SubString($Index1 + 2, $Index2 - $Index1 - 2)
        $String3 = $Line.SubString($Index2)
        # Remove spaces between "> " and " accessed".
        $String4 = $String2.Replace(" ", "")
        # Put the line back together.
        $Final = "$String1$String4$String3"
        # Output.
        $Final
    }
    


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, January 25, 2019 9:15 PM