Need help sorting data line by line in powershell

Question

I need to go through cisco configuration files and pull specific information out to draw a Layer 3 diagram of our network. I need to pull the interface, address, vlan ID, description, etc from the configuration and arrange it in a table or excel spreadsheet. The way I am thinking of the code in my head is setting it up to somehow say "between the word vlan on line x and the next instance of the word vlan, take this information", and then adapting that for between the words interface, etc. But I'm not sure how to put that into code. I am unfortunately fairly new at powershell

example of the small part of the cisco config I'm working with. What I am trying to get out of it is vlan and number from the "vlan" line and the full name line from the line immediately under.

vlan 2
  name mgmt
vlan 10
  name vmotion_tools
vlan 13
  name perfmontools_10.12.144.24
vlan 14
  name perfmontools_10.12.145.2
etc

This is what I have so far:

foreach($line in [System.IO.File]::ReadLines('....\Config_test.txt'))
    {
        #look for line containing vlan
       if ($line -like '*vlan*'){
        $vlanid = $line
        }
        #look for line containing name
       if ($line -like '*name*'){
        $vlanname = $line
        $vlanname = $vlanname -replace ".*name "    
        }
        #look for line containing IP
       if ($line -like '*ip*'){
        $vlan_ip = $line  
        }

        @{name = $vlanid; description = $vlanname} | Out-String 

    }

And it gives the following result:

Name                           Value                                                                                                                                    
----                           -----                                                                                                                                    
name                           vlan 242                                                                                                                                 
description                    perfmontools-172.xxx                                                                                                            


Name                           Value                                                                                                                                    
----                           -----                                                                                                                                    
name                           vlan 242                                                                                                                                 
description                    perfmontools-172.xxx
etc
etc
etc

So they are at least grouped together correctly. I would like the name and description to appear on one line, like a table, and continue down from there. It's slow going when you don't know what you're doing :)

The end goal of this is to put all of this information into an excel spreadsheet

The config file is over a 1000 lines long, and I'm starting small with just getting the vlan name and description, but here is the small sample I am working with vlan 9 name perfmontools1 vlan 28 name perfmontools2 vlan 32 name perfmontools 3 and so on and so on. (the formatting got jacked up when I hit submit. It goes vlan 9 (new line) name perfmontools1 (new line) vlan 28...... — Dnet
– Dnet, Commented Aug 9, 2019 at 17:09
Please edit your post, comments are terrible for code and data :) — Mathias R. Jessen
– Mathias R. Jessen, Commented Aug 9, 2019 at 17:16
The content emitted by Get-Content is not an object with properties like Name and ID. Each object is just a line of text. If your input were a CSV file with header "Name, ID", you could easily use Import-CSV. As it is, you are going to have to parse your input file. I hesitate to offer advice because you are new to Powershell. — Walter Mitty
– Walter Mitty, Commented Aug 9, 2019 at 17:35
Do I have to parse the file before going through the where-object section? I'm not able to import as a csv file — Dnet
– Dnet, Commented Aug 9, 2019 at 17:40

mklement0 · Accepted Answer · 2019-08-09 21:51:20Z

1

I suggest using a switch statement with the -File and -Regex parameters:

$infoObjs = switch -Regex -File '....\Config_test.txt' {
    '^vlan ' { $name = $_; continue }
    '^\s+name (.*)' {
       # Output a custom object with the name and the description.
       # $infoObjs will collect them in an array.
       [pscustomobject] @{
            Name = $name
            Description = $Matches[1]
       }
       continue
    }
}

# Now you can send the objects to Export-Csv to create
# a CSV file, for instance:
$infoObjs | Export-Csv -NoTypeInformation info.csv

If you output $infoObjs directly to the console, you'll see something like:

Name    Description
----    -----------
vlan 2  mgmt
vlan 10 vmotion_tools
vlan 13 perfmontools_10.12.144.24
vlan 14 perfmontools_10.12.145.2

answered Aug 9, 2019 at 21:51

mklement0

453k68 gold badges728 silver badges988 bronze badges

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

Comments

Doug Finke · Accepted Answer · 2019-09-13 02:05:00Z

0

Install-Module ImportExcel can handle the creation of the Excel file.

Here's a way to parse the text.

$data = @"
vlan 2
  name mgmt
vlan 10
  name vmotion_tools
vlan 13
  name perfmontools_10.12.144.24
vlan 14
  name perfmontools_10.12.145.2
"@ -split "`r`n"

$(for ($i = 0; $i -lt $data.count; $i++) {
    [PSCustomObject][Ordered]@{
        Name = $data[$i++]
        Description = $data[$i].Trim() -replace "name ",""
    }
}) | Export-Excel

edited Sep 13, 2019 at 2:05

answered Sep 11, 2019 at 19:57

Doug Finke

6,8432 gold badges34 silver badges48 bronze badges

3 Comments

Lance U. Matthews Over a year ago

Is there an additional module to execute the code embedded in the screenshot? Or perhaps a better way to represent that?

Doug Finke Over a year ago

Yes, the Install-Module ImportExcel at the top of the comment, then it auto imports.

Lance U. Matthews Over a year ago

What I'm getting at is that, by posting your code as a screenshot, anyone who might want to try it for themselves will have to retype it, whereas if it were posted as text that would make things easier and more accessible for everybody. I just found it ironic that an answer about extracting text itself requires text extraction.

Collectives™ on Stack Overflow

Need help sorting data line by line in powershell

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related