1

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:

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
Santiago Squarzon
  • 41,465
  • 5
  • 14
  • 37
NadimAJ
  • 137
  • 1
  • 13
  • Can you include a sample of your input data and the expected output? – zett42 Feb 07 '22 at 10:44
  • add example of the structure of your csv files and not in a image – Avshalom Feb 07 '22 at 10:54
  • [try avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it is exponentially expensive. – iRon Feb 08 '22 at 06:43

1 Answers1

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
Santiago Squarzon
  • 41,465
  • 5
  • 14
  • 37