CSV to JSON

Transforms CSV data into JSON.

This Step kind produces a single output file, which has three modes:

Example input:

A,B,C
1,hello,world
2,"foo,bar",baz

Example output (the first line is a header row):

[
  {
    "A": "1",
    "B": "hello",
    "C": "world"
  },
  {
    "A": "2",
    "B": "foo,bar",
    "C": "baz"
  },
]

Example output when there is no header row (advanced option):

[
  ["A", "B", "C"],
  ["1", "hello", "world"],
  ["2", "foo,bar", "baz"]
]

Download links found in the file

Example: Fetching a URL based on a client-provided ID

Instead of sending the raw invitations in a feed-file, the company has a web-service that provides the file when requested, and the request must include a unique ID to identify the file to provide, something like this:

https://example.com/feedFile?id=20160303.12

When the file is ready to download, the company sends the ID value in a file or email.

20160303.12

To make this work, a preprocessor has two steps:

1. A Regular expression find/replace whose properties are:
   • Regular expression — (.+ )(match the entire contents of the request and put it in a group).
   • Replacement — https://example.com/feedFile=?id=$1 (insert the ID into the URL)
2. A Download links found in the file, with Minimum and Maximum set to 1.

Employee Manager Hierarchy

Converts an input file containing a list of employees and their respective direct managers in RFC-4180, comma-separated (","), CSV format into the structure compatible with Medallia Experience Cloud's Org Hierarchy Auto Importers. The output is a CSV that mirrors the input file and adds additional fields that contain the full hierarchical walk from the employee up to the top-most manager in their reporting chain. This allows for the creation of a full Org Hierarchy where the source HRIS cannot provide the needed hierarchy details itself.

The preprocessor expects two columns to be identified in the input file: the employee ID and the manager ID; by default they are called "employee_id" and "manager_id", but these are configurable to match the requirements of each client.

The preprocessor uses these columns to create the hierarchy in a "tree-walk"-like manner. To explain this process, see the example input file:

employee_id,employee_name,manager_id,manager_name		
1,Mary Jackson,3,John Patterson		
2,Brian Weaver,3,John Patterson		
3,John Patterson,4,Laura Cunningham		
4,Laura Cunningham,6,Mike Smith		
5,Nick Ackerman,6,Mike Smith		
6,Mike Smith,7,Gale Johns		
7,Gale Johns,,		

The file represents the following hierarchy:

For a given employee record in the input file (for example: "Mary Jackson"), the preprocessor uses the value in the manager_id column for that employee record to find the manager's employee record in the input file, iteratively, until the employee record no longer contains a value found in the file. This hierarchy represents a tree of employees and provides the reporting hierarchy details needed.

Each record in the output file should be a superset of the corresponding record in the input file, where aggregated columns are added to the existing record. This ensures scalability with extraneous data in the input file.

By default, the preprocessor aggregates the manager_id hierarchy into a manager_id_hierarchy value. To scale with arbitrarily deep hierarchies, this value is stored as a JSON array, ordered from root (first) to direct manager (last).

The preprocessor operates on the manager records and allows aggregating input column values into a similar JSON array structure. This is done using a JSON key/value mapping as shown below, where the key is the input column on the manager's employee record and the value is the aggregated output column name:

{
  "employee_name": "manager_names",
  "employee_title": "manager_titles"
}

An example output file from the preprocessor is shown below:

employee_id,employee_name,manager_id,manager_name,manager_id_hierarchy,manager_names										
1,Mary Jackson,3,John Patterson,"[""7"",""6"",""4"",""3""]","[""Gale Johns"",""Mike Smith"",""Laura Cunningham"",""John Patterson""]"										
2,Brian Weaver,3,John Patterson,"[""7"",""6"",""4"",""3""]","[""Gale Johns"",""Mike Smith"",""Laura Cunningham"",""John Patterson""]"										
3,John Patterson,4,Laura Cunningham,"[""7"",""6"",""4""]","[""Gale Johns"",""Mike Smith"",""Laura Cunningham""]"										
4,Laura Cunningham,6,Mike Smith,"[""7"",""6""]","[""Gale Johns"",""Mike Smith""]"										
5,Nick Ackerman,6,Mike Smith,"[""7"",""6""]","[""Gale Johns"",""Mike Smith""]"										
6,Mike Smith,7,Gale Johns,"[""7""]","[""Gale Johns""]"										
7,Gale Johns,,,[],[]										

The Auto Importer will be responsible for doing a JavaScript transformation of the following type in order to access the data required:

if (!value) {
    // No parsable data found
    return null;
}
 
var hierarchyData = JSON.parse(value);
if (!hierarchyData.length) {
    // No hierarchy information found for this employee,
    // likely due to the employee being the root of the
    // tree (ex: CEO)
    return null;
}
 
// The value "3" below is an example only, showing the
// 3th manager (from the CEO, defined as "1") in the tree.
// Update this value for each JavaScript transformation
// and/or make it a function argument if using a helper
// function.
 
var desiredManagerLevel = 3;
 
if (desiredManagerLevel > hierarchyData.length) {
    // This employee does not have anyone at this level
    return null;
}
 
var managerAtLevel = hierarchyData[desiredManagerLevel - 1];
 
if (!managerAtLevel) {
    // No information was found for this level
    return null;
}
 
return managerAtLevel;

When you select the Error on detached subtrees validation, the preprocessor stops processing the file if it finds more than one tree. This typically occurs when a manager no longer works at the company and the input file uses the manager’s employee ID rather than a position ID. The following example shows this in action:

employee_id,employee_name,manager_id,manager_name		
1,Mary Jackson,3,John Patterson		
2,Brian Weaver,3,John Patterson		
3,John Patterson,4,Laura Cunningham		
5,Nick Ackerman,6,Mike Smith		
6,Mike Smith,7,Gale Johns		
7,Gale Johns,,

Note that “Laura Cunningham” was removed as an employee declaration. This results in a hierarchy that ends with “John Patterson” being detached from the rest of the tree headed by “Gale Johns”. Visually, this looks like the following:

We recommend to always error on detached subtrees, as this keeps the Org Hierarchy as clean and proper as possible. There are legitimate cases where disabling this error check is acceptable; consult your Medallia Solution Architect or Employee Experience specialist before doing so.

JSON Transform (jq)

Transforms a JSON object into a new JSON object based on a jq filter. The importer expects the JSON to be a single object (representing a record), or an array of objects (representing a collection of records). Use this option to convert a JSON stream into a format the importer can read.

This step produces a single output file.

For an example, see Example: Constructing a jq filter for a walk-through of the steps followed to develop a jq filter.

Regular expression find/replace

Performs regular expression (regex) search and replace on the contents of the feed-file. To perform multiple regex transformations, place each definition in its on Step.

This step produces a single output file.

This step has two fields:

Regular expression

The regex search pattern. Use parenthesis to identify the groups of content to include in the output. Each group can be identified in order with the $ reference in the Replacement field.

Use the (?s) prefix to enable DOT_ALL mode to match any character, including line endings (e.g., \n). Otherwise '.' will only match non-line ending characters.

Replacement

The groups to include in the output, as defined in the search expression. The first group is $1, the second is $2, and so on.

For example, to discard the contents of the first row of input, use:

• Regular expression ((?s).*?\n(.*)) — Indicates whatever comes after the first line break gets put into the first group (.*).
• Replacement ($1) — The first group.

Use a similar trick to swap the order of the first 2 lines in a file:

• Regular expression ((?s)(.*?)\n(.*?)\n(.*)) — creates a group for the first line, a group for the second line and a group for the rest of the file.
• Replacement — Use dollar signs ($) to indicate the second line (second group) should come first, the first line second, and then the rest of the file.

Transform input character encoding to UTF8

Re-encodes the input file to the UTF-8 character set. The Input encoding field identifies the character set of the input file.

This step produces a single output file.

URL-encoded form to JSON

Unencodes HTML form data and converts it to JSON. The input must be an HTTP POST form whose content type is application/x-www-form-urlencoded.

The data are a series of name/value pairs, separated by ampersands (&). Further:

• In each pair, an equal sign (=) separates the name and the value.

• Non-alphanumeric characters are specified by a percent sign (%) prepended to a two-digit hex value of the ASCII character, such as %2E for a period (.).

• A space character may be a specified by a plus symbol (+) or as %20, its ASCII value.

For example, this URL-encoded data:

name=Brian%20Gilmour&email=bg1%40example%2Ecom

is converted to:

{
  "name": "Brian Gilmour",
  "email": "bg1@example.com"
}

XSLT 2.0 transform

Performs an XSLT 2.0 transform over a (valid) XML file.

This step produces a single output file.

Running jq from the command line

The jq tool takes a stream of input, processes it, and sends the filtered result to the output stream. To feed a file into the into the stream, use cat to read a file and then pipe (|) the stream into jq. The jq command takes a filter as an argument. So, for example to read a JSON file named input.json and output the entire contents without change, use a "." filter, like this:

cat input.json | jq '.'

jq then outputs the result.

On the Auto Importers > Preprocessors screen, you can enter the filter on multiple lines to be human-readable. That doesn't work on a command line. To have a filter that is human readable with newlines, define the filter in a text file (such as filter.jq) and include the filter file with the -f command-line argument, like this:

cat input.json | jq -f filter.jq 

The rest of this topic uses the command-line filter to simplify the example.

Creating the simple input file for the example

This example uses this simple JSON stream to demonstrate how to build the filter.

{ "size":2, "data" : [ {"id":1, "a":"foo", "sub":{"b":"hello"}}, {"id":2, "a":"bar"} ] }

Or, when reformatted for legibility:

{
  "size":2,
  "data" : 
    [ 
      {
        "id":1,
        "a":"foo",
        "sub":{
          "b":"hello"
        }
      },
      {
        "id":2,
        "a":"bar"
      }
    ]
}

This input JSON has some summary information ("size"), and then an array of two records ("data"). Additionally, the first record in the array has a sub object ("sub").

The Auto Importer expects the JSON to be a single object (representing a record), or an array of objects (representing a collection of records). In this example the Auto Importer is expecting records to have three fields named: id, a, and b. So the jq filter needs to extract the array and reformat the first record.

If you are doing this example on a command-line, you can create this JSON file by streaming it to a file named input.json, like this:

echo '{ "size":2, "data" : [ {"id":1, "a":"foo", "sub":{"b":"hello"}}, {"id":2, "a":"bar"} ] }' > input.json

Creating the filter

To get the object into a format the Auto Importer expects — records containing columns id, a, and b — first use the dot character to pass the entire file without change, like this:

cat input.json | jq '.'

If you run this command, you'll notice that jq reformatted the input:

{
  "data": [
    {
      "sub": {
        "b": "hello"
      },
      "a": "foo",
      "id": 1
    },
    {
      "a": "bar",
      "id": 2
    }
  ],
  "size": 2
}

Because the importer doesn't need the size field, the filter needs to extract the data array, like this:

cat input.json | jq '.data[]' 

then the output is a collection of two JSON entities rather than a single JSON entity:

{
  "sub": {
    "b": "hello"
  },
  "a": "foo",
  "id": 1
}
{
  "a": "bar",
  "id": 2
}

But when there are multiple records, they need to be in an array, which is done by enclosing the records in square brackets:

cat input.json | jq '[.data[]]'

which gives us

[
  {
    "sub": {
      "b": "hello"
    },
    "a": "foo",
    "id": 1
  },
  {
    "a": "bar",
    "id": 2
  }
]

The output JSON now has an array of objects. But, the b data element is not at the correct level: it is still trapped within the sub object. jq can extract data from nested data if it exists. In this command, the value of the b field comes from the sub field, when the field exists:

cat input.json | jq '[.data[]|{id,a,"b":(.sub.b)}]'

which creates data that the Auto Importer can accept:

[
  {
    "b": "hello",
    "a": "foo",
    "id": 1
  },
  {
    "b": null,
    "a": "bar",
    "id": 2
  }
]

Did you notice the "trick"? The filter includes a pipe character (|) to pass the output of the .data[] filter as input to another filter, which in turn explicitly creates JSON objects by wrapping extracted data within braces. See the jq Manual for a complete discussion of the jq options and filters.

As you can see, jq is terse yet powerful. That combination makes the learning curve a little bit steep and requires a lot of trial and error.