Create JSON with through Excel Hierarchy

Question

I have a table with a tree products. Category, group and subgroup.

When you export the json the Excel file out without hierarchy: As this example:

        [
  {
    "id_cat": 4,
    "desc cat": "Acessorios para Veiculos",
    "id_gr": 1,
    "desc gr": "Acessorios Nautica",
    "id_sub": 15,
    "desc sub": "Bombas"
  },
  {
    "id_cat": 4,
    "desc cat": "Acessorios para Veiculos",
    "id_gr": 1,
    "desc gr": "Acessorios Nautica",
    "id_sub": 16,
    "desc sub": "Cabos"
  },
  {
    "id_cat": 4,
    "desc cat": "Acessorios para Veiculos",
    "id_gr": 1,
    "desc gr": "Acessorios Nautica",
    "id_sub": 17,
    "desc sub": "Helices"
  },

Must generate a file like this:

 [
  {
    "category": {
      "id": 4,
      "name": "Acessorios para Veiculos",
      "group": [
        {
          "id": 1,
          "name": "Acessorios Nautica",
          "subgroup": [
            {
              "id": 15,
              "name": "Bombas"
            },
            {
              "id": 16,
              "name": "Cabos"
            },
            {
              "id": 17,
              "name": "Helices"
            }
          ]
        },
        {
          "id": 2,
          "name": "Acessorios de Carros",
          "subgroup": [
            {
              "id": 26,
              "name": "Exterior"
            },
            {
              "id": 27,
              "name": "Interior"
            }
          ]
        }
      ]
    }
  }
]

the to do this with Excel? Anyone know the best way I can create a file that through my table format?

Answer 1

You can use JS object as a Hash table to keep track of the category id, group id and subgroup id.

I came up with a quick implementation below (assuming that all items in the initial array have id_cat, id_gr and id_sub value), have a look at this jsFiddle

function grouping(items) {
    var catHash = {},
        catList = [],
        i = 0;
            
    for (i = 0; i < items.length; i++) {
        var hash = catHash[items[i]["id_cat"]] || {};
        hash.groupHash = hash.groupHash || {};
        
        var groupHash = hash.groupHash[items[i]["id_gr"]] || {};
        groupHash.subgroupHash = groupHash.subgroupHash || {};
        
        var subgroupHash = groupHash.subgroupHash[items[i]["id_sub"]] || {},
            cat = hash.category || {},
            group = groupHash.group || {},
            subgroup = subgroupHash.subgroup || {};
                
        if (!cat.id) {
            cat.id = items[i]["id_cat"];
            catList.push(cat);
            hash.category = cat;
            catHash[cat.id] = hash;
        }
        if (!cat.name) {
            cat.name = items[i]["desc cat"];
        }
        if (!cat.group) {
            cat.group = [];
        }

        if (!group.id) {
            group.id = items[i]["id_gr"];
            cat.group.push(group);
            
            groupHash.group = group;
            hash.groupHash[group.id] = groupHash;
        }
        if (!group.name) {
            group.name = items[i]["desc gr"];
        }
        if (!group.subgroup) {
            group.subgroup = [];
        }
        
        if (!subgroup.id) {
            subgroup.id = items[i]["id_sub"];
            group.subgroup.push(subgroup);
            
            subgroupHash.subgroup = subgroup;
            groupHash.subgroupHash[subgroup.id] = subgroupHash;
        }
        if (!subgroup.name) {
            subgroup.name = items[i]["desc sub"];
        }
    }
    return catList;
}