PowerShell merging data from multiple CSVs

Question

I have a bunch of CSV files containing information about DHCP scopes (100+ files, one for each server). I wish to merge the data from all into a new table. There are ways to merge the CSVs but that's not good enough, because one the most important piece of information is not included inside the individual CSV files...that of the server name. The server name is coming from the filename itself. Hence the approach to build a new table.

My issue is that the properties for SubnetMask, StartRange, EndRange, ScopeID and State are being output in the same "cell". I managed to do it for the ScopeName (i.e. managed to get one ScopeName per line), but not sure how to approach the rest.

$outputTable = $null
$files = Get-ChildItem -Path "C:\Temp\Discovery\" -Filter *Scopes.csv
Write-Host $files
ForEach($file in $files)
{
    $scopeFile = Import-Csv $file

    ForEach ($scopeName in $scopeFile.Name)
    {
        $outputTable += @(
                    [pscustomobject]@{
                    ` DHCPServer=$file.Name.ToString().Split(".")[0];
                    ` ScopeName=$scopeName;
                    ` SubnetMask=$scopeFile.SubnetMask;
                    ` StartRange=$scopeFile.StartRange;
                    ` EndRange=$scopeFile.EndRange;
                    ` ScopeID=$scopeFile.ScopeID
                    ` State=$scopeFile.State
                    }
                )
    }
}

$outputTable | ft

Current output:

Expected output:

DHCPServerName,ScopeName,SubnetMask,StartRange,EndRange,ScopeID,State
server1,london,255.255.0.0,192.168.0.1,192.168.0.254,192.168.0.1,Active
server1,london,255.255.0.0,192.168.1.1,192.168.0.254,192.168.1.1,Active
server1,london,255.255.0.0,192.168.1.1,192.168.0.254,192.168.1.1,NotActive

Can you include a sample of your input data and the expected output? — zett42
– zett42, Commented Feb 7, 2022 at 10:44
add example of the structure of your csv files and not in a image — Avshalom
– Avshalom, Commented Feb 7, 2022 at 10:54
try avoid using the increase assignment operator (+=) to create a collection as it is exponentially expensive. — iRon
– iRon, Commented Feb 8, 2022 at 6:43

Santiago Squarzon · Accepted Answer · 2022-02-07 14:25:18Z

1

You can use a calculated property with Select-Object to add the file's Name as a new column of your Csv:

$merged = foreach($file in Get-ChildItem -Path C:\Temp\Discovery\ -Filter *Scopes.csv)
{
    Import-Csv $file.FullName | Select-Object @{
        Name = 'DHCPServerName'
        Expression = { $file.Name.Split(".")[0] }
    }, *
}

$merged | Format-Table

edited Feb 7, 2022 at 14:25

answered Feb 7, 2022 at 13:32

Santiago Squarzon

65.2k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NadimAJ Over a year ago

This is truly the work of the devil! Thanks :)

Santiago Squarzon Over a year ago

@NadimAJ 😈 my pleasure, happy to help :)!

Collectives™ on Stack Overflow

PowerShell merging data from multiple CSVs

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related