powershell - 如何从csv中解析带逗号的字符串并使用powershell脚本输出结果
问题描述
请查看示例 test.csv 文件
??????????!??????????!??????????!??????????!??????????!??????????!??????????!??????????!??????????!??????????!"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>
这是我的代码
$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
我得到的 output.csv 文件
我试图实现的期望文件
我很难显示完整的“(启用,AC up)”,但只显示“(启用”,因为逗号。我还想在 ONT 之前删除“-”
对不起,我的编辑能力差。
感谢帮助
解决方案
无需将其作为一个复杂的 csv 导入,您可以使用 switch 语句并逐行解析(就像您正在做的那样),而无需多列的额外复杂性。
这个怎么样
$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
推荐阅读
- javascript - 从本地存储中删除仅选定的列表项 - 纯 JS
- django - 想要显示详细视图,单击列表视图中的链接
- vb.net - 在 windows 中生成声音频率 - VB.Net
- sql - 如何插入具有 ADT 类型属性的数据库表中?使用 Oracle Live SQL
- python - 你如何在python中更改变量函数参数
- assembly - 优化线性序列之和 = n * (n+1)/2 - 比 lea / imul / shrd 快吗?
- r - 为什么从参数列表中通过迭代创建pdf会因for/lapply而失败?
- flutter - 使用颤振向 ClipRRect 添加阴影和彩色边框的最佳方法
- javascript - Redux 只调度了一半的函数
- graphql - 动态 slug 未使用 graphql 和 nuxt 填充 url