Need guidance to amend excel to JSON javascript code to output one JSON per set of data

Question

I'm not from a programming background but I have a requirement in that I need to be able to convert excel data to JSON format.

I have found the below package which is exactly what I need as I can control the object types from the header names rather than hard code for each field. It also allows me to column orientate my data i.e. the header in column A.

The package can be found here --> excel-as-json

The output looks like this:

 [
  {
    "firstName": "Jihad",
    "lastName": "",
    "address": {
      "street": "12 Beaver Court",
      "city": "",
      "state": "CO",
      "zip": "81615"
    },
    "isEmployee": true,
    "phones": [
      {
        "type": "home",
        "number": "123.456.7890"
      },
      {
        "type": "work",
        "number": "098.765.4321"
      }
    ],
    "aliases": [
      "stormagedden",
      "bob"
    ]
  },
  {
    "firstName": "Marcus",
    "lastName": "Rivapoli",
    "address": {
      "street": "16 Vail Rd",
      "city": "Vail",
      "state": "CO",
      "zip": "75850"
    },
    "isEmployee": false,
    "phones": [
      {
        "type": "home",
        "number": "123.456.7891"
      },
      {
        "type": "work",
        "number": "098.765.4322"
      }
    ],
    "aliases": [
      "mac",
      "markie"
    ]
  }
]

What I need to do and what I'm struggling with is two fold:

1. I need to remove the square brackets from the beginning and end of the generated JSON
2. I need to be able to generate multiple JSONs i.e. a JSON for each column of data. So if I have data in column B and C, I need it to generate 2 JSON files with different names (ideally with the name of a specific attribute value)

The code is:

// Generated by CoffeeScript 2.2.4
(function() {
  // Create a list of json objects; 1 object per excel sheet row

  // Assume: Excel spreadsheet is a rectangle of data, where the first row is
  // object keys and remaining rows are object values and the desired json 
  // is a list of objects. Alternatively, data may be column oriented with
  // col 0 containing key names.

  // Dotted notation: Key row (0) containing firstName, lastName, address.street, 
  // address.city, address.state, address.zip would produce, per row, a doc with 
  // first and last names and an embedded doc named address, with the address.

  // Arrays: may be indexed (phones[0].number) or flat (aliases[]). Indexed
  // arrays imply a list of objects. Flat arrays imply a semicolon delimited list.

  // USE:
  //  From a shell
  //    coffee src/excel-as-json.coffee

  var BOOLTEXT, BOOLVALS, _DEFAULT_OPTIONS, _validateOptions, assign, convert, convertValue, convertValueList, excel, fs, isArray, parseKeyName, path, processFile, transpose, write,
    indexOf = [].indexOf;

  fs = require('fs');

  path = require('path');

  excel = require('excel');

  BOOLTEXT = ['true', 'false'];

  BOOLVALS = {
    'true': true,
    'false': false
  };

  isArray = function(obj) {
    return Object.prototype.toString.call(obj) === '[object Array]';
  };

  // Extract key name and array index from names[1] or names[]
  // return [keyIsList, keyName, index]
  // for names[1] return [true,  keyName,  index]
  // for names[]  return [true,  keyName,  undefined]
  // for names    return [false, keyName,  undefined]
  parseKeyName = function(key) {
    var index;
    index = key.match(/\[(\d+)\]$/);
    switch (false) {
      case !index:
        return [true, key.split('[')[0], Number(index[1])];
      case key.slice(-2) !== '[]':
        return [true, key.slice(0, -2), void 0];
      default:
        return [false, key, void 0];
    }
  };

  // Convert a list of values to a list of more native forms
  convertValueList = function(list, options) {
    var item, j, len, results;
    results = [];
    for (j = 0, len = list.length; j < len; j++) {
      item = list[j];
      results.push(convertValue(item, options));
    }
    return results;
  };

  // Convert values to native types
  // Note: all values from the excel module are text
  convertValue = function(value, options) {
    var testVal;
    // isFinite returns true for empty or blank strings, check for those first
    if (value.length === 0 || !/\S/.test(value)) {
      return value;
    } else if (isFinite(value)) {
      if (options.convertTextToNumber) {
        return Number(value);
      } else {
        return value;
      }
    } else {
      testVal = value.toLowerCase();
      if (indexOf.call(BOOLTEXT, testVal) >= 0) {
        return BOOLVALS[testVal];
      } else {
        return value;
      }
    }
  };

  // Assign a value to a dotted property key - set values on sub-objects
  assign = function(obj, key, value, options) {
    var i, index, j, keyIsList, keyName, ref, ref1;
    if (typeof key !== 'object') {
      // On first call, a key is a string. Recursed calls, a key is an array
      key = key.split('.');
    }
    // Array element accessors look like phones[0].type or aliases[]
    [keyIsList, keyName, index] = parseKeyName(key.shift());
    if (key.length) {
      if (keyIsList) {
        // if our object is already an array, ensure an object exists for this index
        if (isArray(obj[keyName])) {
          if (!obj[keyName][index]) {
            for (i = j = ref = obj[keyName].length, ref1 = index; (ref <= ref1 ? j <= ref1 : j >= ref1); i = ref <= ref1 ? ++j : --j) {
              obj[keyName].push({});
            }
          }
        } else {
          // else set this value to an array large enough to contain this index
          obj[keyName] = (function() {
            var k, ref2, results;
            results = [];
            for (i = k = 0, ref2 = index; (0 <= ref2 ? k <= ref2 : k >= ref2); i = 0 <= ref2 ? ++k : --k) {
              results.push({});
            }
            return results;
          })();
        }
        return assign(obj[keyName][index], key, value, options);
      } else {
        if (obj[keyName] == null) {
          obj[keyName] = {};
        }
        return assign(obj[keyName], key, value, options);
      }
    } else {
      if (keyIsList && (index != null)) {
        console.error(`WARNING: Unexpected key path terminal containing an indexed list for <${keyName}>`);
        console.error("WARNING: Indexed arrays indicate a list of objects and should not be the last element in a key path");
        console.error("WARNING: The last element of a key path should be a key name or flat array. E.g. alias, aliases[]");
      }
      if (keyIsList && (index == null)) {
        if (value !== '') {
          return obj[keyName] = convertValueList(value.split(';'), options);
        } else if (!options.omitEmptyFields) {
          return obj[keyName] = [];
        }
      } else {
        if (!(options.omitEmptyFields && value === '')) {
          return obj[keyName] = convertValue(value, options);
        }
      }
    }
  };

  // Transpose a 2D array
  transpose = function(matrix) {
    var i, j, ref, results, t;
    results = [];
    for (i = j = 0, ref = matrix[0].length; (0 <= ref ? j < ref : j > ref); i = 0 <= ref ? ++j : --j) {
      results.push((function() {
        var k, len, results1;
        results1 = [];
        for (k = 0, len = matrix.length; k < len; k++) {
          t = matrix[k];
          results1.push(t[i]);
        }
        return results1;
      })());
    }
    return results;
  };

  // Convert 2D array to nested objects. If row oriented data, row 0 is dotted key names.
  // Column oriented data is transposed
  convert = function(data, options) {
    var index, item, j, k, keys, len, len1, result, row, rows, value;
    if (options.isColOriented) {
      data = transpose(data);
    }
    keys = data[0];
    rows = data.slice(1);
    result = [];
    for (j = 0, len = rows.length; j < len; j++) {
      row = rows[j];
      item = [];
      for (index = k = 0, len1 = row.length; k < len1; index = ++k) {
        value = row[index];
        assign(item, keys[index], value, options);
      }
      result.push(item);
    }
    return result;
  };

  // Write JSON encoded data to file
  // call back is callback(err)
  write = function(data, dst, callback) {
    var dir;
    // Create the target directory if it does not exist
    dir = path.dirname(dst);
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir);
    }
    return fs.writeFile(dst, JSON.stringify(data, null, 2), function(err) {
      if (err) {
        return callback(`Error writing file ${dst}: ${err}`);
      } else {
        return callback(void 0);
      }
    });
  };

  // src: xlsx file that we will read sheet 0 of
  // dst: file path to write json to. If null, simply return the result
  // options: see below
  // callback(err, data): callback for completion notification

  // options:
  //   sheet:              string;  1:     numeric, 1-based index of target sheet
  //   isColOriented:      boolean: false; are objects stored in excel columns; key names in col A
  //   omitEmptyFields:    boolean: false: do not include keys with empty values in json output. empty values are stored as ''
  //                                       TODO: this is probably better named omitKeysWithEmptyValues
  //   convertTextToNumber boolean: true;  if text looks like a number, convert it to a number

  // convertExcel(src, dst) <br/>
  //   will write a row oriented xlsx sheet 1 to `dst` as JSON with no notification
  // convertExcel(src, dst, {isColOriented: true}) <br/>
  //   will write a col oriented xlsx sheet 1 to file with no notification
  // convertExcel(src, dst, {isColOriented: true}, callback) <br/>
  //   will write a col oriented xlsx to file and notify with errors and parsed data
  // convertExcel(src, null, null, callback) <br/>
  //   will parse a row oriented xslx using default options and return errors and the parsed data in the callback

  _DEFAULT_OPTIONS = {
    sheet: '1',
    isColOriented: false,
    omitEmptyFields: false,
    convertTextToNumber: false
  };

  // Ensure options sane, provide defaults as appropriate
  _validateOptions = function(options) {
    if (!options) {
      options = _DEFAULT_OPTIONS;
    } else {
      if (!options.hasOwnProperty('sheet')) {
        options.sheet = '1';
      } else {
        if (!isNaN(parseFloat(options.sheet)) && isFinite(options.sheet)) {
          if (options.sheet < 1) {
            options.sheet = '1';
          } else {
            // could be 3 or '3'; force to be '3'
            options.sheet = '' + options.sheet;
          }
        } else {
          // something bizarre like true, [Function: isNaN], etc
          options.sheet = '1';
        }
      }
      if (!options.hasOwnProperty('isColOriented')) {
        options.isColOriented = false;
      }
      if (!options.hasOwnProperty('omitEmptyFields')) {
        options.omitEmptyFields = false;
      }
      if (!options.hasOwnProperty('convertTextToNumber')) {
        options.convertTextToNumber = false;
      }
    }
    return options;
  };

  processFile = function(src, dst, options = _DEFAULT_OPTIONS, callback = void 0) {
    options = _validateOptions(options);
    if (!callback) {
      callback = function(err, data) {};
    }
    // NOTE: 'excel' does not properly bubble file not found and prints
    //       an ugly error we can't trap, so look for this common error first
    if (!fs.existsSync(src)) {
      return callback(`Cannot find src file ${src}`);
    } else {
      return excel(src, options.sheet, function(err, data) {
        var result;
        if (err) {
          return callback(`Error reading ${src}: ${err}`);
        } else {
          result = convert(data, options);
          if (dst) {
            return write(result, dst, function(err) {
              if (err) {
                return callback(err);
              } else {
                return callback(void 0, result);
              }
            });
          } else {
            return callback(void 0, result);
          }
        }
      });
    }
  };

  // This is the single expected module entry point
  exports.processFile = processFile;

  // Unsupported use
  // Exposing remaining functionality for unexpected use cases, testing, etc.
  exports.assign = assign;

  exports.convert = convert;

  exports.convertValue = convertValue;

  exports.parseKeyName = parseKeyName;

  exports._validateOptions = _validateOptions;

  exports.transpose = transpose;

}).call(this);

Answer 1

This is untested, but should get you close.

Try adding a function like this into the code (along with the other listed functions, like isArray, parseKeyName, etc.). Then in the processFile function, in the line return write(result, dst, function(err) {, replace write with writeEach.

See the comments in the code for clarification of what it's intended to do.

writeEach = function(data, dst, callback){
  // Loops through columns, calling fs.writeFile for each one
  data.forEach(function(person){ // argument name determines the name by which we will refer to the current column
    var homePhoneDigits = person.phones[0]["number"].split(".").join(""); //removes `.` from phone number 
    var personDst = homePhoneDigits + ".json"; // uses digits of home phone as destination name

    // pattern copied from 'write' function, with variable names changed
    var dir;
    dir = path.dirname(personDst);
    if (!fs.existsSync(dir)) { fs.mkdirSync(dir); }
    fs.writeFile(personDst, JSON.stringify(person, null, 2); //writes the current column to a file

  });
  return callback(void 0); // currently ignores any write errors
};