Back to all How-tos

Using Preprocessors in DataMapper

The Preprocessor feature in the DataMapper module is a fairly complex, but extremely powerful one. Basically, a Preprocessor runs on the whole data source before anything else happens – before even the file settings and boundaries are set!

So how are preprocessors useful? Well, for one they can be used to convert one file format to another, which is exactly what we will do here with a simple preprocessor that converts a JSON file into an XML file useable by the DataMapper module (that is, until we get native JSON support!).

There’s a few other things we can do, of course. For example:

  • Filter out characters or lines from the file if it is not a standard format
  • Add a new field to a CSV file (we used this to create a hash key field)
  • Re-organise fields in an XML file
  • etc

But let’s stick to converting JSON to XML. Fortunately for us, as the greatest majority of things you might want to do in Connect, there’s already a code sample out there to do this. In our case, there’s a json2xml function available in JavaScript, which we can almost use as-is in our process.

The only thing this converter doesn’t do is to create a compliant XML, more specifically it does not create the initial <xml> element necessary for the DataMapper to recognize it as a valid XML. Additionally, some JSON files may create nodes that are numerical only (such as <0 />) which is unsupported. Thankfully we can fix that easily with 2 lines of code (included within the sample below).

Creating the JSON to XML data mapping configuration

  1. Create a new data mapping configuration, selecting XML as a data source.
  2. For the initial data sample, change the file type to the right of the filename box to All files (*.*) and browse to a JSON file.
  3. Once the configuration is created, go into the Steps pane and click Preprocessor (the first step)
  4. In the Preprocessor section, click Add. Call the script json2xml.
  5. In the Preprocessor definition, open the JavaScript Editor using the bottom to the right of the text box.
  6. Copy and paste the code below in the editor then click OK.
  7. In the Prerprocessor section, add a checkmark to the left of the json2xml preprocessor, then click the Green checkmark to the right of the list of preprocessors.
  8. Go in the Settings tab, where you should now see various levels in the XML elements.
  9. Continue data mapping the XML as usual.

The Code

In current versions of Connect (up to 1.7 at least) it is not possible to use this as a pre-processor due to an issue in how incoming data files are treated. As a workaround, use the Workflow version of this file (link at the bottom).

// Modified From : http://goessner.net/download/prj/jsonxml/
function json2xml(o, tab) {
  var toXml = function(v, name, ind) {
    if(!isNaN(name[0])) { name = "node" } // Added to prevent numeric node name
    var xml = "";
    if (typeof(v) == "object") {
      var hasChild = false;
      xml += ind + "<" + name;
      for (var m in v) {
        if (m.charAt(0) == "@")
          xml += " " + m.substr(1) + "=\"" + v[m].toString() + "\"";
        else
          hasChild = true;
      }
      xml += hasChild ? ">" : "/>";
      if (hasChild) {
        for (var m in v) {
          xml += toXml(v[m], m, ind+"\t");
        }
        xml += (xml.charAt(xml.length-1)=="\n"?ind:"") + "</" + name + ">";
      }
    }
    else {
       xml += ind + "<" + name + ">" + v.toString() +  "</" + name + ">";
    }
    return xml;
  }, xml="";
  nodes = {"nodes": {}};
  if(o instanceof Array) {
     for (var n in o) nodes.nodes[n] = o[n];
     o = nodes;
  }
  for (var m in o)
    xml += toXml(o[m], m, "");
  return tab ? xml.replace(/\t/g, tab) : xml.replace(/\t|\n/g, "");
}

var inFile = openTextReader(data.filename);
var outFile = openTextWriter(data.filename+'.tmp');

var jsondata = "";

while ((sLine=inFile.readLine())!=null) {
  jsondata+=sLine.replace(/&/g,"&amp;");
}

var xmldata = json2xml(JSON.parse(jsondata));
outFile.write('<?xml version="1.0" encoding="ISO-8859-1"?><root>'+xmldata+"</root>");
inFile.close();
outFile.close();
copyFile(data.filename+'.tmp',data.filename);

And with that, you are good to go with using this XML file in your data mapping!

Doing it in Workflow

If, for some reason, you would like to do the conversion in the Workflow automation tool, add a Run Script task to your process, set it to the JavaScript language, and use the following modified code in the script. This converts the current data file from JSON to XML. The code is not exactly the same for opening and writing to files because it is the WSH environment, no a Java-based environment.

Leave a Reply

Your email address will not be published. Required fields are marked *