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.
Add the Transform Script to the Assay Design
Edit the assay design to add a transform script. 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 select or drag and drop the script (CellCulture_Transform.pl).
- The script will be uploaded to the @scripts folder under the folder's file root. You'll see the full path in the UI.
- 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