Data Import and Transformation
- Michael Luecke
- Thomas Legowo
- Chris Jones
Overview
To assist with importing data into the system, there is a range of functionality to help with getting the data, into the tables and format that you require. This can assist with importing data from a spreadsheet, via LDAP or other integrations that you may utilise. This is done in a three step process which starts with importing data via a data source, which will enter a staging table that you can review, which ends with using a transform map to help it populate its destination. To help describe this process, this article will use the steps from a spreadsheet, but this mechanism can also be interacted with other methods if required.
Data Sources
The first steps with defining a source where data can be imported from. These data sources can either be added manually prior to the initial import, but can also be created at the same time as the initial import. For the purpose of this article, it will detail both options, but start with creating the data source dynamically and using an excel document.
For this example (and remainder of this article), we will use the below spreadsheet to import 2 new starters into the organisation.
Import Data
To start off this process, proceed to the Import Data menu item.
From here, you will need to create or select a staging table, as well as select or create an import source. As this article assumes you are doing this for the first time, it will detail it from creating a new staging table and data source, but note that if you want to reimport a file, or have these already defined, you can also use this page and mechanism to reimport from a data source.
Although this will be detailed in more detail later, if you are creating a staging table, you generally want to give it a name that is identifiably a database table and not used. As a result we generally recommend something along the lines of <Company Abbreviation><Target Table>DataImport. However, you can make it whatever is unique.
Before attaching the excel file as a document, we generally recommend considering the following prior to attaching a file.
Remove any styling, formatting or anything that may impact the data being imported
Ensure there are no formulas in the document (although technically supported, we typically suggest only importing values)
Ensure any column with data has a column heading
When possible, cleanup the data to import. Keep in mind however, when we get to the transformation stage, you can transform the data as you want.
Import using 1 tab and having the column headings in row 1.
You will also notice there are other options on this page, however typically you can ignore the Display XLS/CSV settings, if you follow our recommendations, this can be ignored.
In addition to this, there is also a section where you can use any existing transform maps immediately after using the import. This can be used if you already have a transform map in place, but for this example (given that you would be creating a staging table at the same time), would not be used.
Once done, press submit and a popup will come up (note that you don’t need to wait for this, as it happens in the background, but it normally makes sense to leave it so you can complete the next steps when done).
Once imported, you will be provided with the following page, where you can then see the log, staging table and data source.
Any imports that you do, will have an import data source (which you can access in the menu with the same name). If you are doing multiple imports from the same source, we typically suggest you update the data source, rather than always creating a new data source and staging table. Therefore, in the example you may import a spreadsheet, but then later decide that you need to update it. You can access the data source via the menu, update the attachment and then in the first step use “existing staging table” and “existing import source”. It should be noted that if you are importing with other data sources or otherwise, you will also need to use this page, but you would most likely create the data source, prior to the initial import.
Staging Tables
Staging tables within the system allows you to view the data, prior to it entering the source. You can access any staging table created via the “Import staging table” menu item.
In short, staging tables, although are tables within the system, are built for the purpose of importing data, so you would never need to create any fields manually, or create any functionality about it. In the running example we are using, if we look at the staging table that was created when we imported the excel document, you would see the following screen. It should be noted that every row / record of the data source will translate to one record in this table. As a result, once you have transformed the data and even before then, you may look at the data within this table, to understand what has been imported or not imported accordingly
To explain the purple buttons and their purpose.
View records: This allows you to see any records that have entered the staging table, their data, and then their status and target (once transformed). This is typically used to help understand if data didn’t import the way you expected to, the reason why and / or understanding what record it updated. It is imported to note, as the staging tables are generated differently to normal tables, you may need to refresh your browser before field labels appear correctly. It should also be noted, as every historical import record will be here, you will often see “duplicate” records, but may be because you imported the same data multiple times.
Delete temporary table: This deletes the staging table and any records within the staging table (not the target table).
Generate temporary table: This generates the staging table. This is normally not required, because as you import data, it will automatically generate it.
Reset state: This will reset the state of EVERY record within the staging table from EVERY import. Therefore making it that the next time you import, every record will be reimported. Warning: As this resets the state of every record, this can cause you to import a lot of records unnessacarily and therefore not recommended unless you have only 1 import worth of data there.
Delete records: This will delete any record within the staging table (not the target table).
As these tables can grow quite substantially overtime, we recommend setting up a table cleanup record for each of your staging tables. See System Table Cleanup
Transform Maps
To allow data to get into the target table, you need to create a transform map. There are a few different ways to do this, however, for the purpose of this example, it will detail the manual method, to provide insight as to how the parts all hang together. To create a transform map the manual way, go to the transform maps menu item.
Once on this screen, you’ll want to give it a name (for informational purposes), description and source and target table as a starting point. The source table will be the staging table created, whilst the target table, where you want the data to go.
The main element of transform maps that you will need to generate are the field mappings. It is important that you have a unique key that you can use to import, to avoid duplicate data in the primary table. Which you can use via the “primary key” checkbox for the field mapping. We suggest that you also use this for mappings that are of type “copy”. It should be noted that as of 1.8, multiple fields can be a primary key, in case you need to ensure it is a combination of fields, rather than a singular one.
To help with field mappings, there are three different types for different purposes, however, for all of these, you will want to pick the target field.
Copy
Copy is simply a copy of the data from the source field (data source) to the target field. The data won’t be transformed or altered in any way.
Lookup
Lookup allows you to look up a table to assist in populating reference fields. To do this, you will need a source field, target field, as well as detailing what table it will need to lookup and based on what field. The field you lookup doesn’t have to be display value of the lookup table, however, you will normally want it to be unique (such as an identifier or name or otherwise).
When using the lookup import transform mapping type, you can also create the record if it doesn’t exist. So in the below example, it would create company, with the company name that didn’t exist in the system.
Lookup Multiple
(New as of 1.8)
Lookup multiple allows you to look up a table to assist in populating multiple reference fields. To do this, you will need a source field, target field, as well as detailing what table it will need to lookup and based on what field. The field you lookup doesn’t have to be display value of the lookup table, however, you will normally want it to be unique (such as an identifier or name or otherwise).
When using the lookup import transform mapping type, you can also create the record if it doesn’t exist. So in the below example, it would create company, with the company name that didn’t exist in the system.
For lookup multiple fields, you can populate add multiple references at once. To do this, you simply need to add a “|” in between each reference you want to add. An example of this, lets just say we want to add Nicholas Smith to Group1, Group2 and CAB groups. Within the excel spreadsheet it would look as per below.
Transform
Transform provides the most capabilities in terms of what you can do in transform maps, as this allows you to script an value for the target field. In this example, a source field is optional, but if you set it, you get easier access to it. From here, you have access to the Table API (Server) so you can do any lookups you need to.
In addition to this you have access to “value”, which will be the value of the source field for a given row, and row, which is an object which holds any column value for a given row. Such as in the below example, we are getting the company name we used above to generate an employee name.
To set the field, you will need to set the answer variable.
In the script, you have access to the following variables:
Variable Name | Description |
|---|---|
| A key/value set of the fields and their values, with the key being the field name, and the value being the string value of the record from the import. Example: answer = `${row.FirstName} ${row.LastName}`; // Will return 'Bob Smith' if FirstName is Bob and LastName is Smith |
| The current index of the record in the transform. Starts at 1. |
| A reference to the TableRecord of the row in the Staging table. Can be useful when the script requires more information than may be available in the ‘row’ object. |
| The target record object. Transform scripts should avoid making any direct changes to this record, but it may be useful to use this record to look at other related information. |
| A log object that allows the Transform Script to record entries in the ‘Import load log’. |
| A reference to the current Import Mapping Transform record. |
| The name of the target table being referenced. |
| The String value of the related field from the Staging table. |
| This variable should be set to the value you wish to set the target field to. |
Example - mapping a value to a choice field’s option
If for example that you have a Choice field, and you are importing the label (not the key), you can import those using a Transform type with the following script.
The script queries the “AvailableValue” table which stores Choice field’s choices/options. The query is filtering against the Choice field we are mapping to and the choice label we are mapping for. The return value would be the Choice key to apply to the Choice field.
if (value) {
let availableValueRec = Table("AvailableValue")
.AND(
EQUAL("Table", mapping.Parent().Target()),
EQUAL("Field", mapping.TargetField()),
EQUAL("Value", value)
)
.query();
if (availableValueRec.next()) {
answer = availableValueRec.Key.value();
} else {
answer = "";
}
} else {
answer = "";
}
Pre-process Script
Pre-process scripts can be used to perform logic before the transforms commence. They can also be used to instruct the Transform process to skip the row but returning the value ‘false’ or ‘SKIP’ in the answer variable.
if (row.UserName == "admin") {
answer = SKIP;
}Variable Name | Description |
|---|---|
| A key/value set of the fields and their values, with the key being the field name, and the value being the string value of the record from the import. |
| The current index of the record in the transform. Starts at 1. |
| A reference to the TableRecord of the row in the Staging table. Can be useful when the script requires more information than may be available in the ‘row’ object. |
| The target record object. Transform scripts should avoid making any direct changes to this record, but it may be useful to use this record to look at other related information. |
| A log object that allows the Transform Script to record entries in the ‘Import load log’. |
| The name of the target table being referenced. |
| Set to ‘SKIP’ or ‘false’ to skip the record for the transform. |
Final import step
Once the transform map has been set up, you can proceed to the final step to use the transform map to get the data from the staging table, over to the target table. To do this, go to the “Run data load…” menu item.
From here, you need to select a transform map, staging table, and transform type. Typically you would want to select the import load type of “sync”, which will create / update any records, rather than one or the other.
Once done, press submit and a popup will come up, similar to what came up when you imported data into the data source and when completed, will show you a screen for quick access to the log or otherwise.
At this point, the data should be in the target table based on your transform mapping. If it doesn’t appear as you like, remember to look at the staging table and the logs to understand what the record looked like prior to entering the system and / or any log messages that came up.
Scheduling imports
If you want to schedule imports (although not a valid use case for the spreadsheet example provided through out this article), you can schedule the Import of data (Data Source → Staging table) and the transformation (Staging table – (via transform map) > Target Table) separately or together. This is done via the scheduled imports and import scheduled loads menu items respectively. Note that you should never have these run every minute and we typically suggest when scheduling imports as you need it (normally every day). If you need data updated to the minute, we instead suggest that you push that data from the source system via a REST API (or otherwise), rather than using this method.
Related content
Servicely Documentation