Check if a value exists in one csv file and add static text to another csv field with Powershell

Question

I need to check if the column value from the first csv file, exists in any of the three other csv files and return a value into a new csv file, in order of precedence.
So if the username field from allStaff.csv exists in the list of usernames in the sessionVPNct.csv file, put the static text into the final csv file as 'VPN'. If it does not exist, check the next csv file: sessionCRXct.csv then put the static text 'CRX', if not check the last csv file: sessionTMSct.csv then put the static text: TM if not the put the static text 'none' into the final csv file. I have four csv files as below:

1. allStaff.csv
2. VPN.csv
3. CRX.csv
4. TMS.csv

I have imported the csv files into variables as below:

$allUsers = Import-Csv -Path "C:\allStaff.csv"
$vpn = Import-Csv -Path "C:\VPN.csv" | Select-Object -ExpandProperty UserName
$crx = Import-Csv -Path "C:\CRX.csv" | Select-Object -ExpandProperty UserName
$tms = Import-Csv -Path "C:\TMS.csv" | Select-Object -ExpandProperty UserName

The $allUsers variable displays the following:

Firstname     LastName   Username    Position          Area
---------     --------   --------    --------          ----
Joe           Bloggs     jbloggs     Gardener          Maintenance
Jim           Smith      jsmith      Accountant        Finance
Bob           Seger      bseger      HR Advisor        Human Resources
Adam          Boson      aboson      Customer Support  IT
Adele         bree       abree       Payroll           Finance

The $vpn variable displays the following:

Username
--------
jbloggs
jsmith

The $crx variable displays the following:

Username
--------
jbloggs
jsmith
bseger

The $tms variable displays the following:

Username
--------
jbloggs
jsmith
bseger
aboson

Then I have the following line to start the result csv file

$result = $allUsers | Select-Object *,ConnectionMethod

Not quite sure how to do the final query, which I believe should be an if else loop to go through all rows in the $result variable, and check the other csv if the username field exists, then return the static text.

$result | Export-Csv -Path "C:\allStaffConnections.csv"

This is how I need the final allStaffConnections.csv file to be displayed.

Firstname     LastName   Username    Position          Area              ConnectionMethod
---------     --------   --------    --------          ----              --------------
Joe           Bloggs     jbloggs     Gardener          Maintenance       VPN
Jim           Smith      jsmith      Accountant        Finance           VPN
Bob           Seger      bseger      HR Advisor        Human Resources   CRX
Adam          Boson      aboson      Customer Support  IT                TMS
Adele         bree       abree       Payroll           Finance           none

Am I on the right track with the below code?

$allUsers = Import-Csv -Path "C:\allStaff.csv"
$vpn = Import-Csv -Path "C:\VPN.csv" | Select-Object -ExpandProperty UserName
$crx = Import-Csv -Path "C:\CRX.csv" | Select-Object -ExpandProperty UserName
$tms = Import-Csv -Path "C:\TMS.csv" | Select-Object -ExpandProperty UserName
$vpnText = 'VPN'
$crxText = 'CRX'
$txsText = 'TMS'
$noneText = 'none'

$allUsersExtended = $allUsers | Select-Object *,ConnectionMethod
$results = $allUsersExtended.ForEach(
    {
        if($vpn -Contains $PSItem.UserName) {
            # add $vpnText to ConnectionMethod column for that row in the $result
            $PSItem.ConnectionMethod = $vpnText
        }elseif($crx -Contains $PSItem.UserName) {
            # add $crxText to ConnectionMethod column for that row in the $result
            $PSItem.ConnectionMethod = $crxText
        }elseif($tms -Contains $PSItem.UserName) {
            # add $txsText to ConnectionMethod column for that row in the $result
            $PSItem.ConnectionMethod = $tmsText
        }else {
            # add $noneText to ConnectionMethod column for that row in the $result
            $PSItem.ConnectionMethod = $noteText
        }
    })
$results | Export-Csv -Path "C:\allStaffConnections.csv" -NoTypeInformation

This gives me an empty allStaffConnections.csv file.
I have run the code line by line and can get as far as:

$allUsersExtended = $allUsers | Select-Object *,ConnectionMethod

Which gives me the extra column "ConnectionMethod", but after running the loop, it gives me an empty allStaffConnections.csv file.

iterate thru the $AllUsers list, find any matches in the others based on the shared value, build a new [PSCusomtObject] with the combined results, and finally write that out to a new CSV file. — Lee_Dailey
– Lee_Dailey, Commented Apr 19, 2020 at 5:33
@Lee_Dailey - so I would use the MATCH on each of the other csv files in order e.g VPN, CRX, TM or none? Do you have some example code? I have been struggling on this one for hours. I posted the code I have started with, bit not sure if I'm on the right track or not. Assuming I would need nested loops within what I have already started on — brokencrow
– brokencrow, Commented Apr 19, 2020 at 5:39
don't use Select-Object on it since the way you defined that select throws out the other properties. keep them all. then, in the loop, use $Vpn.Where({$_.UserName -eq $CurrentUserName}) or $Vpn - $CurrentUserName. i think the 2nd will work, but i am very confident that the 1st will work. [grin] — Lee_Dailey
– Lee_Dailey, Commented Apr 19, 2020 at 5:56
@Lee_Dailey - how are you referencing the $CurrentUserName variable to the allStaff.csv file? — brokencrow
– brokencrow, Commented Apr 19, 2020 at 6:13
your new code otta work IF you change -Contain to -Contains. however, you have JBloggs in 3 different connection type lists ... but your final report shows that there is only one connection type per account. how do you handle multiple matches? — Lee_Dailey
– Lee_Dailey, Commented Apr 19, 2020 at 7:38

Lee_Dailey · Accepted Answer · 2020-04-19 07:57:09Z

here is one way to do the job. [grin] it presumes that you only want to 1st connection type found. if you want all of them [for instance, JBloggs has all 3 types listed], you will need to concatenate them.

what it does ...

fakes reading in the CSV files
when ready to use real data, comment out or remove the entire #region/#endregion section and use Get-Content.
iterates thru the main collection
uses a switch to test for membership in each connection type list
this breaks out of the switch when it finds a match since it presumes you only want the 1st match. if you want all of them, then you will need to accumulate them instead of breaking out of the switch block.
sets the $ConnectionType as appropriate
builds a PSCO with all the wanted props
this likely could be shortened by using Select-Object, a wildcard property selector, and a calculated property.
sends it out to the $Results collection
shows it on screen
saves it to a CSV file

the code ...

#region >>> fake reading in CSV files
#    in real life, use Import-CSV
$AllUsers = @'
FirstName, LastName, UserName, Position, Area
Joe, Bloggs, jbloggs, Gardener, Maintenance
Jim, Smith, jsmith, Accountant, Finance
Bob, Seger, bseger, HR Advisor, Human Resources
Adam, Boson, aboson, Customer Support, IT
Adele, bree, abree, Payroll, Finance
'@ | ConvertFrom-Csv

$Vpn = @'
UserName
jbloggs
jsmith
'@ | ConvertFrom-Csv

$Crx = @'
UserName
jbloggs
jsmith
bseger
'@ | ConvertFrom-Csv

$Tms = @'
UserName
jbloggs
jsmith
bseger
aboson
'@ | ConvertFrom-Csv
#endregion >>> fake reading in CSV files

$Results = foreach ($AU_Item in $AllUsers)
    {
    # this presumes you want only the 1st connection type found
    #     if you want all of them, then you will need to concatenate them
    switch ($AU_Item.UserName)
        {
        {$_ -in $Vpn.UserName}
            {
            $ConnectionType = 'VPN'
            break
            }
        {$_ -in $Crx.UserName}
            {
            $ConnectionType = 'CRX'
            break
            }
        {$_ -in $Tms.UserName}
            {
            $ConnectionType = 'TMS'
            break
            }
        default
            {
            $ConnectionType = 'None'
            }
        }

    [PSCustomObject]@{
        FirstName = $AU_Item.FirstName
        LastName = $AU_Item.LastName
        UserName = $AU_Item.UserName
        Position = $AU_Item.Position
        Area = $AU_Item.Area
        ConnectionTYpe = $ConnectionType
        }
    }

# on screen
$Results

# send to CSV
$Results |
    Export-Csv -LiteralPath "$env:TEMP\brokencrow_-_UserConnectionType.csv" -NoTypeInformation

truncated on screen output ...

FirstName      : Joe
LastName       : Bloggs
UserName       : jbloggs
Position       : Gardener
Area           : Maintenance
ConnectionTYpe : VPN

[*...snip...*] 

FirstName      : Adele
LastName       : bree
UserName       : abree
Position       : Payroll
Area           : Finance
ConnectionTYpe : None

the CSV file content from brokencrow_-_UserConnectionType.csv ...

"FirstName","LastName","UserName","Position","Area","ConnectionTYpe"
"Joe","Bloggs","jbloggs","Gardener","Maintenance","VPN"
"Jim","Smith","jsmith","Accountant","Finance","VPN"
"Bob","Seger","bseger","HR Advisor","Human Resources","CRX"
"Adam","Boson","aboson","Customer Support","IT","TMS"
"Adele","bree","abree","Payroll","Finance","None"

Collectives™ on Stack Overflow

Check if a value exists in one csv file and add static text to another csv field with Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related