How to parsing string with comma from csv and output result using powershell script

Question

Please see a sample test.csv file

??????????!??????????!??????????!??????????!??????????!??????????!??????????!??????????!??????????!??????????!"CalixE7" Wed Feb 19 15:12:51 2020

Username: 
Password: 
CalixE7>disable session pager
success: pager disabled
CalixE7>disable session alarm-notif
success: alarm notifications disabled
CalixE7>disable session event-notif
success: event notifications disabled
CalixE7>disable session tca-notif
success: TCA notifications disabled
CalixE7>show ont 1301 summary
ONT "1301" (enabled)
    serial number: 3421F0
    profile: 726GE
    - SIP IP host
        ip: 172.24.4.73, netmask: 255.255.254.0, gw: 172.24.4.11 (DHCP)
        service tag action: VOICE-ADDTAG (add-tag)
            outer tag: 4, outer p-bit: pbit7
            match list: all-untagged
    - TDM GW IP host
        ip: 0.0.0.0, netmask: 0.0.0.0, gw: 0.0.0.0 (DHCP)
        service tag action: <not configured>
    - H.248 IP host
        ip: 0.0.0.0, netmask: 0.0.0.0, gw: 0.0.0.0 (DHCP)
        service tag action: <not configured>
    - MGCP IP host
        ip: 0.0.0.0, netmask: 0.0.0.0, gw: 0.0.0.0 (DHCP)
        service tag action: <not configured>
    - ONT RF port "1301/r1" (enabled, AC up)
    - ONT Ethernet port "1301/g1" (enabled, AC up)
         security profile: system-default
         member of: ONT Residential GW port "1301/G1"
    - ONT Ethernet port "1301/g2" (enabled, AC up)
         security profile: system-default
         member of: ONT Residential GW port "1301/G1"
    - ONT Ethernet port "1301/g3" (enabled, AC up)
         security profile: system-default
         member of: ONT Residential GW port "1301/G1"
    - ONT Ethernet port "1301/g4" (enabled, AC up)
         security profile: system-default
         member of: ONT Residential GW port "1301/G1"
    - ONT POTS port "1301/p1" (enabled, AC up)
         signal type: loop-start
        - ONT SIP service (enabled)
            SIP gateway profile: AA-VoiceGW, user: 1682
    - ONT POTS port "1301/p2" (enabled, AC up)
         signal type: loop-start
        - ONT SIP service (user disabled)
            SIP gateway profile: AA-VoiceGW, user: 1682
    - ONT Residential GW port "1301/G1" (enabled)
         management-mode: native
             Eth-Svc for In-band TR69: Data1
         This port has these members: 
             1301/g1         1301/g2         1301/g3 
             1301/g4
        - Ethernet service "Data1" (enabled)
            multicast profile: <none>
            bandwidth profile: 100m/100m
            service tag action: HSDA-CHANGETAG (change-tag)
                outer tag: 3, outer p-bit: pbit0
                match list: AA-TAGGED-3
            pon upstream cos: derived
    - ONT Full Bridge port "1301/F1" (enabled)
         security profile: system-default 
         This port has these members: <none> 
CalixE7>show ont 1302 summary
ONT "1302" (enabled)
    serial number: 434B51
    profile: 726GE
    - SIP IP host
        ip: 172.24.4.201, netmask: 255.255.254.0, gw: 172.24.4.11 (DHCP)
        service tag action: VOICE-ADDTAG (add-tag)
            outer tag: 4, outer p-bit: pbit7
            match list: all-untagged
    - TDM GW IP host
        ip: 0.0.0.0, netmask: 0.0.0.0, gw: 0.0.0.0 (DHCP)
        service tag action: <not configured>
    - H.248 IP host
        ip: 0.0.0.0, netmask: 0.0.0.0, gw: 0.0.0.0 (DHCP)
        service tag action: <not configured>
    - MGCP IP host
        ip: 0.0.0.0, netmask: 0.0.0.0, gw: 0.0.0.0 (DHCP)
        service tag action: <not configured>
    - ONT RF port "1302/r1" (enabled, AC up)
    - ONT Ethernet port "1302/g1" (enabled, AC up)
         security profile: system-default
         member of: ONT Residential GW port "1302/G1"
    - ONT Ethernet port "1302/g2" (enabled, AC up)
         security profile: system-default
         member of: ONT Residential GW port "1302/G1"
    - ONT Ethernet port "1302/g3" (enabled, AC up)
         security profile: system-default
         member of: ONT Residential GW port "1302/G1"
    - ONT Ethernet port "1302/g4" (enabled, AC up)
         security profile: system-default
         member of: ONT Residential GW port "1302/G1"
    - ONT POTS port "1302/p1" (enabled, AC up)
         signal type: loop-start
        - ONT SIP service (enabled)
            SIP gateway profile: AA-VoiceGW, user: 1683
    - ONT POTS port "1302/p2" (enabled, AC up)
         signal type: loop-start
    - ONT Residential GW port "1302/G1" (enabled)
         management-mode: native
             Eth-Svc for In-band TR69: Data1
         This port has these members: 
             1302/g1         1302/g2         1302/g3 
             1302/g4
        - Ethernet service "Data1" (enabled)
            multicast profile: <none>
            bandwidth profile: 50m/50m
            service tag action: HSDA-CHANGETAG (change-tag)
                outer tag: 3, outer p-bit: pbit0
                match list: AA-TAGGED-3
            pon upstream cos: derived
    - ONT Full Bridge port "1302/F1" (enabled)
         security profile: system-default 
         This port has these members: <none> 
CalixE7>

This is my code

$OutputPath = .\output.csv
$topheader = "TAG"+","+"Datetime Created"+","+"Ont"+","+"RF"+","+"SIP"+","+"Data"+","+"Bandwidth"
$ontdetail = Import-CSV -Path .\test.csv | Foreach-Object {

    foreach ($property in $_.PSObject.Properties){

            if($property.Value -Like 'ONT "*'){
                $header = $property.Name
                $length = $header.length
                $year = $header.substring($length -4,4)
                $month = $header.substring($length -20,3)
                $day = $header.substring($length -16,2)
                $time = $header.substring($length -13,8)
                $timestamp = $day+"-"+$month+"-"+$year+" "+ $time
                $ont = $property.Value                   
            }

            if($property.Value -Like "*RF*"){
                $rf = $property.Value
            }

            if($property.Value -Like "*ONT POTS*p1*"){
                $sip = $property.Value
            }

            if($property.Value -Like "*ONT Residential*"){
                $data = $property.Value
            }

            if($property.Value -Like "*bandwidth profile*"){
                $bandwidth = $property.Value
                $detail = "CalixE7"+","+$timestamp+","+$ont+","+$rf+","+$sip+","+$data+","+$bandwidth
                $detail            
            }
    }
}

$ontdetail
($topheader,$ontdetail) | Out-File $OutputPath

The output.csv file i'm getting

The expect file I'm trying to achieve

I have difficulty to have full "(enabled, AC up)" displayed but only display "(enabled" because comma. I also want to remove "-" before ONT

Sorry for my poor editing skills.

Thanks for help

Felix JIN · Accepted Answer · 2020-02-20 07:17:34Z

1

Instead of importing it as one convoluted csv, you can use a switch statement and parse line by line (kind of like you are doing anyway) without the extra complexity of multiple columns.

How about this

$OutputPath = '.\output.csv'
$topheader = "TAG,Datetime Created,Ont,RF,SIP,Data,Bandwidth"

[string]$DateTimeCreated = $null
$ontdetail = switch -file .\test.csv -Wildcard
{
    '*CalixE7*' 
    {
        if (!($DateTimeCreated)) # processed only for first line
        {
            $length = $_.Length
            $year = $_.substring($length -4,4)
            $month = $_.substring($length -20,3)
            $day = $_.substring($length -16,2)
            $time = $_.substring($length -13,8)            
            $DateTimeCreated = "$($day)-$($month)-$($year) $($time)"
        }
    }

    '*ONT "*' {$ont = $_}
    '*RF*' {$rf = $_.Trim(' -')}
    '*ONT POTS*p1*' {$sip = $_.Trim(' -')}
    '*ONT Residential*' {$data = $_.Trim(' -')}
    '*bandwidth profile*' {"CalixE7,$($DateTimeCreated),$($ont),$($rf),$($sip),$($data),$($_.Trim())"}
}
$ontdetail
($topheader,$ontdetail) | Out-File $OutputPath

edited Feb 20, 2020 at 7:17

Felix JIN

1011 gold badge1 silver badge12 bronze badges

answered Feb 19, 2020 at 7:08

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

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

3 Comments

Felix JIN Over a year ago

Thanks, it works for displaying line with comma, but the datetime won't be picked up from the first line due some reasons. It will display CalixE7, , ONT "1301" ---etc

Felix JIN Over a year ago

Thanks, I figured out need add an identifier "CalixE7" for the first line to parse the date time instead of using '.*'

Lieven Keersmaekers Over a year ago

@FelixJIN - my bad. I changed from -Regex to -Wildcard because that more closely resembled your original script but I forgot to adjust that one .* to *. Your edit is performance wise even better.

Collectives™ on Stack Overflow

How to parsing string with comma from csv and output result using powershell script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related