Adding multiple text file data to a csv file with powershell into specific columns

Question

This my code so far:

# Master
Import-Csv -Path "C:\PS\SessionData\MasterList.csv" | Select-Object "Service Unit",@{ Name='UserID';Expression = { $PSItem.'UserID'.ToLower()} } | Export-Csv C:\PS\SessionData\MasterList2Col.csv -NoTypeInformation
# Get list of total numbers per Service Unit
Import-Csv -Path "C:\PS\SessionData\MasterList2Col.csv" | Select-Object -ExpandProperty "Service Unit" | Group-Object | Select-Object Name,Count | Out-File "C:\PS\SessionData\countUsersByBU.txt"

#Citrix
Import-Csv -Path "C:\PS\SessionData\Session Summary 09_04_2020.csv" | Select-Object -Property @{ Name="UserID";Expression={ $PSItem."Associated User" } } -Unique | Export-Csv -Path "C:\PS\SessionData\sessionCRX.csv" -NoTypeInformation
$citrixUsers = Import-Csv -Path "C:\PS\SessionData\sessionCRX.csv" | Select-Object -ExpandProperty UserID
Import-Csv -Path "C:\PS\SessionData\MasterList2Col.csv" | Where-Object {$citrixUsers -contains $PSItem.UserID} | ForEach-Object {"$($PsItem.'Service Unit')" } | Out-File "C:\PS\SessionData\totalCRX.txt"
$fileCRX = "C:\PS\SessionData\totalCRX.txt"
Get-Content $fileCRX | Group-Object | Select-Object Name,Count | Out-File C:\PS\SessionData\countCRX.txt

#VPN
Import-Csv -Path "C:\PS\SessionData\Cisco Anyconnect users 09-04.csv" | Where-Object { $PSItem.Status -eq 'Active' } | Select-Object @{Name = 'UserID'; Expression = {($_.Name -split '\\',2)[-1] }} -Unique | Export-Csv -Path "C:\PS\SessionData\sessionVPN.csv" -NoTypeInformation
$vpnUsers = Import-Csv -Path "C:\PS\SessionData\sessionVPN.csv" | Select-Object -ExpandProperty UserID
Import-Csv -Path "C:\PS\SessionData\MasterList2Col.csv" | Where-Object {$vpnUsers -contains $PSItem.UserID} | ForEach-Object {"$($PsItem.'Service Unit')" } | Out-File "C:\PS\SessionData\totalVPN.txt"
$fileVPN = "C:\PS\SessionData\totalVPN.txt"
Get-Content $fileVPN | Group-Object | Select-Object Name,Count | Out-File C:\PS\SessionData\countVPN.txt

#Create new csv file with totals
$txtMain = "C:\PS\SessionData\countUsersByBU.txt"
$txtCRX = "C:\PS\SessionData\countCRX.txt"
$txtVPN = "C:\PS\SessionData\countVPN.txt"

#End result new.csv 
#Get-Content ??? | Export-Csv -Path "C:\PS\SessionData\new.csv"

I have three text files, which has a "Name" column and a "Count" column, example below:

$txtMain = "C:\PS\SessionData\countUsersByBU.txt"

Name    Count
----    -----
HR       20
Account  25
Dev      25
Sales    25
Garden   10

$txtCRX = "C:\PS\SessionData\countCRX.txt"

Name    Count
----    -----
HR         3
Account    6
Dev        9
Garden     7

$txtVPN = "C:\PS\SessionData\countVPN.txt"

Name    Count
----    -----
HR         7
Account    8
Dev        1
Sales      5

I need to get this data into a new.csv file. Where the txtMain data has all Name columns and a total count column. The data from txtOne, needs to check the new.csv file for the Name column and put the Count value into a third column. The data from txtTwo, needs to check the new.csv file for the Name column and put the Count value into a fourth column. e.g. new.csv

Name   (txtMain)   (txtCRX)   (txtVPN)   (Percentage)
----   ---------   --------   --------   ------------
HR          20          3         7          50%
Account     25          7         8          60%         
Dev         25          9         1          40%
Sales       25                    5          20%
Garden      10          7                    70%

Then a final column which is a calculated expression, which calculates the percentage of txtCRX and txtVPN columns from the txtMain column.
Apologies for the noob question, haven't worked with Powershell for a while and not sure how to write the final Get and Export statement to create the new.csv file.

Answer 1

Using your example input files, You can do this:

# Create an object collection with totals counts from the helper files
$result = Import-Csv -Path "C:\PS\SessionData\countUsersByBU.txt" | Select-Object *,countCRX,countVPN
# add info from countCRX.txt
Import-Csv -Path "C:\PS\SessionData\countCRX.txt" | ForEach-Object {
    $name = $_.Name
    $item = $result | Where-Object { $_.Name -eq $name }
    if ($item) { $item.CountCRX = [int]$_.Count }
}

# add info from countVPN.txt
Import-Csv -Path "C:\PS\SessionData\countVPN.txt" | ForEach-Object {
    $name  = $_.Name
    $item = $result | Where-Object { $_.Name -eq $name }
    if ($item) { $item.CountVPN = [int]$_.Count }
}

# finally calculate the percentages
$result = $result | Select-Object *, @{Name = 'Percentage'; Expression = {'{0:P0}' -f (([int]$_.countCRX + [int]$_.countVPN) / [int]$_.Count)}}

# output on screen
$result | Format-Table -AutoSize

# output to CSV
$result | Export-Csv -Path 'C:\PS\SessionData\new.csv' -UseCulture -NoTypeInformation

Output on screen:

Name    Count countCRX countVPN Percentage
----    ----- -------- -------- ----------
HR      20           3        7 50%       
Account 25           6        8 56%       
Dev     25           9        1 40%       
Sales   25                    5 20%       
Garden  10           7          70%

P.S. you should create the 3 files countUsersByBU.txt, countCRX.txt and CountVPN.txt with Export-Csv -Path 'X:\thefile -NoTypeInformation instead of Out-File to get usable files to work with.
Normally you would give them the .csv extension, but.txt will also work (although technically incorrect..)