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).
For this example,
you will need a scripting engine for Perl configured on your machine.
Add the Transform Script to the Assay Design
To use a transform script in an assay design, edit the design and click
Add Script next to the
Transform Scripts field. Note that you must have Platform Developer or Site Administrator to see or use this option.
To use this example as written, start from the
Assay Tutorial folder and make a copy of 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 leave the default Upload file option selected.
- Select or drag and drop the script you downloaded: 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.
- Leave Run on Import checked and Run on Edit unchecked for this example.
- Click to open the Results Fields section.
- Click Add Field.
- Enter Name: "MonthDay" and select Data Type: "Integer"
- Scroll down and click Save.
Note that you can also use a script already uploaded by selecting
Enter file path and providing the path. Learn more about the options in this topic:
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
