Excel Update Column C with data in column B

Heres my dilemma, first things first i want to use powershell if possible to do this. I have a csv file that has 4 columns filled out
DisplayName, GUID, NewGuid, GPOLink

What i want is for anything in the GPOLink column that matches the GUID column to update that GUID with the NewGUID. So that everything in the GPOLink column gets updated with the matching NewGUID.

test.com.csv
LVL 1
ntr2defAsked:
Who is Participating?
 
RobSampsonCommented:
Hi, this was a curly one, but I think this will do what you need.

Regards,

Rob.

$InputFile = "C:\Temp\Test.csv"
$OutputFile = "C:\Temp\Test2.csv"
$Contents = Import-CSV $InputFile
$GUIDs = $Contents | Where {$_.GUID} | Select GUID,NewGUID

$Contents | ForEach {
    $DisplayName = $_.DisplayName
    $GUID = "{$($_.GUID)}" -replace "{{", "{" -replace "}}", "}"
    $NewGUID = "{$($_.NewGUID)}" -replace "{{", "{" -replace "}}", "}"
    If ($GUID -eq "{}") {$GUID = ''}
    If ($NewGUID -eq "{}") {$NewGUID = ''}
    $GPOLink = $_.GPOLink
    $GUIDS | ForEach {
        $GPOLink = ($GPOLink).Replace($_.GUID, $_.NewGUID)
    }
    $Prop = [Ordered]@{
        'DisplayName'="$DisplayName"
        'GUID'="$GUID"
        'NewGUID'="$NewGUID"
        'GPOLink'="$GPOLink"
    }
    New-Object PsObject -Property $Prop
} | Export-CSV $OutputFile -NoTypeInformation
Invoke-Item $OutputFile

Open in new window

0
 
footechCommented:
Here's the way I would approach it.  I'm assuming you only want to replace the GUID in the GPOLink field when there's a matching GUID in the GUID field of the same row, and not with a matching GUID in the GUID field from any row.  Maybe that assumption is incorrect.  The latter is what I read Rob's as doing.
$InFile = "test.com.csv"
$OutFile = "test.com2.csv"
Import-Csv $InFile  | ForEach `
{
    If (  $_.GUID -ne "" -and $_.GPOLink -match "$([regex]::Escape("$($_.GUID)"))" )
    {
        $_.GPOLink = $_.GPOLink -replace "$([regex]::Escape("$($_.GUID)"))","$($_.NewGUID)"
        $_
    }
    Else
    { $_ }
} | Export-Csv $OutFile -notype

Open in new window

0
 
ntr2defAuthor Commented:
so what i want is to take column D which is GPOLInk [LDAP://cn={F6E26033-3D54-4C26-9B76-DC0A96853232},cn=policies,cn=system,dc=testdomain,dc=com;0][LDAP://cn={6D03482A-2227-4664-AA90-FEBEBCE11E42},cn=policies,cn=system,dc=testdomain,dc=com;0][LDAP://cn={36C6CEB7-721C-4B24-869C-838547E93818},cn=policies,cn=system,dc=testdomain,dc=com;0] Notice there are 4 GUIDs in this cell and find the matching pair in columnB which is the GUID when it does i want it to be replaced by the NewGUId which is Columns for Example lets take the first GUID F6E26033-3D54-4C26-9B76-DC0A96853232 in Column B that GUID matches up with 61A54EE4-6F1A-4FCE-B7F2-91FEF25157D2 so i want it to get updated as such in column D [LDAP://cn=61A54EE4-6F1A-4FCE-B7F2-91FEF25157D2},cn=policies,cn=system,dc=testdomain,dc=com;0]

Rob when i ran what you provided the output I got is attached.
Output.txt
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
footechCommented:
From your explanation I would take it to mean that Columns B and C have a relation to each other, but otherwise have no relation to other columns.  In that case it would be more straight-forward to have those columns as a separate file.  As a rule, each row in a .CSV is an independent object, with each field describing an attribute of that object.  So to restate your goal, what you're trying to do is convert the GUIDs in the GPOLink field of test-input.csv, using test-conv.csv as a conversion table (referenced files attached).
$conversions = Import-Csv "test-conv.csv"
Import-Csv "test-input.csv" | ForEach `
{
    foreach ( $conv in $conversions )
    {
        $_.GPOLink = $_.GPOLink -replace "$([regex]::Escape("$($conv.GUID)"))","$($conv.NewGUID)"
    }
    $_
} | Export-Csv "test-output.csv" -notype

Open in new window


By the way, for Rob's, the [ordered] type is only available in PS 3.0 and above.  Remove that and you won't get an error.  Though you might want to change line 23 to
} | Select DisplayName,GUID,NewGUID,GPOLink | Export-CSV $OutputFile -NoTypeInformation

Open in new window

in order to maintain the same property order.
test-input.csv
test-conv.csv
0
 
ntr2defAuthor Commented:
Thank you for the clarification it did work but since you pointed it i will split the points.
0
 
RobSampsonCommented:
Thanks footech.  I think I tend to think in baby steps for Powershell at the moment, so I break it up a lot more and test output as I go, hence all of the re-assignments.  Your version is a nice and neat shorthand type.

Rob.
0
 
footechCommented:
Oh yes, I've been there!  Much better to make sure of your current values before plowing ahead with the rest of a script.  I'm pretty sure you've seen this in VBScript too.  Nothing like making a false assumption and then 50 lines down wondering why you aren't getting the right result.
0
 
RobSampsonCommented:
Yeah, I'm much more familiar with VBScript, so I can shorthand a lot of that where possible, although Powershell goes WAY further to that effect!  It's pretty cool stuff....starting to get used to it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.