PowerShell Tip of the Week: Combine multiple CSV files

Recently I had to combine multiple files and count unique users from them. Script is pretty simple but it might me useful in some troubleshooting sessions. Below you can find two examples about how can this be done.

Combine CSV files

I created folder called Files on my desktop and pasted there user lists in CSV format. Files contains multiple columns but I was focusing only on Samaccountname:

CSV files
CSV files

File example – Samaccountname column:

file example
file example

To select all of the CSV files from source location I used Get-ChildItem command and limited to *.csv. Next I looped each file and select group unique users:

$CSVPath = 'C:\Users\$env:username\Desktop\files'

Get-ChildItem $log_path -Recurse -Include *.csv | 
where-object {$_.BaseName -match "User list"} | 
ForEach-Object { Import-CSV $_.FullName } | 
ForEach-Object {$_.'Samaccountname'} | Group-Object | 
Select-Object Name  -Unique | Sort-Object Name | 
Export-Csv -Path "C:\Users\$env:username\Desktop\Files\Results.csv" -NoTypeInformation -Force

#Import saved CSV
$Results = Import-CSV "C:\Users\$env:username\Desktop\Files\Results.csv"

#Numbers
($Results.name).count
($Results | select * -Unique).count

Below you can find another way to do that using PSCustomObject and add it to Array:

$CSVPath = "C:\Users\$env:username\Desktop\files"
$AllCSVs = Get-ChildItem $CSVPath -Recurse -Include *.csv | Where-Object {$_.BaseName -match "User list"} 
$Array = @()
 
#Looping each file
Foreach($File in $AllCSVs)
{
    $CSV = Import-Csv $file.FullName
    Foreach($item in $CSV)
    {
        $Object = [pscustomobject][ordered] @{
                 
            "User" = $item.SamAccountName
             
        }
        $Array += $Object
    }
}
($Array | Select-Object * -Unique).count

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.