I’m tying up some loose ends on my code, and I’m having some trouble with my input and output file not capturing leading 0s of matrix barcodes in .csv format
The worklist is currently being manually generated (we have electronic forms in which information is input and then it reformats the data so that it can be copied and pasted into an excel sheet to be used as a worklist. Unfortunately I cannot post an example of our documentation since it’s proprietary, but I can provide more clarity if needed).
This may be something that is more on the documentation side, but how can I parse information so that the leading zero of a barcode is captured? I normally have to manually format the worklist to include the leading barcode
I think that for this I saved this worklist on the instrument computer so when I open it from this computer it automatically keeps the leading 0, but normally if I try to expand the cell to check the barcode it omits the leading 0. Not formatting it normally causes issues with the protocol because the check against the easyCode scan fails.
After reading the worklist and going through the protocol, I use the “get barcode” function for the matrix tubes so that it reads from the array of scanned barcodes and applies it to the output file. However, once the .csv output file is generated, it does not return the leading 0s on the barcodes.
when I trace out the array, the leading 0s are still there, but I’m not sure what I can do for that particular column. Should I use a different output file type? Currently it’s writing to .csv file. Or, is there a way that I can apply the apostrophe before the leading 0 to ensure that it captures the 0?
I think I was able to figure it out (though still not sure how to mitigate it)
The instrument version of Excel is Microsoft 2016. On my personal computer in which I run the program in simulation, the version of Excel is Microsoft 365. Microsoft 365 has an option to automatically notify of any automatic data conversions
There doesn’t seem to be an equivalent in Microsoft 2016, so not sure what other options I can do codewise in the method to add the apostrophe to retain the leading 0
I have encountered this issue in the past as well. The dropped leading 0 is a fun helpful feature that Excel does for you .
Luckily, there is an easy step you can take within VENUS to ensure that the dropped 0 does not affect your reads/barcode comparisons.
Utilizing the HSLStrLib library, there is a function called StrFillLeft. This can be used to make sure the string in question is exactly the specified number of characters long and will fill the missing characters with the string value of your choice. In this case, all Matrix barcodes should be 10 digits long. In your example 9 + the leading 0 to make 10. See below the logic to fill the missing 0 in while reading the file.
You can see with this logic, that the 0 was only added to the barcodes that were < 10 digits long. The last barcode which includes a leading 1 was not affected.
Thanks for the help! That seemed to work. I tested in simulation with a worklist containing 9 and 10 character barcodes and it worked as you said. I’m going to go back to the instrument computer as well just to double check that it works as well.
As I said, I would (finally) go back to confirm on the instrument that it worked. Technically, it did! it converted the worklist to 10 characters and included the leading 0. I think that the limitations of Excel 2016 made it so that the leading 0s were not included in the worklist or the output file. However, I opened the output file from the instrument on my personal computer and it included the leading 0s so this could just be something I discuss with the lab on how to tackle.
Hey @noname, I have dealt with numeric barcodes and Excel quite a bit. In our case, we had 12-digit barcodes that got converted to scientific notation.
One way we addressed this was to put one or more letters in our barcodes. If you have control over your barcode sequence you could include a non-numeric character, and you won’t have these issues with Excel anymore.
If you can’t change your barcodes, then you might need to be more creative. I think some handheld barcode scanners can prefix or suffix your scanned barcodes with arbitrary text. If your files are generated by a script you could potentially change the script to insert some text that you remove with your Venus method. I think keeping the StrFill() step is a good idea even if you address the issue outside Venus, just in case.
If you are more worried about the Venus output displaying correctly, then adjust your method to prefix the barcode with something non-numeric like “bc:” and Excel will see it as text instead of a number.
.
