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 other columns in the dataset. For this simple example, we use Perl to add a column that pulls the day out of a date value in the dataset. This example builds on the design used in the
Assay Tutorial.
Additional documentation
Configure the 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.
- Enter configuration information. Details available in this topic.
Place 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, you might create a dedicated location for scripts like:
Download
CellCulture_Transform.pl to the location of your choice and remember the full path. Ex:
C:/labkey/scripts/CellCulture_Transform.pl
Set Up the Assay Design
The path to the script will be included in your assay design, and in the case of this example, will need an additional column added to hold the new value. You can edit an existing design, or to use this example, go to the
Assay Tutorial folder and copy the
CellCulture design you created during the
Assay Tutorial.
- Navigate to the Assay Tutorial folder.
- Click CellCulture 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: CellCulture_Transformed
- Transform Script: Click Add Script and enter the full path to the perl transform script, CellCulture_Transform.pl, on your local machine.
- Click to open the Results Fields section.
- Click Add Field.
- Enter Name: "MonthDay" and select Data Type: "Integer"
- Scroll down and click Save.
Import Data and Observe the Transformed Column
- On the main folder page, in the Files web part, select the file CellCulture_001.xls.
- Click Import Data.
- Select Use CellCulture_Transformed and click Import.
- Click Next.
- Click Save and Finish.
The transform script is run during data import and populates the "MonthDay" column that is not part of the original data file. This simple script uses perl string manipulation to pull the day portion out of the Date column and imports it as an integer. You can alter the script to do more advanced manipulations as you like.
Related Topics
