Adding Data Profiles

Last Updated: Aug 21, 2020
  1. At the top of the Data Profiles panel click the (emoji) action button

  2. Populate the fields as desired

  3. Next define the source data that is being imported and any transforms and validations you require by loading the Schema tab and following the steps below:

    1. To add a new source field click the (emoji) action button on the Schema tab and the new field will appear within the tab

    2. To edit the new source field click the (emoji) button to the right of the field. A pop-up modal will appear allowing you to change the source field Name, Field Type and Delimited Item (source field order within the source file) values. After modifying any of these values you can click Update to save the changes

    3. To delete a source field click the  button and then click the  button that appears. Source fields that have not yet been saved to the data profile will be removed immediately, source fields that are configured on the profile will be shown in orange text and removed after you click the Save button

    Adding Import Transforms

    1. If you wish to modify (transform) the source data when it is imported load the Schema tab and for the desired field item click the (emoji) above the 'Transforms' text in relation to the imported data you wish to transform. The  will be displayed if a transform is currently setup for the source data fieldNote: additional transform options will be available later when configuring transforms on the Visual Map tab

    2. A pop-up modal will appear. Click the (emoji) action button on the modal to add a new transform

    3. On the model under the Add Transforms heading you will need to specify the Transform Type. The options are:

      • Case Conversion: converts the imported text into the desired case. The options are Lower, Title (capitalize the first character of each separate word) and Upper

      • Condition: allows you to transform the value if it meets a specific condition, e.g. if the source data 'Is Null' import the substitute value of '0'

      • Date Conversion: converts the imported data value into an alternate date format that you specify. E.g. if the imported date value is in a MM/DD/YYYY format you can convert the date to be imported with the day first instead of the month (DD/MM/YYYY)

      • Regex: permits you to transform source data using JavaScript compatible regular expressions

      • Set Value: allows you to change the imported value to a static value you specify

      • Substitute: allows you to import a specific value based on the value of the target field in the system. E.g. if the target value to be imported is 'U.S.' substitute it with 'USA' instead. If the target value does not match then no substitution will occur

      • Time - AM/PM to 24 Hour: converts an imported 12 hour time value into a 24 hour time format

      • Trim: removes whitespace until a character is reached. Whitespace can be trimmed from the left, right or both the left and the right of the imported value

    Adding Validations

    1. Validations ensure imported data conforms to a standard you define. On the Fields tab click the  (emoji)icon above the 'Validate' text in relation to the inbound data you wish to validate

    2. A pop-up modal will appear. To add a validation click in the Validation Type list in the bottom left of the pop-up modal. The validation options are:

      • In List: validate the imported value against a list of values you supply to create the list:

        1. In the Validation Type field select In List

        2. Click the + New button

        3. In the upper right of the Add Validations section click the (emoji) action button.

        4. Enter the first list value in the Item field that appears

        5. To add additional list items click the (emoji) again

        6. Once the list is built click the Add button in the lower right

      • Regex: validate the imported data against a regular expression. To add the regex:

        1. In the Validation Type field select Regex

        2. Click the + New button

        3. In Expression text box enter the regex

        4. Click the Add button

  4. The next step is to map the imported data to the target fields in the system. The mapping must follow the order of the fields in the import file. To map the fields do the following:

    1. Click the Visual Map tab

    2. To the right of the tab click the (emoji) action button

    3. On the pop-up modal that loads specify the Source Field from the import fileNote: you do not need to have a source field if you are automatically setting a target value via a transform. E.g. you can have a literal transform that writes the value 0 to multiple target fields with no source field specified

    4. If you wish to further transform the data you can do so here. After you select the transform option you desire and click Add to close the modal you will be able to configure the transform you have added by clicking on it on the Visual Map tab. The transform options are:

      • Combine: combine allows you to merge multiple fields from the imported file into one or more target fields in the system. See Configuring a Combine below for more information

      • Formula: Formulas are mathematical expressions that can modify the source data. Source field values can be referenced in the formula numerically using curly braces, for example {1} + {2} would add the first source value to the second source value and write that value to the target field. See Configuring a Formula below for more information

      • Literal: writes the value you specify to the target field. Literals can be used to provide data that is not present in the import file. E.g. with a literal transform you can write the value 'true' to a 'isActive' target field without the need to include a source field on the Visual Map

      • Match: matches the imported value to the target field data type. For instance, if you were importing a value called 'activated' to a StatusID target field the value 'activated' would be compared against the list of StatusID's in the system. If activated is found with an ID value of '2' then '2' would be written to the target StatusID field. This option is useful in scenarios where you don't know what the targetID value is.
        Note: match transforms are case sensitive

    5. Specify the Target field to import the data to after any transforms or validations have been performed

    6. Click Add

  5. After you have mapped the imported data fields to the target fields using the (emoji) action the Visual Map will be displayed. You can further configure the transforms you have setup by clicking on buttons and objects. These options are explained below:

    • Adding source or target fields: by clicking the + below the field you can add additional source fields or target fields.

      This approach is used when you need to combine data or in instances where the source data needs to be written to multiple target fields

    • Removing Fields: you can remove a field from the map by clicking the X to the right of the field name.

      Configuring a Combine: after you have added the combine transform you can add additional source fields using the + below the existing source field on the left of the Visual Map. Once the source fields you want to combine are added click on the combine transform on the Visual Map and the Edit Transform modal will load

      The Mask inputs (source fields) will be listed which can be used in the Combine Mask field. A simple combine example would be merging together location data from multiple source fields, for instance: Country, State and City. To combine these values into a forward slash separated format you would enter a Combine Mask of {1}/{2}/{3}. In this example if you were importing a source file row that contained the country,state,city values of: USA,California,Los Angeles then the data written to the target field would be USA/California/Los Angeles

    • Configuring a Formula: after you have added the formula transform you can click on it in on the Visual Map tab to load the Edit Formula modal. The Available inputs (source fields) that are available will be listed along with the reference number you can use in the formula (i.e. {1}, {2}, etc.). A simple formula that would divide the source field value by 100 would be entered as: {1} / 100

    • Configuring a literal: after you have added the literal transform you can click on it in on the Visual Map tab to load the Edit modal. In the Literal Value field supply the value that will be written to the target field. If you wish to populate the same literal value to multiple target fields simply click the + at the bottom of the target field on the Visual Map to add additional target fields

  6. Finally verify your profile is in order by uploading a sample file using the (emoji) action button. After the file is uploaded you can verify that the source data is being red properly by clicking on the (emoji) action button. You can also verify the target data by clicking on the (emoji) action button. The target sample data displayed will include the effect of transforms, so viewing this sample output is a good way to ensure you are manipulating the inbound data as desired.