Hello everyone,
I am using the SELECT DISTINCT SQL command for a .csv files and I noticed that it automatically alphabetize the selected distinct variables.
I was wondering if there is a way around this so that the array is built in the same order as the .csv file from top to bottom. Below is my method for opening and reading the file:
Any help or advices are greatly appreciated!
-Nat
is there another column in the table with the order? e.g. “Id”
No, there is no other columns in my table with the same header as “sample_name” nor exhibit that order the array is showing. Also, based from this post, the SELECT DISTINCT command only reads the column of interest.
https://forums.pylabrobot.org/t/select-distinct-sql-command-not-available-with-csv-files/1651?u=nat
Hi @Nat ,
SQL Select distinct will always sort the results and there isn’t a way around that using that technique. If you would like the distinct values but in file order, I would suggest reading the file normally and using a Lookup Function (from HSLUtilLib, or HSLExtensions::Array) to determine if the value is in your output array and only add it if it doesn’t exist. See the logic below:
Results:
Thank you so much for the help as it works and definitely fits my need! I do have one more follow up question. Is there a function or way to count the distinct values (e.i. count how many times each value shows up in the column)? Similarly, I have explored using the SELECT COUNT SQL command but not much success with it.
Hi @Nat ,
You would need to loop over your unique sample names from the previous example and get the count from the file where sample_name = ‘SampleName’.
Results:
Note: You need surround the variable for the current sample name with a ’ since it is a string value it needs to look for. The result of the COUNT function also needs to be named and then referenced in your File Open. In the example above, I named the result as N so that is the value that I read from the file.
Thank you for the example and explanation! I followed the example you provided however, I ran into this error:
error; An error occurred while running Vector. The error description is: C:\Program Files (x86)\HAMILTON\Methods\antigen_production\Post_Production_scales_v2.med(36, 1) : No value given for one or more required parameters. (0x23 - 0x2 - 0x39) ,
It appears that this error is inherent when using SELECT COUNT for .csv files. An alternative solution was provided but I am unsure on how to set that up.
https://forums.pylabrobot.org/t/venus-sql-help/625/13?u=nat
-Nat
Hi @Nat ,
I was able to reproduce and correct this issue. For the counting File Open command you need to make sure to use Open file to read, not Open file to append.
That is weird because the mode for the second time I open the file is already set at Open file to read. Building the sample_name array from the file works fine but when it comes to opening the file for the second time is when the error occurs. I also tried with a .csv file that only contains the sample_name column but to no luck resolving the error. Perhaps I could send you the pkg, trc, and corresponding template files?
Hi @Nat ,
Here is the example method I was using to test this. The worklist should import to the same folder.
Get Unique File Order
Password: Hamilton24
Thank you for the example method as I was able to take a closer look between the two methods and noticed that the error occurred due to how I concatenated my string. The single quotation marks around t_strSampleName was missing, which was hard to distinguish next to the double quotation mark and tiny text 
I apologize for the hassle as I’m thankful for your help! I am excited to explore more into working with spreadsheets now that I understand these principles!
