locked
Comparing 3 Columns and Each Row Inside and Replacing String RRS feed

  • Question

  • This is an odd issue I've been presented with:

    I have to change the samaccountnames in column C that match with column A to the adjacent cell in column B. 

    I have to compare 3 columns and each row. 

    Column A = Samaccountname

    Column B = desired email

    Column C = Samaccount names seperated by "," but multiple people in the cell. 

    The logic I have worked out is as such:

    $a = $users.acc[$i]

    $b = $email.email[$j]

    $c = $members.member[$k]

    if($c[$k] -eq $b[$j] -or $a[$i])
    { $c.replace($b[$j]) }

    I don't know how to take the array of members and replace strings and then cycle through the rest checking and replacing properly. 

    Any ideas?

    Saturday, June 1, 2019 12:35 AM

All replies

  • Columns A,B,C of what?  What is it that you have that has columns.  Is this an array?  Arrays have no column names.  Is it a data table? 

    Start by reading the following and carefuly read and understand the instructions on how to ask a technical question.  After you understand then edit your original post so that it makes some sense and gives us information that can be used to help you find a solution.

    How to ask questions in a technical forum


    \_(ツ)_/

    Saturday, June 1, 2019 1:00 AM
  • Hi,

    Could you please provide your input file and the desired sample output. It will be helpful for us to understand the exact requirement.



    Komal Bachchani

    Saturday, June 1, 2019 4:40 AM
  • Ok, sorry for the obscurity. I can't give to many details. Let me swap some data and give an actual example.

    https://i.imgur.com/x0qN9pD.png


    I need to cycle throw many rows like this and take the cell in column a and find it in column c and then swap it with column b's value for that row. Does this help?

    **Update**

    The other thing I'm thinking is: It has to find the usernames in column c and then find it in column a then look to the adjacent cell in column b and then replace it in it's place in the cell in column c. Ugh, my mind can't think on how to find the solution. I'd rather send them a script to convert members in column c and query for the emails. I don't know if that's an option right now though. 
    • Edited by MSmith7295 Saturday, June 1, 2019 3:28 PM Update
    Saturday, June 1, 2019 3:09 PM
  • I think it'd be clearer of you didn't use "it" and "it's" in your description

    What you want to do isn't difficult, but you goal is still not clear. Given the examples below, which output do you want? Or, if none of them is you desired result, then provide an example.

    #### INPUT  ####
    A,B,C
    jwick,john.wick@company.com,"jwick,rmurtah,jsmith,tstark"
    
    # Output #1
    A,B,C
    john.wick@company.com,john.wick@company.com,"jwick,rmurtah,jsmith,tstark"
    
    # Output #2
    A,B,C
    jwick,john.wick@company.com,"john.wick@company.com,rmurtah,jsmith,tstark"
    
    # Output #3
    A,B,C
    jwick,john.wick@company.com,john.wick@company.com
    


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Saturday, June 1, 2019 6:23 PM
  • # Output #2
    A,B,C
    jwick,john.wick@company.com,"john.wick@company.com,rmurtah,jsmith,tstark"

    This is exactly what I need and then search for the other account names in C. 

    Saturday, June 1, 2019 6:33 PM
  • Why do you have to search for the "other" account names? There's only one value in the "A" column for each row no matter how many names are in the "C" column. Is there a possibility that the name in the "A" column occurs more than one in the "C" column in each row?

    Assuming, for each row, there's only one match in the "C" column for the name in the "A" column then it isn't that difficult to achieve your objective:

    Import the CSV, and for each row,
      If the "A" value appears in the "C" value
        replace the matching part of the "C" value with the "B" value


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Sunday, June 2, 2019 2:13 AM
  • It should be all right for your needs :-)

    Code

    $Source = '.\testcsv1.csv'
    $Destination = '.\testcsv2.csv'
    
    $csv = Import-Csv -Path $Source -Delimiter ','
    "samaccountname,mail,members" | out-file -FilePath $Destination -Encoding utf8
    
        foreach ($line in $csv) {
        $newColC = $line.members -replace $line.samaccountname,$line.mail
        $line.samaccountname+','+$line.mail+',"'+$newColC+'"'
        $line.samaccountname+','+$line.mail+',"'+$newColC+'"' | out-file -FilePath $Destination -Append -Encoding utf8
        }

    Source

    samaccountname,mail,members
    jwick,john.wick@company.com,"jwick,rmurtah,jsmith,tstark"
    jsmith,john.smith@company.com,"jwick,rmurtah,jsmith,tstark"

    Destination

    samaccountname,mail,members
    jwick,john.wick@company.com,"john.wick@company.com,rmurtah,jsmith,tstark"
    jsmith,john.smith@company.com,"jwick,rmurtah,john.smith@company.com,tstark"
    


    Sunday, June 2, 2019 9:56 PM
  • Using simple text replacement might work, but it's not right. You're not taking into account the fact that the names in the "members" column are delimited by commas (and that the 1st name in the list won't have a leading comma, and the last name in the list won't have a trailing comma). If you add the name "jwickY" to the first line in your example CSV you'll see there will be two substitutions and the 2nd one will have a bogus email address.

    This code will work correctly:

    $x = [ordered]@{
        OldCompanyUserName=''
        NewCompanyEmail=''
        Members=''
    }
    
    Import-Csv c:\temp\xxx.csv | 
        foreach {
            $arr = $_.Members -split ','
            For ($i=0; $i -lt $arr.length; $i++) {
                if ($arr[$i] -eq $_.OldCompanyUserName){
                    $arr.Item($i) = $_.NewCompanyEmail
                }
            }
            $x.OldCompanyUserName = $_.OldCompanyUsername
            $x.NewCompanyEmail = $_.NewCompanyEmail
            $x.Members = $arr -join ','
            [pscustomobject]$x
        } | Export-Csv c:\temp\xxx1.csv -NoTypeInformation

    If you wanted to use a regex you could first replace the commas in the string with spaces and then use "\b" (word break) as a delimiter:

    $newColC =$line.members -replace "\b$line.samaccountname\b",$line.mail

    . . . and then replace the spaces with commas, but that gets to a less understandable set of instructions.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Monday, June 3, 2019 2:36 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Friday, June 14, 2019 6:21 AM