我正在尝试构建一个脚本，该脚本获取文本文件的内容并将其附加到工作表中，但它在第一个匹配项处停止。我希望它遍历整个文本文件并将每个匹配项附加到工作表中。
文本文件：
Node: ABC
Name: PC
Cluster: HS1
Node: XZZ
Name: CC
Cluster: HS2
Node: XYZ
Name: DD
Cluster: HS3
我想要它如何
Node|Name|Cluster
ABC PC HS1
XZZ CC HS2
XYZ DD HS3
我正在使用以下代码：
$WorkbookPath = "Destination Path"
$Patterns = "Name"
$Testbestand = get-content "Source Path"
$sheetName = "Sheet1"
$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open($WorkbookPath)
$WorkSheet = $WorkBook.sheets.item($sheetName)
$usedRange = $WorkSheet.usedRange
$lastCell = $usedRange.SpecialCells(11)
$newRow = $lastCell.row + 1
foreach ($Pattern in $Patterns){
$AlertValue = $Testbestand | Select-string -pattern $Pattern -list -quiet
$AlertValue = ($AlertValue -split " ")[-1]
$col = ($Worksheet.Columns.Find($pattern))
$WorkSheet.cells.item($newRow, $col.Column).value = $AlertValue
}#foreach pattern
$WorkBook.Save()
$objExcel.Quit()
您可以将您拥有的文本文件转换为适当的 CSV 文件，然后毫不费力地将其导入 Excel。
# read the file as a single string including newlines
$content = Get-Content -Path 'TheInputFile' -Raw
# create a regex to parse the file
$regex = [regex] '(?i)Node:s*(?<node>[^rn]*)r?nName:s*(?<name>[^rn]*)r?nCluster:s*(?<cluster>[^rn]*)'
$match = $regex.Match($content)
# collect the resulting PSObjects in variable $result
$result = while ($match.Success) {
[PsCustomObject]@{
'Node' = $match.Groups['node'].Value
'Name' = $match.Groups['name'].Value
'Cluster' = $match.Groups['cluster'].Value
}
$match = $match.NextMatch()
}
# output on screen
$result
# output to CSV file (if you like)
# $result | Export-Csv -Path 'TheOutputFile' -UseCulture -NoTypeInformation
屏幕上的结果：
Node Name Cluster
---- ---- -------
ABC PC HS1
XZZ CC HS2
XYZ DD HS3
您可以使用此$result数组将数据插入到 Excel 文件中。 像这样：
# first use the above code to parse the text file into an array of PSObjects.
# next, insert data from the $result array in the Excel
$WorkbookPath = "Destination Path"
$sheetName = "Sheet1"
$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open($WorkbookPath)
$WorkSheet = $WorkBook.sheets.item($sheetName)
$usedRange = $WorkSheet.usedRange
$lastCell = $usedRange.SpecialCells(11)
# determine the row and column positions
$row = $lastCell.row + 1
$colNode = ($Worksheet.Columns.Find("Node"))
$colName = ($Worksheet.Columns.Find("Name"))
$colCluster = ($Worksheet.Columns.Find("Cluster"))
# now loop through the parsed $result array
$result | ForEach-Object {
$WorkSheet.cells.item($row, $colNode.Column).value = $_.Node
$WorkSheet.cells.item($row, $colName.Column).value = $_.Name
$WorkSheet.cells.item($row, $colCluster.Column).value = $_.Cluster
$row++
}
$WorkBook.Save()
$objExcel.Quit()
# release COM objects from memory
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkBook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
正则表达式详细信息：
(?i) Make the Match work case-insensitive
Node: Match the characters “Node:” literally
s Match a single character that is a “whitespace character” (spaces, tabs, line breaks, etc.)
* Between zero and unlimited times, as many times as possible, giving back as needed (greedy)
(?<node> Match the regular expression below and capture its match into backreference with name “node”
. Match any single character that is not a line break character
* Between zero and unlimited times, as many times as possible, giving back as needed (greedy)
)
r Match a carriage return character
? Between zero and one times, as many times as possible, giving back as needed (greedy)
n Match a line feed character
Name: Match the characters “Name:” literally
s Match a single character that is a “whitespace character” (spaces, tabs, line breaks, etc.)
* Between zero and unlimited times, as many times as possible, giving back as needed (greedy)
(?<name> Match the regular expression below and capture its match into backreference with name “name”
. Match any single character that is not a line break character
* Between zero and unlimited times, as many times as possible, giving back as needed (greedy)
)
r Match a carriage return character
? Between zero and one times, as many times as possible, giving back as needed (greedy)
n Match a line feed character
Cluster: Match the characters “Cluster:” literally
s Match a single character that is a “whitespace character” (spaces, tabs, line breaks, etc.)
* Between zero and unlimited times, as many times as possible, giving back as needed (greedy)
(?<cluster> Match the regular expression below and capture its match into backreference with name “cluster”
. Match any single character that is not a line break character
* Between zero and unlimited times, as many times as possible, giving back as needed (greedy)
)
如果我能理解你 荷兰语评论灵活性是必不可少的，因为它涉及具有不同目标文件(和列(的不同输出优秀的 Theo 的答案： 这是我的老派方法：
$fileContent = @'
Node: ABC
Name: PC
Cluster: HS1
FooBar: x11
Node: XZZ
Name: CC
Cluster: HS2
FooBar: x12
Node: XYZ
Name: DD
Cluster: HS3
FooBar: x13
'@
$Testbestand = $fileContent.Split( [System.Environment]::NewLine,
[System.StringSplitOptions]::RemoveEmptyEntries )
### all code above substitutes $Testbestand = Get-Content "Source Path"
# compute properties from given file
$linieIdLast = ''
$linieTemplate = [ordered]@{}
foreach ( $linie in $Testbestand ) {
$linieId, $linieVal = $linie -split ":s*",2
if ( $linieId -in $linieTemplate.Keys ) {
break
} else {
$linieIdLast = $linieId
$linieTemplate.Add( $linieId, $Null )
}
}
# apply computed properties to de-serialize given file
$linieComb = New-Object -TypeName PSCustomObject -Property $linieTemplate
$liniesAll = foreach ( $linie in $Testbestand ) {
$linieId, $linieVal = $linie -split ":s*",2
$linieComb."$linieId" = $linieVal.Trim()
if ( $linieId -eq $linieIdLast ) {
$linieComb
$linieComb = New-Object -TypeName PSCustomObject -Property $linieTemplate
}
}
$liniesAll # debugging output
'' # dtto
###########################################################
# #
# the following code snippet is stolen from Theo's answer #
# #
###########################################################
$row = 33 # an example instead of $row = $lastCell.row + 1
# now loop through the parsed $liniesAll array
$liniesAll | ForEach-Object {
# and loop through the properties
foreach ( $linieKey in $linieTemplate.Keys ) {
# the next line is merely displayed;
# remove all "Quotation Marks" and `Grave Accents` to make it operational
"`$WorkSheet.cells.item($row, (`$Worksheet.Columns.Find($linieKey))).value = `$_.$linieKey"
}
$row++
}
# end of stolen code snippet
输出：.SO60233929a.ps1
Node Name Cluster FooBar
---- ---- ------- ------
ABC PC HS1 x11
XZZ CC HS2 x12
XYZ DD HS3 x13
$WorkSheet.cells.item(33, ($Worksheet.Columns.Find(Node))).value = $_.Node
$WorkSheet.cells.item(33, ($Worksheet.Columns.Find(Name))).value = $_.Name
$WorkSheet.cells.item(33, ($Worksheet.Columns.Find(Cluster))).value = $_.Cluster
$WorkSheet.cells.item(33, ($Worksheet.Columns.Find(FooBar))).value = $_.FooBar
$WorkSheet.cells.item(34, ($Worksheet.Columns.Find(Node))).value = $_.Node
$WorkSheet.cells.item(34, ($Worksheet.Columns.Find(Name))).value = $_.Name
$WorkSheet.cells.item(34, ($Worksheet.Columns.Find(Cluster))).value = $_.Cluster
$WorkSheet.cells.item(34, ($Worksheet.Columns.Find(FooBar))).value = $_.FooBar
$WorkSheet.cells.item(35, ($Worksheet.Columns.Find(Node))).value = $_.Node
$WorkSheet.cells.item(35, ($Worksheet.Columns.Find(Name))).value = $_.Name
$WorkSheet.cells.item(35, ($Worksheet.Columns.Find(Cluster))).value = $_.Cluster
$WorkSheet.cells.item(35, ($Worksheet.Columns.Find(FooBar))).value = $_.FooBar
至于这个...
感谢您的回复。难道不能循环模式吗 并附加文本文件？–
。当然，有几种方法可以将其转换为更有用的东西......
然而，你真的应该打人，从任何工具导出时修复文件，在你得到它之前你可以使用的适当的文件格式。为大家节省宝贵的时间。然而，有时我们必须处理我们得到的东西。
@'
Node: ABC
Name: PC
Cluster: HS1
Node: XZZ
Name: CC
Cluster: HS2
Node: XYZ
Name: DD
Cluster: HS3
'@ | Out-File -FilePath 'D:TempTextData.txt'
$FileContent = Get-Content -Path 'D:TempTextData.txt'
0..(($FileContent -match '^Node' -replace '^Node: ' ).Count -1) |
Select-Object @{Name = "Node";Expression = {($FileContent -match '^Node' -replace '^Node: ')[$PSitem]}},
@{Name = "Name";Expression = {($FileContent -match '^Name' -replace '^Name: ')[$PSitem]}},
@{Name = "Cluster";Expression = {($FileContent -match '^Cluster' -replace '^Cluster: ')[$PSitem]}} |
Export-Csv -Path 'D:TempTextData.csv' -NoTypeInformation
Get-Content -Path 'D:TempTextData.csv'
Import-Csv -Path 'D:TempTextData.csv'
<#
Node Name Cluster
---- ---- -------
ABC PC HS1
XZZ CC HS2
XYZ DD HS3
#>
# Or this way
$FileContent = Get-Content -Path 'D:TempTextData.txt'
0..(($FileContent -match '^Node' -replace '^Node: ' ).Count -1) |
ForEach{
[PSCustomObject]@{
Node = (($FileContent -match '^Node' -replace '^Node: ')[$_] )
Name = (($FileContent -match '^Name' -replace '^Name: ')[$_] )
Cluster = (($FileContent -match '^Cluster' -replace '^Cluster: ')[$_])
}
} |
Export-Csv -Path 'D:TempTextData.csv' -NoTypeInformation
Get-Content -Path 'D:TempTextData.csv'
Import-Csv -Path 'D:TempTextData.csv'
<#
Node Name Cluster
---- ---- -------
ABC PC HS1
XZZ CC HS2
XYZ DD HS3
#>
完成上述内容后，根据需要附加。
总有方法。 有些比其他方法更优雅，但您需要研究并决定什么适合您的用例。