Scripts for Importing

Theory:

• Mapping by Column Header

• Mapping by Script: Why the CSV column names matter

  • How to Map
  • Tips and Tricks

Scripts:

• Sample Import Script (Manager Lookup)
• Index-based Script (Lookup in another Data Object)
• Key Value Map (Checking Permitted Values & Marking)

---

Mapping by Column Header

You can use this option when:

• the column header names in the CSV file AND
• the system field names in Feeder are the same
• all possible dates follow the same pattern (=> set the date pattern field)
• all possible numbers follow the same pattern (=> set the number pattern field)

You cannot use this option when:

• a text value “true” needs to be mapped to a Boolean field
• a text value should be mapped to a Reference field (e.g. MANAGER) but another value is delivered (e.g. EMAIL) than it is stored (e.g. USERID)

---

Mapping by Script: Why the CSV column names matter

Please take a minute and analyze your import file. Do the column header names in your CSV file match the Feeder field names?

Case #1: The names are the SAME

• You could specify the date and number formats in the given fields of the import setting using mapping by column header
• Nevertheless, you want to script (see “DATE_OF_BIRTH”)
• The date pattern defines how the date values are spelled and delivered in the CSV file

var i, name, value;
for (i = 0; i < headers.length; i++) {  // go over every column header name
    name = toStr(headers[i]);   // store the current column header name in this variable
    value = toStr(columns[i]);  // store the current value of the record found in this column

    switch (name) {
            case 'DATE_OF_BIRTH': // the csv column name
            obj.set(name, Helper.parseDate(value, 'dd.MM.yyyy'));
            break;

            // name = DATE_OF_BIRTH, the system name of the Feeder field

Case #2: The names are DIFFERENT

• Every column header needs to be scripted, thus telling the Feeder what goes where
• For example the column header “dob” deviates from the field name

var i, name, value;
for (i = 0; i < headers.length; i++) {
    name = toStr(headers[i]);
    value = toStr(columns[i]);

    switch (name) {
            case 'dob': // the csv column name
            obj.set('DATE_OF_BIRTH', Helper.parseDate(value, 'dd.MM.yyyy'));
            break;

            // obj.set(): print the value into the explicit field DATE_OF_BIRTH
            // the pattern tells Feeder that it is to expect date values in the given date format

Case #3: The names contain umlauts or other special characters

• Then it is advisable to rely on index-based based scripting
• Precondition: the csv file structure is static and the column sequence is not changed!
• Please keep in mind that the index numbers start with “0”.
• +’‘ or toStr(columns[0]) type cast the value explicitly to a TEXT/STRING

obj.set("EMAIL", columns[0] +'');       // We expect the email value to be delivered in the first column
obj.set("LASTNAME", columns[1] +'');    // in the second column
obj.set("FIRSTNAME", columns[2] +'');   // in the third column
obj.set("DateOfBirth", Helper.parseDate(columns[4] +'', "dd.MM.yyyy"));

Note

When possible, please prefer mapping by column header over the index-based approach. In this way, the script remains more flexible regarding changes in the CVs data structure.

---

How to Map

Using the case-switch notation will allow mapping any headline.

The following script will:

• Map all column headers of the CSV import file explicitly that deviate from the spelling of the Feeder fields
• Will write the value delivered in the column “objectguid” into the EMPID field of Feeder
• Will import the e-mail addresses in lower case spelling
• Add a prefix (‘BEL’) to the value

 for (var i = 0; i < headers.length; i++) {
    var name = toStr(headers[i]);
    var value = toStr(columns[i]);

    switch (name) {
        case 'objectguid':
            if (value != "") {
                obj.set('EMPID', 'BEL_' + value);
            }
            break;
        case 'mail':
            obj.set('EMAIL', value.toLowerCase());
            break;
        case 'startdate':
            var date = Helper.parseDate(value, 'dd.MM.yyyy');
            if (date != null) {
                obj.set('HIREDATE', date);
            }
            break;
        default:
            obj.set(name, value);
            break;
    }
}

Background/ Use Cases:

• Take employee IDs from an active directory and add a country code for making these unique in a global context
• Import all e-mail addresses in lower case spelling

Example:

• Locally unique (SAP) employee ID: 1234
• Globally unique employee ID includes a prefix: BEL_1234.

---

Tips and Tricks

• Variables in the import script only apply to the respective data record and are not global (e.g. var country = “de”;)
• Although a value is delivered, you may delete it and import no/ an empty value writing: obj.set(“FIRSTNAME”,null); or obj.set(“FIRSTNAME”,’‘);

Important

If an import setting uses mapping by script in combination with getIncrementalID(objType, attribute, start) function, the global variable isTestImport should be used to query the test case. Otherwise IDs from the current numerical series are wasted and ID gaps result.

if (!isTestImport) {
    // do something if this is not a test import
}

---

Sample Import Script (Manager Lookup)

Important

When mapping by script, applicable date and number patterns have to be scripted too! The dedicated fields in the import configuration will have no effect.

The following full-fledged script presumes that the names of the column headers in the CSV import file match the spelling of the Feeder system fields. The script will:

• Translate true/false TEXT values of the import file into real BOOLEANs
• Look up an employee’s GLOBAL_ID in Feeder using his or her EMAIL address
• Throw an individual error message that will appear in the ImportFailure download file

    var i, name, value;
    for (i = 0; i < headers.length; i++) {

        name = toStr(headers[i]);
        value = toStr(columns[i]);

        switch (name) {

            case 'DATE_OF_BIRTH':
            case 'ENTRY_DATE':
            case 'TERMINATION_DATE':
            case 'TRANSFER_DATE':
                obj.set(name, Helper.parseDate(value, 'dd.MM.yyyy'));
                break;
            case 'IS_MANAGER':
            case 'HAS_NON_LOCAL_MANAGER':
            case 'HOME_OFFICE':
                obj.set(name, value == 'yes' ? true : false);
                break;
            case 'BASE_SALARY':
            case 'FTE':
            case 'MONTHLY_WORKING':
            case 'TOTAL_CASH':
                obj.set(name, Helper.parseNumber(value, '#,##0.#/D,/G.'));
                break;
            case 'LINE_MANAGER':
                if (value) {
                    var mgr = Helper.getByKey('SF_User', 'EMAIL', value);
                    if (mgr) {
                        obj.set(name, mgr.get('GLOBAL_ID'));
                    } else {
                        throw 'Manager with key ' + value + ' does not exist.';
                    }
                }
                break;
            default:
                obj.set(name, value);
        }
    }


var fname = toStr(obj.get('FIRSTNAME'));
// do not convert the Java Date into a JavaScript Date, cause the Helper is used later
var bday = obj.get('DATE_OF_BIRTH');
if (fname && bday) {
    obj.set('ImportID', fname + "_" + Helper.formatDate(bday,'yyyyMMdd'));
}

Comments:

1. Fall-through-statements:

• Using this abbreviated notation, limits the extent and recurring operations need to be written only once.
• E.g. all dates handled in the case statements will conform with the “dd.MM.yyyy” pattern.

Normal Notation

case 'TERMINATION_DATE':
     obj.set(name, Helper.parseDate(value, 'dd.MM.yyyy'));
     break;
case 'TRANSFER_DATE':
    obj.set(name, Helper.parseDate(value, 'dd.MM.yyyy'));
    break;

Fall-Through-Notation

case 'TERMINATION_DATE':
case 'TRANSFER_DATE':
   obj.set(name, Helper.parseDate(value, 'dd.MM.yyyy'));
   break;

2. Mapping data type:

• Converse a “true”/ “false” TEXT value into a true BOOLEAN by comparison:
• obj.set(name, value == ‘true’ ? true : false);

3. Ensuring number pattern:

• When importing values for attributes of data type NUMBER can be formatted by using:
• obj.set(name, Helper.parseNumber(value, ‘#,##0.#/D,/G.’));
• The pattern must always be #,##0.# whereas the separators can be changed
• /D, specifies the decimal separator, here a comma.
• /G. specifies the group separator, here a dot.

4. Retrieving GLOBAL_ID:

• As GLOBAL_IDs are generated by the Pentos Feeder, they are not yet available at import.

• When a pseudo unique attribute like an EMAIL address will be delivered, the adequate employee entry can be retrieved by calling Helper.getByKey(arg1,arg2,arg3).

  • arg1 specifies the data object concerned,
  • arg2 specifies the (available) attribute and
  • arg3 specifies the searched value or entry.

• In case that a record was found, the record’s “GLOBAL_ID” can be extracted by calling the get() method and be saved in the LINE_MANAGER fields

5. Throwing error messages:

• An individual error message can be generated by writing throw ‘hello’;.
• This message will show, if a record (employee) could not be imported

Hint

Consolidating employee data from multiple systems may require scripting a consistent ImportID for future updates!

---

Index-based Script (Lookup in another Data Object)

The following script realizes index-based mapping of headers. It is important to know the precise order of the column headers in the import file in order to address them via array[index]! Assuming the first column header is EMAIL followed by LASTNAME, the indexing would be:

• column[0] -> EMAIL
• column[1] -> LASTNAME etc.

Goal & Setup

• Translate country ISOs to the full country names
• Look up the country names which are stored in another data object (external referencing)

Index-based Script

var s = toStr(columns[0]);
if (s) {
        throw("Missing EMAIL");
}
obj.set("EMAIL", columns[0] +'');
obj.set("LastName", columns[1] +'');
obj.set("FirstName", columns[2] +'');

s = columns[3];
if (s == "Herr") {
        s = "M";
} else if (s == "Frau") {
        s = "F";
}

obj.set("Gender", s);

s = columns[4];
obj.set("DateOfBirth", Helper.parseDate(s, "dd/MM/yyyy"));

s = columns[5];
var reference = Helper.getByKey("MAP_Country", "ISO", s);

// look up the value s in the field ISO in the other object MAP_Country
if (reference != null) {              // if a value and thus a record was found
        var value = reference.get("Name");// get the value in Name
        obj.set("Nationality", value);    // and save it to your object's field Nationality
}

s = columns[7];
if (!s || s == "") {
        var g = obj.get("Gender");
        if (g == "M") {
                s = "Mr.";
        } else if (g == "F") {
                s = "Ms.";
        }
} else if (s == "Dr.") {
        s = "PhD / Dr.";
}
obj.set("Title", s);
/*...*/

Implemented SF_User Object (System Configuration)

Implemented Country Object (Data View)

Further Explanation

A second object in the Pentos Feeder contains the mapping of ISO to (country) Name. Calling Helper.getByKey(MAP_Country,s); looks up the translation within the object. If the value of s (in Helper.getByKey(“MAP_Country”, “ISO”, s)) is AU at import, then the function will return Argentina.

The trailing instruction obj.set(‘Nationality’, value); then assigns this name to the Nationality field.

Additional

This code is to demonstrate the flexibility of the getByKey() function. Please note that the code presents a code snippet. At some positions, an abbreviated notation is used. For example:

• if (this[i] == search) return true; is short for
• if (this[i] == search){ return true;} thus the curly braces can be left out

Hint

Prefer column header-based scripting (over index-based) for greater flexibility!

---

Key Value Map (Checking Permitted Values & Marking)

Goals and Explanation

The following code defines a key value map which maps keys to values when called. In other words, this map translates codes (keys) to terms (values) and is placed at the beginning of your import script.

var keyMapDepartment = {
    '10012' : 'Quality',
    '10013' : 'Quality',
    '10022' : 'Sales',
    '10021' : 'Product Marketing'
};

The consequent code snippet will mark imported records that are later potentially subject for deletion. This is done by setting the value of an additional Boolean check attribute to true, whereupon this attribute needs to be configured in Feeder before the import (here: TEST_DATA).

obj.set('TEST_DATA', true);

In this way, these records can be filtered and found by a simple query like TEST_DATA equals true when defining Simple Criteria.

Full Import Script

//key value map
var keyMapDepartment = {
    '10012' : 'Quality',
    '10013' : 'Quality',
    '10022' : 'Sales',
    '10021' : 'Product Marketing'
};

for (var i = 0; i < headers.length; i++) {

    var key = toStr(headers[i]);
    if (key) {
        var col = toStr(columns[i]);
        switch (key) {
            case 'Startdate':
                var date = Helper.parseDate(col, 'dd.MM.yyyy');
                if (date != null) {
                    obj.set('HIREDATE', date);
                }
                break;
            case 'Subfunction':

                // look-up the col-key in the map
                var val = keyMapDepartment[col];

                // if no key matches val will be null
                // otherwise get the value e.g. 'Quality' or 'Sales'
                if (val) {
                    obj.set('DEPARTMENT', val);
                }
                break;
        default:
            obj.set(key, col);
            break;
        }
    }
}
// Marking import records
obj.set('TEST_DATA', true);

Hint

If the MAP becomes quite lengthy, it might be a better idea to implement a dedicated data mapping object (as seen in the previous section “MAP_Country”)