Dealing with Tecan Excel Output files: Separating by Semicolons

What are scan files?

The Tecan liquid handler, a piece of lab automation equipment that replicates pipetting on a large scale, has a barcode scanner at the back which keeps track of the vials/tubes/other compound containers on its rack. This eventually lets the machine know what compound to transfer to which destination. Once the scanner has completed its scanning successfully, an output Excel file is generated on the computer the Tecan is connected to. The file is in .csv format, and it is pivotal for making worklist files that will eventually be used to run the transfer. In this article, I would like to just go over this file and how to deal with it’s peculiar format. Even though I’m addressing this format from the perspective of a Tecan user, you might find this useful for other applications as well.

Tecan scanner reading tube barcodes by sliding a tube rack. Source: https://www.youtube.com/watch?v=ujJkk2AjK28

I’ve posted a screenshot of what a Tecan scan file typically looks like below. Each row corresponds to a single tube. The first number from the left in each row tells you which rack the tube is in, the third number displays the position of the specific tube in the rack, and the last number from the left is the barcode of the tube itself. I will not go into detail about the other values within the semicolon maze because they’re not too relevant to this demonstration.

What a typical Tecan scan file looks like. Notice different values separated out by semicolons

Missing/Unread Barcodes

Sometimes, the Tecan scanner will refuse to read some of the barcodes. This could be because there is an issue with the barcode reader, or the barcodes it’s trying to scan are damaged. The scan can be retried and sometimes it will read, but other times it will show the same result. The screenshot below shows what the file looks like when the scanner misses some of the tube barcodes. The barcodes are replaced with “$$$”. Now you want to input your barcodes. You can go into each row individually and do this, which would be fine if there were only one or two barcodes missing and you knew where they were. However, if there were more than a few, and you didn’t know where in the file their location was, this method could be really tedious.

Example of a missing barcode in the middle of the file. It appears as dollar signs, as highlighted in the image.

Deconcatenate (i.e split) data

There’s a much easier way to fix the missing barcodes; separate out the numbers to make multiple columns, one of which will contain the barcodes alone. Then, using a filter you can find all the missing barcodes and input the barcodes manually instead. How do you separate out by semicolons? I’ve demonstrated this in the video capture below. All you need to do is select the column where the data is in, go to the Data tab, click Text to Columns, make sure the Delimited option is selected, click Next, select Semicolon, and click Next again, leave the default General selected and then click Finish and you’re done! Now you can click the Filter button and spot all the missing barcodes and change them as you wish. Remove the filter and now you have a column with no more missing barcodes.

This video demonstrates how to deconcatenate the data for editing the Tecan scan file

Concatenate data

Now, you would want to go back to the original semicolon format because that’s what you need to make a worklist file which the Tecan will use for it’s dispense. For this you can use the Concatenate formula. The formula is used as shown in the video capture below. All the columns are to be merged into one so the cells are chosen one by one and have a semicolon in between each of them to replicate the original format as shown below. It’s important not to forget the semicolon in between each piece of data across the row or else you will not get the original format back. You can drag the formula down and then copy and paste values onto the original location and get rid of the excess columns. Now you have your original scan file format back with no missing barcodes. Again, I have demonstrated this in the video below.

This video demonstrates how to concatenate the data to get back the original format back for editing the Tecan scan file

Faster methods?

Ok, so this is all fine and dandy, but what if you need to do this concatenating/deconcatenating action again and again because your Tecan scanner keeps on misreading barcodes? You would probably want to be able to it in a more convenient fashion rather than going through these steps every single time.

One simple way to streamline the process is to use as many keyboard shortcuts as possible. For example, for the deconcatenating part, instead of selecting the first column of data, press Ctrl+Spacebar to select the first column, then press Alt+a+e to get to the Text to Columns prompt, then press Enter. Instead of choosing the semicolon option with a mouse, you can press m instead, and then press Enter a few times again. You will get the data deconcatenated as before, and it can be way faster than dealing with the mouse. You can find similar shortcuts for the concatenation part as well.

Keyboard shortcuts are faster than the traditional clicking around Excel, but it can still be a bit tedious to deal with over and over. A much quicker way to deal with this repetition is to record a macro. First of all, enable the Developer tab by going into File>Options>Customize Ribbon, and click on the Developer checkbox to enable it under the Main Tabs section. The tab is disabled by default, but if you have already have the tab enabled, then don’t worry about this step. Now under the developer tab, enable the click Record Macro and the following prompt shown in the picture below will show up. You can pick a name for your macro and also choose a shortcut. I picked my deconcatenate macro name to be “Split” and chose Ctrl+Shift+S to be the shortcut

Prompt for naming your macro and defining a shortcut

Once you press the OK button, the macro will start recording. Now just repeat the deconcatenate steps from the top as shown earlier, using either your mouse or the faster keyboard shortcuts. Once the data is deconcatenated, just press the Stop Recording button which replaces the Record Macro button as shown here.

Now your macro is recorded and ready for use. You can simply use your shortcut as you defined it to call on the macro to deconcatenate again. You can repeat this same method for the concatenate technique. Once you have both macros in your sheet, you can concatenate and deconcatenate like no one’s business! Make sure you save your file as a macro enabled Excel sheet if you want to use the macros again. I’ve posted a video to show you how ridiculously fast it is to split then concatenate again using macros with keyboard shortcuts. You can also alternatively click Macros under the Developer tab and choose the macro you defined and click Run. However, this technique is slower than just using the shortcut you defined.

It is ridiculously fast and easy to split and concatenate data with recorded Macros as shown here

Ok, so that’s pretty fast. But is there an *even* faster way? Actually, yeah! That way would be to write a custom macro using Visual Basic script, but I’ll leave that for a future blog post.

Summary

I’d just like to summarize the basic steps for both the concatenate and deconcatenate method right here.

Deconcatenate:

• Select column of interest
• Go to Data>Text To Columns (Or use Alt+a+e)
• Click Next>Enable Semicolon checkbox (Or use Enter and press m to choose semicolon)
• Click Next>Finish

Concatenate:

• Write =CONCAT( right next to data
• Choose each of the columns, separating out by semicolons, and complete the formula
• Drag down formula
• Copy/paste new values onto first column
• Delete all the excess columns