I have a worklist that has tubes and plates mixed in the worklist. I am trying to open the column for the positions as a string.
This works for:
Plate worklist:
Labware
Position
Plate
A1
Plate
B1
Plate
C1
Plate + Tube worklist(Checked box import mixed as string):
Labware
Position
Plate
A1
Plate
B1
Tube
1
But does not work for a worklist that only has tubes:
Labware
Position
Tube
1
Tube
1
Tube
1
I get a type mismatch error, as there is only integers in this column. Is there a way to force Hamilton to open the position column as a string no matter what data is inside? This seems specific to .xls, I do not have this problem with .csv
Alright so I couldn’t get this to work in Hamilton editor so I just let excel do it.
I modified the Hamilton HSL_XSL_Converterv2.1 to have a new macro to convert a specified column in an excel sheet into string values and save my excel sheet. Shoutout to alvaro for commenting the original macro code so I could copy some of his macros to get this to work.
So now I can sanitize my input sheets that have mixed tube and plate wells to strings 100% of the time.
This looks like you’ve fixed it now, but just wanted to check - how were you trying to open the file? Was it through the standard File Handling commands in Venus? If so then it should work (at least on Venus 4) - would you be able to send a screenshot of your code?
Is there a specific reason for using Excel files as an input?
I am asking this because otherwise you could use csv (you can use excel to make this too) and then read it as a string. That way your PositionID is always a string.
Aha yes, if you use SQL you need to use Excel here.
But then again, if you are only making a sequence from this list, I would not overcomplicate this with SQL or any fancy tricks, just a simple csv file does the trick, and since position ID and labware ID are both strings…no need to convert
(HSL Extentions, together with String is your best friend in datahandling…I can’t imagine a method without the HSLExtentions, I haven’t used a single SQL command or Excel file in any of my methods since 3 years)
Really!? I find the SQL invaluable and just the opposite experience! Being able to use SELECT, UPDATE, SORT by ASC and DESC, and DISTINCT is really game changers. It almost gives you “class” to work with since you are not in arrays, no indexing multiple arrays.
For example I can sort a file based on x and y positions ascending, select a distinct barcode, and then update that column as transferred all from one SQL command. That would be many many lines of code… in HSL extensions and string finding
The problem in here is that you are performing file actions. This means - at least in my setting - that I need to validate that action and make sure no user can edit data. So for me, I read a worklist to a sequence, do my datahandling there without any file generation and I am happy.
I have to say, in the past years I made a few libraries for myself that I am now using and optimized for the setting I am in now. Like said, Excel? 3 years ago I think
What’s the difference between sorting a file using HSL open command + SQL versus sorting using HSL, except more code in HSL in the latter? Isn’t reading a file a file action? Minus the UPDATE function I am not editing the file, just reading.
What I do
Open my file
Read it
Write the data to a sequence property as 1 string
Close file
Delete file
Then in the subsequent steps to be performed, simply read your sequence position, read the property and use the HSLExtention.string libary to extract the data you need. If sorting is needed, do that too.
