2015 Import Lesson 1

Video Transcription – 00:00

Hello, and welcome to our first instruction on how to use the import option in Freezerworks 2015.  I'm Rick Michels, and this is an example of how you can use Freezerworks to import data from your Excel™ Spreadsheet or text file into Freezerworks.  Now the import option is very flexible, it has to be because there are so many different ways that you'll want to import samples, aliquots, and patient data, so we need a tool that's as flexible as the many ways in which you need to do your work.  Sometimes you need to import new samples and new aliquots, sometimes you need to update existing samples and add aliquots, and sometimes you need to update samples, aliquots, and patient data of existing records.

Creating a File to Import – 01:00

In this first example, what we're going to do is give you an example of a data file in Excel™.  These are samples with just a few data fields and Number of Aliquots for each sample.  So, in this example that you see right here, we have 10 sample records with some data for each record, and then a number for the Number of Aliquots that each of these sample records have.  Now, I do not have box location in this example, that will be the next example.  In this situation, what I want to do is, I'm going to import each of these sample records and I'm going to create aliquots based on the number of aliquots for each of these records.  So, Sample ID - 30,000 has one aliquot, whereas 30,009 has 10 aliquots.  So, we're going to create new samples and attach aliquot records to each of those samples.  Let's go ahead and do that. 

Saving a file to be imported – 02:12

Now, it's important to remember that in Freezerworks we import CSV and .txt files, we do not import Excel™ files.  So, the step you'll have to do if you have an Excel™ file is save it as one of those two options.  Come over here, you hit the save, select save as type, and here's an option we can do: a tab delimited or CSV comma delimited file.  Let's go ahead and select tab delimited.  It gives us a txt extension, we will save this file as a .txt.  You'll get these warning messages.  Go ahead and hit yes, it's okay. Now, we'll go ahead and import that .txt file. 

Creating an Import Format in Freezerworks – 03:15

At Sample Management we select Import Data, and we're going to create a new format to match the format of our data file or Excel™ file.  Select Add New, let's give it a name, I'll call this import A.  Now, we'll have to set up our parameters. First, is there a header record included in this import file? To go back, here's our header record.  Freezerworks wants to know whether it should ignore the first record or try to import it.  Since it has a header file, we want to ignore it, so let's go ahead and select Header Record Included in Import.  The Field Delimiter, remember that we selected the tab delimiter (.txt).  A Record Delimiter, as you recall, every sample record is separated by a new line in the data file.  Our Date Format is the same here, no need to change anything. We don't have a Time Format, no time fields being imported.  The next thing we want to do is select that text file that we're going to import.  We do that by selecting Open File, and then we go and look and find the file that we created, and here it is.  

Mapping Fields – 04:40

Here's our original excel file, here is the one that we saved in .txt, so this is the one we want to select.  We press Open, what Freezerworks will do at this point is look at your header file record and your header record will have a look at the names for each of these fields that we imported, it'll then also look at Freezerworks.  If it finds an exact match, it will suggest that you may want to use that record in Freezerworks to map to it.  So, for example, it's the first record in my file with Sample ID and Freezerworks saw that there is indeed a field in Freezerworks with the exact same name, so it’s going to suggest that I go ahead and use that field to import into.  Now, in some cases, I may not want to do that, and in fact, in this case, I don't want to do that.  I created a special Sample ID field in Freezerworks that I wanted to import this into, so let me show you how I select that. What I do, is I highlight this and then I go to the table where I am importing it into, and that would be the samples table.  I have a User Defined Field down here that I called Sample IDimport.  Here's the one that it saw, but I want to actually important Sample ID from Excel™ and into Sample IDimport.  I highlight Sample IDimport, I'll highlight Sample ID, and I select Map.  Now, it's going to map the Sample ID into this Sample IDimport.  The next field was Sample Date, it found an exact match again.  That’s good, that's where I want to import it so I leave that. Now, the third field was Type and it did not find a type field in Freezerworks, but what I want to do is I want to import that type, which in this case was BUF for buffy coat.  I'm going to import that into a field in the Aliquots table.  Every aliquot record that I create is going to have an Aliquot Type of BUF, so I highlight Type and Aliquot Type and click Map, and my Type field will be imported into Aliquot Type which is in my Aliquots table.  With Patient ID, it found another field with the exact same name, that's good I'll leave it. Hospital, the same thing and that's fine.  I'm going to import those into those fields.  Then the last field was Number of Aliquots.  It did not find a match, but in this situation what I want to do is go to my Samples table.  I do have a Number of Aliquots Total field, so I want to map it to the Number of Aliquots Total so I select Map here.  

Import Settings – 07:52

Okay, now, the next thing I need to do is create some import settings to tell Freezerworks how, more specifically, I want to import these fields.  First, the Import Type, I am adding samples so I select Import Type Add, and over here it gives me the details of what I want to do.  Now, any time that I am adding samples into Freezerworks, or anytime you want to import, Freezerworks will want to know what fields should be the unique identifier.  When it looked at the import file, and it looked at fields I selected the import into, it only found one unique identifier, Sample IDimport, which is fine.  This is indeed the unique identifier that I'm going to create my samples with.  Now, it wants to know what to do with new drop down items.  I'm going to Add to List, BUF is not in Freezerworks but it's going to import and it's going to add that as an option.  I'm going to have to select here, Automatically Create Aliquots for how our aliquots will be created.  What this will do then, is, it will create for each sample an aliquot according to the number of the value of the Number of Aliquots column. Remember that I had a number in my original field for Number of Aliquots, so when I selected this option here it will automatically create one aliquot for the first record, two aliquots for the second record, three for the thirds, and so forth. Automatically create aliquots records as import the sample records based on that number.  

Completing the Import using the Import Form – 09:50

Okay, now everything looks good at this point.  So now I'm going to go ahead and do my import.  I select Import and then I select “Import Samples and Create Aliquots.txt” (the file that I want to import), and I select Open.  It writes the import data, and here is my original report.  The import is complete, and I received no errors.  I have 10 samples created with 55 aliquots created from those 10 samples.  Let's go ahead and look at those, I'm going to view these records that I imported by sample records using my list view.  Let's go ahead and hit OK here, and now I will see a list view of the ten records that I created.  Here they are, let's go ahead and take a look at each one.  I want to change my form to a different form that I created for this purpose, which would be this one here, as you can see here is my first record, Sample ID 30,000. Let's call up my data file and I have one aliquot, and it is down here.  My one aliquot, Buffy Coat, it assigned was a Freezerworks ID here and a Unique Aliquot ID as well, as well as importing my Sample ID.  Go to the next record then, and that would be 30,001, and I imported two aliquot records.  If I go to the third record imported I have three aliquot records and so forth.  So these ten records have been imported and you can see here that each one has the aliquots, so let's take a look at the last one which you should have should have 10 aliquots. Here they are.  This is a situation where we imported samples and we created aliquots.  

Conclusion – 12:10

The next example I will give you is how to import box locations.  If those are box locations or something you have in your Excel™ file as well, thank you.