导入csv的1列，替换字符串，然后将每一行用引号括起来连接成一行

Question

我使用powershell，我有一个csv文件，有大量的数据，但我唯一关心的字段是第一个。

I need to:

1. 提取数据头的第一列:Ticket_ID
2. 从该列的值中删除尾随字符串(站点:)
3. 将列中的所有值连接到单个以空格分隔的行中，但确保每个值保留双引号。
4. 在该行前加上一个长字符串，并将其写入文件。(我还没有尝试解决这部分，因为我不能让1-3完全工作。)

我已经尝试了这个代码的几种排列:

$TT = (Import-Csv '.sourcefile.csv').______Ticket_ID______ -join (" ")
$TT = $TT -replace 'site:',''
$TT | Set-Content -Path .JulyTickets.csv

csv文件示例:

"______Ticket_ID______","____________Title____________","___________CreateDate___________"
"site:TICKET1","SOMETITLE","01/01/1901"
"site:TICKET2","SOMETITLE","01/01/1901"
"site:TICKET3","SOMETITLE","01/01/1901"
"site:TICKET4","SOMETITLE","01/01/1901"
"site:TICKET5","SOMETITLE","01/01/1901"

我的挫折:如果我使用Export-CSV，生成的csv只包含值的字符长度。

如果我使用Set-Content，我可以成功地提取第一列，并删除前导字符串(site:)，将所有行连接为一行，并将其写入文件，但它不会保留csv ("site:TICKET1"中每个值周围的双引号。输出如下所示:

TICKET1 TICKET2 TICKET3 TICKET4 TICKET5

我不确定我是否在这里做的事情没有顺序，或者如果我错过了一些东西，但我无法将两个值都内联，并单独用引号括起来。

所需输出:

this is a long string that needs to be prepended to the data "TICKET1" "TICKET2" "TICKET3" "TICKET4" "TICKET5"

Answer 1

应该可以了。最后需要管道和PowerShell的计算属性表达式是为了克服PowerShell删除双引号

的问题。

$string = 'this is a long string that needs to be prepended to the data ' + (Get-Content -Path .sourcefile.csv | Convertfrom-Csv -Delimiter "," | select ______Ticket_ID______ -ExpandProperty ______Ticket_ID______ | ForEach-Object {$_ -replace 'site:', '';} | select @{ Name = 'Ticket';  Expression = {$([char]34) + $_ + $([char]34)}} | Select Ticket -ExpandProperty Ticket);
write-host $string

返回

this is a long string that needs to be prepended to the data "TICKET1" "TICKET2" "TICKET3" "TICKET4" "TICKET5"

Answer 2

试试这个，我使用-Header参数，因为唯一需要的列是第一个。

$csv = @'
"______Ticket_ID______","____________Title____________","___________CreateDate___________"
"site:TICKET1","SOMETITLE","01/01/1901"
"site:TICKET2","SOMETITLE","01/01/1901"
"site:TICKET3","SOMETITLE","01/01/1901"
"site:TICKET4","SOMETITLE","01/01/1901"
"site:TICKET5","SOMETITLE","01/01/1901"
'@ |
ConvertFrom-Csv -Header ID |
Select-Object -Skip 1 |
ForEach-Object {
'"{0}"' -f $_.ID.TrimStart('site:')
}
$toPrepend = 'this is a long string that needs to be prepended to the data'
"$toPrepend $($csv -join ' ')"
# Above yields => this is a long string that needs to be prepended to the data "TICKET1" "TICKET2" "TICKET3" "TICKET4" "TICKET5"

注意，这里我只使用ConvertFrom-Csv作为示例，但在您的情况下，它应该是这样的:

$TT = Import-Csv '.sourcefile.csv' -Header ID | Select-Object -Skip 1 | ForEach-Object {
'"{0}"' -f $_.ID.TrimStart('site:')
}
$toPrepend = 'this is a long string that needs to be prepended to the data'
"$toPrepend $($TT -join ' ')" | Out-File JulyTickets.csv # => Not sure why CSV here, since this is no longer a CSV