It can be useful to transform data columns during the process of importing data to LabKey Server. For example, you can add a column that is calculated from several other columns in the dataset. For this simple example, we add a column that contains a randomly selected value.
Topics
- Set up the Perl Scripting Engine
- Set up a New Assay Design
- Import Data and Observe the Data Transformation
Additional documentation
Set up the Perl Scripting Engine
Before you can run transform scripts, you need to set up the appropriate scripting engine. You only need to set up a scripting engine once per type of script (e.g., R or perl). You will need a copy of Perl running on your machine to set up the engine.
- Select (Admin) > Site > Admin Console.
- Click Settings.
- Under Configuration, click Views and Scripting.
- Select Add > New Perl Engine.
- Name: Perl Scripting Engine
- Language: Perl
- Language Version: Leave this blank
- File Extensions: pl
- Program Path: Provide the path to the perl program, including the name of the program. For example, "/usr/bin/perl", or a typical path on Windows is "C:\perl\bin\perl.exe".
- Program Command: Leave this blank
- Output File Name: Leave this blank
- Enabled: Confirm this is checked.
- Click "Submit"
Locate the Transform Script
To add a transform script to an assay design, you will need to enter a full path to it on your local machine. For example, if you are completing the assay tutorial on a local windows machine and uploaded the LabKeyDemoFiles package, you might find our sample perl script using a path like:
C:/Program Files (x86)/LabKey Server/files/home/Assay Tutorial/@files/LabKeyDemoFiles/Assays/Generic/GenericAssay_Transform.pl
You can also download
GenericAssay_Transform.pl to the location of your choice now.
Set Up a New Assay Design
We create a new assay design in order to avoid losing the original GenericAssay design. Using this transform script requires adding a column to the design.
Copy the GenericAssay Design.
- Navigate to the Assay Tutorial project page.
- Click GenericAssay in the Assay List section.
- Select Manage Assay Design > Copy Assay Design.
- Click Copy to Current Folder.
- Set these properties in the Assay Properties section.
- Name: GenericAssay_Transformed
- Transform Script: Click Add Script and enter the full path to the perl transform script, GenericAssay_Transform.pl, on your local machine.
- Open the Results Fields section using the (expansion) icon on the right.
- Click Add Field.
- Enter Name: "Animal" and select Data Type: "Text"
- Scroll down and click Save.
Import data and observe the transformed column
Start Import
- On the main folder page, in the Files web part, select the file LabKeyDemoFiles/Assay/Generic/GenericAssay_Run4.xls.
- Click the Import Data button.
- Select Use GenericAssay_Transformed and click Import.
Enter Batch Properties.
- OperatorEmail: john@company.com
- Instrument: ABI QSTAR
- Click Next.
Enter Run Properties
- Assay ID: Enter "Run4"
- Comment: Enter whatever you wish.
- Instrument setting: 24 (or any integer)
- Press Save and Finish.
The transform script is run during data import and adds a column called "Animal" that contains a randomly selected type of animal. The transform script used here is arbitrary; you could easily alter the script to do something different. You can see the resulting dataset
here in the demo on labkey.org and in the following screenshot:
Related Topics
