Powershell: Specify file path as variable

Question

I am running the following SQL query through a powershell script and need to run the script multiple times against different files. So what I am trying to figure out is how to specify a file path as a variable when I run the script?

update [$Db_name].[dbo].[$BatchTable]
set [$Db_name].[dbo].[$BatchTable].Wave = 'Wave1.1'
from [$Db_name].[dbo].[$BatchTable]
inner join OPENROWSET(BULK 'FilePath\file.csv',    
FORMATFILE= 'E:\import.xml') AS a
on ([$Db_name].[dbo].[$BatchTable].Name= a.Name) and  
([$Db_name].[dbo].[$BatchTable].Domain = a.Domain)

The 'FilePath\file.csv' is the file path I need to define as a variable so that my code would instead look like this:

inner join OPENROWSET(BULK '$INPUTFILEPATH',    
FORMATFILE= 'E:\import.xml') AS a

Any help or potentially better methods to accomplish this would help very much.

From the command like I want to be able to run the script like this:

CMD: updatescript.ps1 $INPUTFILEPATH = C:\Documents\myfile.csv

Again, I'm not sure this is the best way to go about this?

Graham Gold · Accepted Answer · 2015-04-29 23:06:12Z

You're nearly there. You will need to add a parameter block at the very start of your script e.g.

Param( 
[Parameter(Mandatory=$true)]
[ValidateScript({Test-Path $_ -PathType 'leaf'})]  
[string] $InputFilePath  
)

This creates a mandatory (not optional) string parameter, called InputFilePath, and the ValidateScript is code used to validate the parameter, in this case checking the file exists using the Test-Path cmdlet and pathtype of leaf (if checking existence of a directory use 'container').

When running your script use the syntax below:

updatescript.ps1 -INPUTFILEPATH "C:\Documents\myfile.csv"

and in the script use the variable as the path exactly as in your question:

inner join OPENROWSET(BULK '$INPUTFILEPATH',    
FORMATFILE= 'E:\import.xml') AS a

NOTE: in powershell when using parameters when running a script you only need to use the least amount of characters that uniquely identify that parameter from all the others in your param block - in this case -I works just as well as -InputFilePath.

jithinpt · Accepted Answer · 2015-04-29 22:42:10Z

1

You can pass command line parameters to the powershell script using param.

Example:

param(
   [string]$INPUTFILEPATH
)

And then call the script as follows:

updatescript.ps1 -INPUTFILEPATH C:\Documents\myfile.csv

More details about cmd line parameters can be found here

answered Apr 29, 2015 at 22:42

jithinpt

1,2642 gold badges19 silver badges35 bronze badges

Collectives™ on Stack Overflow

Powershell: Specify file path as variable

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related