SQL Command from variable worklist

Hamilton (Venus)
1

Hi,

I’m making a Normalisation method where the input worklist is selected and referenced to as “str_worklist”. For optimisation of tips and time I don’t want to add any lines where the buffer volume to transfer is 0, however I’m struggling to workout the command string to enter since the table name is variable. Probably something simple I’ve overlooked!

Capture
Capture526×611 18.9 KB

Any help appreciated !!

2

I think the source is missing. You may want to try “Select * From str_worklist where [Buffer Volume] > 0”.

3

Yes sorry, I didn’t add the source in the screenshot because I couldn’t make one that worked. I think because the str_worklist is the entire path it causes it to fail, as the Source should be just the file name

4

Could you please provide an example worklist file?

It can be uploaded to the shared folder here.

Password is Hamilton23.

5

Hi Eric,

Thanks, I have uploaded one now. Important to note the file name will never be the same but the internal format will be

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
7

Hi Dan,

Thank you this makes a lot of sense! Thanks for the speedy help!

2 Likes