英文:
How to add quotation mark to existing text in a csv file using PowerShell
问题
I can provide you with the requested translations. Please note that I will only translate the code parts and not the code itself. Here are the translations for the code snippets you provided:
- Original Text:
I need to convert strings in a csv file to strings with quotation marks around it.
My csv file looks like this:
Description;AllowHosts;SPNs;Owner
Description1;server1$, server2$, server3$;MSSQLSvc/PD01.dom1.com:1521,MSSQLSvc/PD01.dom1;Owner JDOE
Description2;server4$, server5$, server6$;MSSQLSvc/PD02.dom2.com:1521,MSSQLSvc/PD02.dom2;Owner JDOE
Description3;server7$, server8$, server9$;MSSQLSvc/PD03.dom1.com:1521,MSSQLSvc/PD03.dom1;Owner JDOE
I tried to search for header `"AllowHosts"` and replace with quotation mark in start and end,
$csv = @(
Import-Csv -Path $New -Delimiter ';' -Encoding UTF8
)
$data = ConvertFrom-Csv $csv
$Data[0].AllowHosts = '"'
$Data | where AllowHosts -Like '*$' | foreach {
$_.AllowHosts = '*$"'
}
$Data | where AllowHosts -Like 'SF' | foreach {
$_.AllowHosts = '"SF*'
}
$Data | ConvertTo-Csv -NoTypeInformation
but it did not work as expected....
I would like to have quotation mark around each string
1) in column `"AllowHosts"` (servernames)
2) in column `"SPNs"`
I am hoping for a result like this:
Description;AllowHosts;SPNs;Owner
Description1;"server1$", "server2$", "server3$";"MSSQLSvc/PD01.dom1.com:1521","MSSQLSvc/PD01.dom1";Owner JDOE
Description2;"server4$", "server5$", "server6$";"MSSQLSvc/PD02.dom2.com:1521","MSSQLSvc/PD02.dom2";Owner JDOE
Description3;"server7$", "server8$", "server9$";"MSSQLSvc/PD03.dom1.com:1521","MSSQLSvc/PD03.dom1";Owner JDOE
But how?
- Translated Text:
我需要将CSV文件中的字符串转换为带引号的字符串。
我的CSV文件如下所示：
Description;AllowHosts;SPNs;Owner
Description1;server1$, server2$, server3$;MSSQLSvc/PD01.dom1.com:1521,MSSQLSvc/PD01.dom1;Owner JDOE
Description2;server4$, server5$, server6$;MSSQLSvc/PD02.dom2.com:1521,MSSQLSvc/PD02.dom2;Owner JDOE
Description3;server7$, server8$, server9$;MSSQLSvc/PD03.dom1.com:1521,MSSQLSvc/PD03.dom1;Owner JDOE
我尝试搜索标题`"AllowHosts"`并在开头和结尾替换为引号，
$csv = @(
Import-Csv -Path $New -Delimiter ';' -Encoding UTF8
)
$data = ConvertFrom-Csv $csv
$Data[0].AllowHosts = '"'
$Data | where AllowHosts -Like '*$' | foreach {
$_.AllowHosts = '*$"'
}
$Data | where AllowHosts -Like 'SF' | foreach {
$_.AllowHosts = '"SF*'
}
$Data | ConvertTo-Csv -NoTypeInformation
但结果不如预期。
我希望每个字符串周围都有引号
1）在列`"AllowHosts"`（服务器名称）中
2）在列`"SPNs"`中
我希望结果如下：
Description;AllowHosts;SPNs;Owner
Description1;"server1$", "server2$", "server3$";"MSSQLSvc/PD01.dom1.com:1521","MSSQLSvc/PD01.dom1";Owner JDOE
Description2;"server4$", "server5$", "server6$";"MSSQLSvc/PD02.dom2.com:1521","MSSQLSvc/PD02.dom2";Owner JDOE
Description3;"server7$", "server8$", "server9$";"MSSQLSvc/PD03.dom1.com:1521","MSSQLSvc/PD03.dom1";Owner JDOE
但怎么做呢？
- Original Text:
I have a powershell script that imports csv-file and creates json-files. My problem is that this line
" ""PrincipalsAllowedToRetrieveManagedPassword"""+": [" | Out-File $filepath1 -Append
gives this result
"PrincipalsAllowedToRetrieveManagedPassword": [ "server1$, server2$, server3$" ],
instead of
"PrincipalsAllowedToRetrieveManagedPassword": [ "server1$", "server2$", "server3$" ],
- Translated Text:
我有一个导入CSV文件并创建JSON文件的PowerShell脚本。我的问题是，这行代码
" ""PrincipalsAllowedToRetrieveManagedPassword"""+": [" | Out-File $filepath1 -Append
产生了以下结果
"PrincipalsAllowedToRetrieveManagedPassword": [ "server1$, server2$, server3$" ],
而不
<details>
<summary>英文:</summary>
I need to convert strings in a csv file to strings with quotation marks around it.
My csv file looks like this:
Description;AllowHosts;SPNs;Owner
Description1;server1$, server2$, server3$;MSSQLSvc/PD01.dom1.com:1521,MSSQLSvc/PD01.dom1;Owner JDOE
Description2;server4$, server5$, server6$;MSSQLSvc/PD02.dom2.com:1521,MSSQLSvc/PD02.dom2;Owner JDOE
Description3;server7$, server8$, server9$;MSSQLSvc/PD03.dom1.com:1521,MSSQLSvc/PD03.dom1;Owner JDOE
I tried to search for header `"AllowHosts"` and replace with quotation mark in start and end,
$csv = @(
Import-Csv -Path $New -Delimiter ';' -Encoding UTF8
)
$data = ConvertFrom-Csv $csv
$Data[0].AllowHosts = '"'
$Data | where AllowHosts -Like '*$' | foreach {
$_.AllowHosts = '*$"'
}
$Data | where AllowHosts -Like 'SF' | foreach {
$_.AllowHosts = '"SF*'
}
$Data | ConvertTo-Csv -NoTypeInformation
but it did not work as expected....
I would like to have quotation mark around each string
1) in column `"AllowHosts"` (servernames)
2) in column `"SPNs"`
I am hoping for a result like this:
Description;AllowHosts;SPNs;Owner
Description1;"server1$", "server2$", "server3$";"MSSQLSvc/PD01.dom1.com:1521","MSSQLSvc/PD01.dom1";Owner JDOE
Description2;"server4$", "server5$", "server6$";"MSSQLSvc/PD02.dom2.com:1521","MSSQLSvc/PD02.dom2";Owner JDOE
Description3;"server7$", "server8$", "server9$";"MSSQLSvc/PD03.dom1.com:1521","MSSQLSvc/PD03.dom1";Owner JDOE
But how?
I have a powershell script that imports csv-file and creates json-files. My problem is that this line
" ""PrincipalsAllowedToRetrieveManagedPassword"""+": [" | Out-File $filepath1 -Append
gives this result
"PrincipalsAllowedToRetrieveManagedPassword": [ "server1$, server2$, server3$" ],
instead of
"PrincipalsAllowedToRetrieveManagedPassword": [ "server1$", "server2$", "server3$" ],
</details>
# 答案1
**得分**: 2
使用`-replace`运算符在字符串中的每个"word"周围添加`"`。
```powershell
# 读取数据到内存
$csv = Import-Csv -Path $New -Delimiter '; ' -Encoding UTF8
# 修改所有`AllowHosts`和`SPN`单元格
$csv | ForEach-Object {
$_.AllowHosts = $_.AllowHosts -replace '([^\s,]+)','"$1"'
$_.SPNs = $_.SPNs -replace '([^\s,]+)','"$1"'
}
# 重新导出
$csv | Export-Csv -Path path\to\export.csv -NoTypeInformation
模式([^\s,]+)匹配（并捕获）任何连续的字符序列，不包含,或空格，并且替代字符串"$1"扩展为"<whateverSubstringWasMatched>"。
请注意，这会引入模糊性，因为"用作CSV中的_值限定符_ - 因此Export-Csv将转义您添加的引号以保留它们，结果文件将如下所示：
"Description","AllowHosts","SPNs","Owner"
"Description1","""server1$"", ""server2$"", ""server3$""","""MSSQLSvc/PD01.dom1.com:1521"",""MSSQLSvc/PD01.dom1""","Owner JDOE"
"Description2","""server4$"", ""server5$"", ""server6$""","""MSSQLSvc/PD02.dom2.com:1521"",""MSSQLSvc/PD02.dom2""","Owner JDOE"
"Description3","""server7$"", ""server8$"", ""server9$""","""MSSQLSvc/PD03.dom1.com:1521"",""MSSQLSvc/PD03.dom1""","Owner JDOE"
英文:
Use the -replace operator to add "'s around each "word" in the string:
# read data into memory
$csv = Import-Csv -Path $New -Delimiter ';' -Encoding UTF8
# modify all `AllowHosts` and `SPN` cells
$csv |ForEach-Object {
$_.AllowHosts = $_.AllowHosts -replace '([^\s,]+)','"$1"'
$_.SPNs = $_.SPNs -replace '([^\s,]+)','"$1"'
}
# re-export
$csv |Export-Csv -Path path\to\export.csv -NoTypeInformation
The pattern ([^\s,]+) matches (and captures) any consecutive sequence of characters not containing , or whitespace, and the substitution string "$1" expands to "<whateverSubstringWasMatched>".
Beware that this introduces ambiguity, as "'s are also used as value qualifiers in CSVs - so Export-Csv will escape the quotation marks you've added to retain them, and the resulting file will look like this:
"Description","AllowHosts","SPNs","Owner"
"Description1","""server1$"", ""server2$"", ""server3$""","""MSSQLSvc/PD01.dom1.com:1521"",""MSSQLSvc/PD01.dom1""","Owner JDOE"
"Description2","""server4$"", ""server5$"", ""server6$""","""MSSQLSvc/PD02.dom2.com:1521"",""MSSQLSvc/PD02.dom2""","Owner JDOE"
"Description3","""server7$"", ""server8$"", ""server9$""","""MSSQLSvc/PD03.dom1.com:1521"",""MSSQLSvc/PD03.dom1""","Owner JDOE"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论