0

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.

Thanks

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')"
}

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'."

TheMadTechnician
  • 34,906
  • 3
  • 42
  • 56
Oggy
  • 31
  • 2
  • 8
  • 4
    You will get much more assistance if you show the code you already have, instead of talking about your problem in an abstract way. Single quotes are perfectly legal as SQL data, assuming you're constructing & executing your inserts properly. Unless you have another need for this intermediate CSV file, it sounds like you're taking a very convoluted path to getting data into your database. – alroc Jun 18 '14 at 18:55
  • Edited some code into the original post. I'm using CSVs to log software version each day, and SQL to maintain a current list of installed software and versions. It may be convoluted but that's something I'll have to review and isn't an immediate concern. 98% of my variable insert values take but five of them have apostrophes in their names and powershell throws an error when using invoke-sqlcmd for those five. Thanks for you help so far – Oggy Jun 18 '14 at 20:54

1 Answers1

2

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 "'", "''")')"

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

TheMadTechnician
  • 34,906
  • 3
  • 42
  • 56