Execute a Powershell script using T-SQL Problem

Question

Hello,

I have created a very simple Powershell script called Simple_Excel_To_CSV.ps1:

$SourceFile = "D:\Development Projects\PowerShell - ImportExcel\Banking.xlsx"
$DestinationFile = "D:\Development Projects\PowerShell - ImportExcel\CSVFiles\Transactions.csv"
Import-Excel $SourceFile -WorksheetName Transactions |
Export-Csv $DestinationFile -NoTypeInformation

I am now trying to execute this script within a T-Sql script using the following code

EXEC xp_cmdshell 'powershell.exe  "D:\Development Projects\PowerShell - ImportExcel.Simple_Excel_To_CSV.ps1"'

When I execute the script I get the following error:

D:\Development : The term 'D:\Development' is not recognized as the name of a cmdlet, function, script file, or 
operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try 
again.
At line:1 char:1
+ D:\Development Projects\PowerShell - ImportExcel.Simple_Excel_To_CSV. ...
+ ~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (D:\Development:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
 
NULL

Thank you in advance!.

Answer 1

It's most likely a problem with quoting the files' path.

Try adding additional double quotes (in the SQL EXEC):

"""D:\Development Projects\PowerShell - ImportExcel.Simple_Excel_To_CSV.ps1"""

Every pass through a shell removes one level of quotes.