Compare two strings in powershell, regex * match, output DataSpace_ID field from string1 based on match, into switches listing string in own column

Question

I have a list of switches in CSV and a list of data spaces where these switches are. In my list of Data Spaces, I have a DataSpace_ID field which represents its associated DataSpace_Name.

My list of switches has a Host_Name and IP_Address fields. What I want is using PowerShell and regex matching using Wildcards, I want to match the DataSpace field example, "ABC-COM" to the switch listing Host_Name which would be ABC-COM-3750-SW1. I only want to match up to ABC-COM...

Then for my result I want the output, based on the matches found, to associate the DataSpace_ID value found and include it in the output of the switch listing.

Let's say I match ABC-COM = DATASPACE_ID 1 and DEF-COM = DataSpace_ID 2, and my switch data is:

Host_Name        IP_Address
ABC-COM-3750-SW1 IP 192.168.1.2
ABC-COM-3750-SW2 IP: 192.168.1.3 
DEF-COM-3750-SW1 IP: 192.168.3.5
DEF-COM-3750-SW2 IP: 192.168.3.6

So, in the end you would have this output from the switch listing based on comparison of the dataspace listing, except it would add the DataSpace_ID Column from the other comparison listing of data space names... Switch listing Output would look like this:

DataSpace_ID     Host_Name        IP_Address
1                ABC-COM-3750-SW1 IP 192.168.1.2
1                ABC-COM-3750-SW2 IP: 192.168.1.3 
2                DEF-COM-3750-SW1 IP: 192.168.3.5
2                DEF-COM-3750-SW2 IP: 192.168.3.6

Here is my latest code revised based on some of your input, I am not getting errors any longer, however my output is not returning any results either.


    clear-host
    $hash.clear()
    $dataSpacesExport = Import-Csv -Path .\DataSpaces_Export.csv -Header 'DataSpace_ID', 'DataSpace_Name' -Delimiter ","
    $accessSwitchesForExport = Import-Csv -Path .\AccessSwitchesForExport.csv -Header 'Host_Name', 'IP_Address' -Delimiter ","

    # create hashtable
    $hash = @{}
    # Create Regex criteria
    $re = [regex]".+(?=-\d+)"

    $dataSpacesExport | ConvertFrom-Csv | % { $hash.Add($_,”$_”) }

    # output
    $accessSwitchesForExport | ConvertFrom-Csv |
    Select-Object @{ n = "DataSpace_ID"; e = { $hash[$re.Match($_.Host_Name).Value] } },* |
    Where-Object { $_.DataSpace_ID -ne $null }

My CSV files as some have asked for, example data would be: DataSpaces and switches output examples are below in the post. DataSpaces contain a DataSpace_ID and DataSpace_Name, and switches csv contain a Host_Name and IP_Address fields.

Output, like below, based on comparison of two csv's should show: Matching DataSpace_ID with matching Host_Name, and its associated IP Address in final table.

Why are you trying to split your data at newlines after the import? Import-Csv should already have done that for you (and transformed each row into a custom object). And why do you specify the parameter -Header? Your input files seem to have a header row. Imort-Csv will automatically determine column names from the first row in the file unless you tell it otherwise. Also, comma is the default delimiter, so -Delimiter ',' is not needed. — Ansgar Wiechers
Hi Ansgar Wiechers, This code was me playing with different options to see what works. So, I agree with your statements here, the extra code is not needed. — haimster2797
Example...Compare Network Switch names in one CSV(ABC-COM-3750-AS1) to a Network Closet name similar in naming convention (ABC-COM) from a separate CSV. Network closet name has an ID field associated with each closet. I need to compare switch name with network closet, and then grab the associated ID for that network closet and insert it into my switch listing as a new field called ID... Do this for each match, using Regex method to match. [a-z0-9] etc. Hope this helps. — haimster2797
To wrap up on what I mentioned, the reason I need this is because there may be network switches in my list NOT associated to a Network Closet in my database. That Network closet CSV is an export from my database. Any switches that don't have an associated network closet, I don't want the data, only matches. — haimster2797

rokumaru rokumaru · Accepted Answer · 2019-07-23T01:50:58

This is a solution using a hash table.

$dataSpacesExport = @"
DataSpace_ID,DataSpace_Name
1,ABC-COM
2,DEF-COM
"@ 

$accessSwitchesForExport = @"
Host_Name,IP_Address
ABC-COM-3750-SW1,IP: 192.168.1.2
ABC-COM-3750-SW2,IP: 192.168.1.3 
DEF-COM-3750-SW1,IP: 192.168.3.5
DEF-COM-3750-SW2,IP: 192.168.3.6
GHI-COM-3750-SW2,IP: 192.168.3.6
"@

$re = [regex]".+(?=-\d+)"

# create hashtable
$id = @{}
$dataSpacesExport | ConvertFrom-Csv | ForEach-Object { $id[$_.DataSpace_Name] = $_.DataSpace_ID }

# output
$accessSwitchesForExport | ConvertFrom-Csv |
Select-Object @{ n = "DataSpace_ID"; e = { $id[$re.Match($_.Host_Name).Value] } },* |
Where-Object { $_.DataSpace_ID -ne $null }

The output is as follows.

DataSpace_ID Host_Name        IP_Address      
------------ ---------        ----------      
1            ABC-COM-3750-SW1 IP: 192.168.1.2  
1            ABC-COM-3750-SW2 IP: 192.168.1.3 
2            DEF-COM-3750-SW1 IP: 192.168.3.5 
2            DEF-COM-3750-SW2 IP: 192.168.3.6

The following code is another solution. In this case, you do not need a regular expression.

$dataSpaces = $dataSpacesExport | ConvertFrom-Csv

$accessSwitchesForExport | ConvertFrom-Csv | ForEach-Object {
    foreach ($ds in $dataSpaces) {
        if (!$_.Host_Name.StartsWith($ds.DataSpace_Name)) { continue }
        [pscustomobject]@{
            DataSpace_ID = $ds.DataSpace_ID
            Host_Name = $_.Host_Name
            IP_Address = $_.IP_Address
        }
        break
    }
}

Compare two strings in powershell, regex * match, output DataSpace_ID field from string1 based on match, into switches listing string in own column

2 Answers