SQL Command from variable worklist

Hamilton (Venus)
6

Hi @DaveWigglesworth,

Thank you for uploading the example worklist. There are a couple things that need to be taken into account here to achieve your goal:

  1. To have a variable table name in the Command String parameter, the Command String needs to be made in a variable of its own before the File: Open command is called

  2. The table name for .csv files require the following format: [TableName#csv]. The period “.” in the file name and extension needs to be replaced with a hashtag “#”

One way to go about this is to use a combination of the HSLExtensions File library (installer can be downloaded here) and the HSLStrLib to setup the Command String. Refer to the image below.

  1. Call “GetFileName” from the File library to return the file name and extension from the full file path: “ExampleWorklist1_random.csv”

  2. Call “StrReplace” from the HSLStrLib, pass in the return from “GetFileName”, “.”, and “#” to replace the period with a hashtag. The input variable is adjusted without an additional return: “ExampleWorklist1_random#csv”

  3. Call “StrConcat” from the HSLStrLib to build the Command String by concatenating the various pieces of the SQL Command along with the table name variable. In this case, input “SELECT * FROM [”, table name variable, “] WHERE [Buffer Volume] > 0”. This will yield a Command String: “SELECT * FROM [ExampleWorklist1_random#csv] WHERE [Buffer Volume] > 0”

  4. Call the Command String variable in the File: Open command. In this case, stored in “t_strSQLCommand”

image

This technique can be used for different file types (Excel files sometimes require the dollar sign “$” at the end of the Sheet name) and for other variable clauses in the Command String.

Thank you,
Dan

6 Likes