2018 Import Part 1

Introduction – 00:00

Hi there, welcome to the Freezerworks Learning Series, your visual guide to our sample management software. Today, I am going to show you how you can quickly import loads of data from an Excel spreadsheet, or text file, into Freezerworks. Now, our import option is very flexible, and it has to be, as there are so many different ways that you'll want to import sample, aliquot, and patient data. Sometimes you need to import new samples or aliquots, sometimes you need to modify existing samples (possibly by adding aliquots), and sometimes you need to update existing patient, sample, and aliquot associations.

Preparing the Text File – 00:55

In this first example, let's look at some data in Excel. These are samples with just a few fields, including the number of aliquots for each sample. You'll notice though, that freezer positions are not in my import file, that'll be our next example. This import file will simply create 10 new samples for 10 patients existing in my system with an amount of aliquots based on each sample’s Number of Aliquots entry. The first sample only has one aliquot while the 10^th has ten.

Now, before we get to Freezerworks and import this file, we need to make sure it is in the right format. While we can create and manipulate our import files in Excel we can only import .csv or .txt file types in the Freezerworks Desktop Client. So, if your import file is .xlsx or another type of Excel file, all you have to do is save it as either the valid file types. Click File – Save As, and then change the Save as Type dropdown to either .csv (Comma-delimited) or .txt (Tab delimited). We'll go with the .txt extension and click Save. Simply click through the warning messages that appear.

Creating a New Import Format – 02:17

Alright, let's open Freezerworks now and import that file. Open the Inventory Mgmt menu and select Import Inventory Data. Let's create a new format to match the import file, click Add New. Let's give it a name, how about Import A. Then we set up the Parameters, starting with whether or not there is a header record in our import file. What this is referring to is the top line in the import file that lists the field names. Our import file does have a header record, as you can see, so let's check on that box so Freezerworks will ignore it when importing. Next, we have the Field Delimiter; if you can remember, we saved our file as a tab delimited .txt file, so we select Tab. Now, the Record Delimiter; if you recall, each sample record in my import file was separated by a “New Line” so we can just leave this dropdown alone. Lastly, you'll want to make sure the ways your dates and times are written in the import file are recorded correctly here so you don't receive a bunch of messy errors. The Date Format, Separator, and Time Format all look correct to me so I can move forward.

Mapping Fields – 03:40

Next, we need to map the fields that we will be using to import. This can be done manually by using the field selector or this can be done automatically using our import file’s headers. Under Use Existing Import File to Map Fields, click Open File. Now we go find our import text file and open it. Freezerworks will look at your header record and see if any of the cells exactly match fields in your database. If they do match, the program will automatically map them in the proper order. For example, Freezerworks ID is my first column header, and the program found a field name that exactly matched it. However, these are only suggestions and can be changed if you want to import into a different field. In fact, Freezerworks ID isn't the field I wanted to use; I created a new ID field for this demonstration, so let's map that there instead. I'll first highlight the row and field I want to change, which is Column A and Freezerworks ID. Then I need to find the new field in my field selector (it'll be in the samples table as it is a sample field), and here's Sample ID. So I highlight that, ensure that Column A is still highlighted on the right, and click Map. As you can see, Sample ID is now the mapped field for the Freezerworks ID column.

Now I need to make sure all my other map fields are correct, and you'll notice that two fields are actually missing. Freezerworks was unable to find fields that match the headers exactly, but that's okay because the fields do exist, they may just be named differently. The Type header, for instance, refers to Aliquot Type, so let's map that field there by highlighting the empty line and the field, then clicking Map. While the Number of Aliquots header definitely refers to the system field, Number of Aliquots Total, the lack of clarity caused it to be missed. No matter, let's map it now.

Import Setting – 05:50

So we just got done telling Freezerworks what is contained in the import file, now we need to tell it how to import the data. To do this we'll go to the Import Settings page. First, the Import Type (what am I trying to accomplish with this import). Now, since I'm adding samples and aliquots I may want to select Add, but I'm not actually adding patients, I'm adding samples and aliquots to existing patients, so I'll select Add Samples and Aliquots to Patients. Now, be sure to pay attention to the Import Details box as it will tell you exactly what the import is going to do, based on your real-time selections. So far I'm just adding samples and aliquots to patients. Over to this right side, you'll see a few important options. First, what should be done with new dropdown items? This pertains to any choice list fields that you may be importing. Our import contains Aliquot Type, for example, which is a choice list field, and I know that I'm importing a new value. Instead of getting errors, I'll make sure Add to List is selected so the new value is accepted and added to the field’s configuration. This second question really only applies to Modify Imports, and while we are modifying patients – we’re really only adding samples and aliquots, so we'll skip this for now. Third question, how will aliquots be created? Let's check on the Automatically create Aliquots box and notice that some info has appeared in the details box. It says that aliquots will be created according to the value of the Number of Aliquots column. This is perfect since I do have the number of aliquots field in my import file. It will create one aliquot for the first record, two for the second, and so on. This quantity field would come in handy if we didn't have the number of aliquots field and wanted to create the same number of aliquots for each sample. Also, if we wanted to assign the aliquots freezer positions during the import, we could select Interactive auto-assign Aliquot positions, but we'll leave that alone for now.

Identifying Unique Fields during Import – 08:05

Alright, before we import, let's make sure the unique fields that will be identifying my imported records are correct. Since we'll be automatically creating aliquots, we only need unique IDs for the sample and patient records. Freezerworks automatically checks the fields we selected on the import fields page and finds suitable unique fields. In this case, there was only one choice for each table, and they are indeed correct. Just be sure these fields are set properly before importing, especially if you are using multiple unique fields.

Running the Import – 08:37

Finally, let's import. Click Import. Find and open your import file and let Freezerworks do its thing. Once it's finished, you'll receive a little import report. It tells you how many records of each table were created and modified. My report says I've created 10 samples and 55 aliquots while modifying 10 patients. Let's go ahead and look at the records we imported.

View Import Results – 09:15

I'll click Samples because I want to look at samples first, but don't worry we can easily see the aliquots too. Click View Records and here are the 10 samples I created through the import. I can double-click and open the records to double-check that each has the correct number of aliquots, and double-check the rest of the data on the list view itself. I can even see all of the aliquots just by clicking the Aliquots tab at the top.

Conclusion – 09:40

That about does it for today's video. This was more of a simple import; we just created samples and aliquots, and added them to patients. In the next video, we'll try importing freezer locations along with the aliquots. Thanks as always for watching, and see you next time.