Connecting to JSON and Excel files

The sample Javascript code provided below builds on the simple connector that uses CSV data from a single table. The differences in the code are highlighted with a lighter gray background. Two sets of sample code are provided. The first Javascript file imports data in JSON format, and the second imports an Excel file. Each set of sample code is followed by an explanation of important parts of the code.

Importing a JSON file

To support JSON file import in a connector that uses raw data files, you need to change the code in the fetchTable function so that the JSON data is stringified.

Copy

(function(){
  // define global variable for file type  var fileType;
  // mstr is a global object from mstrgdc-2.0.js, which represents the data connector framework
  var myConnector = mstr.createDataConnector();
  // Connector must define fetchTable function
  myConnector.fetchTable = function(table, params, doneCallback) {       
    // params represents information sent by connector to MSTR  at interactive phase
    var mstrObj = JSON.parse(params);
    var file = mstrObj.connectionData.file;
    var url = file;       
    // Retrieve file type from params
    fileType = mstrObj.fileType;
    $.get(url, function(resp) {
      var data = resp;
      if (fileType == "JSON"){
        // resp is a JSON object. You need to transform it to a string.
        >data = JSON.stringify(resp);
      }
      table.appendRawData(data);
      doneCallback(table);
    });
  };
  // validateDataConnector does the validation check of the connector
  mstr.validateDataConnector(myConnector);
});

The mstrObj.fileType indicates the file format, which MUST be set before the mstr.submit() function is called.
data = JSON.stringify(resp); stringifies the response when the file type is JSON.
table.appendRawData only accepts data as string. You need to stringify resp if the file is JSON format.

Copy

// Create event listener for when the user submits the form
$(document).ready(function() {
  $("#submitButton").click(function() {
    var content = $("#file").val();
    mstr.connectionName = "RawDataFiles";
    // connectionData is a JSON object. Connector can put any information here.
    mstr.connectionData = {};
    mstr.connectionData.file = content;
    // Get file type from extension
    fileType = content.split('.').pop().toUpperCase();
    if(fileType == "JSON"){
      mstr.fileType = "JSON";
    }
    // MUST define tableList field. Can import multiple tables in one connection
    mstr.tableList = [];
    mstr.tableList.push({tableName: "RawDataFiles"});
    // Inform that interactive phase is finished and send information to MSTR
    window.mstr.submit();
  });
});

For JSON files, the event listener changes the file extension to upper case and then sets the MicroStrategy file type.

Importing an Excel file

In addition to setting the file type to Excel, there are other steps to take to support importing Excel files. For example, you need to encode the file binary before you send it to MicroStrategy.

Copy

(function(){
  var fileType; //define global variable
  // mstr is a global object from mstrgdc-2.0.js, which represents the data connector framework
  var myConnector = mstr.createDataConnector();
  // Connector must define fetchTable function.
  myConnector.fetchTable = function(table, params, doneCallback) {
    // params represents the information sent by connector to MSTR at interactive phase
    var mstrObj = JSON.parse(params);
    var file = mstrObj.connectionData.file;
    var url = file;
    // Retrieve file type from params
    fileType = mstrObj.fileType;
    if(fileType == "EXCEL"){
      getFileBlob(url, function (fileObject) {
        var reader = new FileReader();
        reader.onload = function(event) {
          var contents = new Uint8Array(reader.result);
          var data = uintToString(contents);
          table.appendRawData(btoa(data));
          doneCallback(table);
        };
        reader.readAsArrayBuffer(fileObject);
      });
    }
    else{
      $.get(url, function(resp) {
        var data = resp;
        if (fileType == "JSON"){
          data = JSON.stringify(resp);
        }
        table.appendRawData(data);
        doneCallback(table);
      });
    }
  };
  // validateDataConnector does the validation check of the connector
  mstr.validateDataConnector(myConnector);
  // Create event listeners for when the user submits the form
  $(document).ready(function() {
    $("#submitButton").click(function() {
      var content = $("#file").val();
      mstr.connectionName = "RawDataFiles";
      // connectionData is a JSON object. Connector can put any information here.
      mstr.connectionData = {};
      mstr.connectionData.file = content;
      // Get file type from extension
      fileType = content.split('.').pop().toUpperCase();
      if(fileType == "JSON"){
        mstr.fileType = "JSON";
      }
      else if(fileType == "XLS" || fileType == "XLSX" ){
        mstr.fileType = "EXCEL";
      }
      // MUST define tableList field. Can import multiple tables in one connection.
      mstr.tableList = [];
      mstr.tableList.push({tableName: "RawDataFiles"});
      // Inform that interactive phase is finished and send information to MSTR.
      window.mstr.submit();
    });
  });
  // EXCEL reader helper functions
  function getFileBlob(url, cb) {
    var xhr = new XMLHttpRequest();
    xhr.open("GET", url);
    xhr.responseType = "blob";
    xhr.addEventListener('load', function() {
      cb(xhr.response);
    });
    xhr.send();
  };
  function uintToString(uintArray) {
    var out = "";
    var len, i;
    len = uintArray.length;
    for(i = 0; i < len; i++){
      c = uintArray[i];
      out += String.fromCharCode(c);
    }
    return out;
  }
})();

Both xls and xlsx Excel types are supported.
getFileBlob and uintToString are helper functions to read content from an Excel file.

Test and run the connector

To test and run this connector, you put a JSON or Excel file in the same folder as the HTML file. Once the data connector has been registered, you enter the file name (with the file extension) on the HTML page to import that file.