在PowerShell或Excel CSV中替换单引号 [英] Replace Single Quotes in PowerShell Or Excel CSV

问题描述

我正在包装一个脚本，该脚本获取软件版本并将其放入CSV，然后返回到powershell，最后返回到SQL.某些软件名称中带有单引号，不允许将其导入SQL.我试图在最终的SQL导出之前的某个时间点找到这些单引号并将其替换为反引号.我的第一个直觉是通过powershell在excel中操作查找和替换功能来进行替换，但是我不确定该怎么做，或者这是否是最好的方法.

I'm wrapping up a script which gets software versions and puts them into a CSV, then back to powershell then finally to SQL. Some of the software names have single quotes in them which is not allowed for import to SQL. I'm trying to find and replace these single quotes with back ticks at some point before the final SQL export. My first instinct is to operate the find and replace function in excel via powershell to do the replace, but I'm not sure how to go about this or if this is the best way.

任何建议都将不胜感激.我对这一切都很陌生.

Any advice is greatly appreciated. I'm very new to all this.

谢谢

edit:到目前为止，感谢您的建议alroc.我正在为这篇文章进行以下工作:

edit: Thanks for advice so far alroc. I'm working with the following for this piece:

##SQL PART##
$CSV = Import-CSV -path $UpdatePath\$($todaydate).csv
import-module sqlps
ForEach ($item in $CSV){
$CSVAppID = $($item.AppID)
$CSVAppName = $($item.AppName)
$CSVVersion = $($item.Version)
$SqlServer = "redacted"
$SqlDB = "redacted"
$SoftwareTable = "redacted"

Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SqlDB" -query "INSERT INTO dbo.ecca_sw_standard_2 (name, version, product_id) VALUES (N'$CSVAppName', N'$CSVVersion' , N'$CSVAppID')"
}

一些变量值在字符串中包含单引号，而当获得这些变量时，powershell会向我抛出错误."Invoke-Sqlcmd:'S'附近的语法不正确."

A few of the variable values contain single quotes in the strings, and powershell throws me an error when it gets to these few. "Invoke-Sqlcmd : Incorrect syntax near 'S'."

推荐答案

答案是在您的字符串中创建子表达式，以将'替换为''.

The answer is to create sub expressions in your string to replace ' with ''.

Invoke-Sqlcmd -ServerInstance "$SQLServer" -Database "$SqlDB" -query "INSERT INTO dbo.ecca_sw_standard_2 (name, version, product_id) VALUES (N'$($CSVAppName -replace "'", "''")', N'$($CSVVersion -replace "'", "''")' , N'$($CSVAppID -replace "'", "''")')"

这样，当字符串扩展时，将被适当地转义以进行SQL插入.

This way when the string is expanded it will be appropriately escaped for SQL insertion.

这篇关于在PowerShell或Excel CSV中替换单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！