Create JSON with Hierarchy through Excel

Question

Create JSON with Hierarchy through Excel

Navigation

#1 by (1 votes)

1

I have a table with a tree of products. Category, Group and Subgroup.

When I export json to excel, the file exits without hierarchy: As in 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"
  },

I need to generate the file this way:

    [
  {
    "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"
            }
          ]
        }
      ]
    }
  }
]

Do you want to do this with excel? How do I create a file in this format through my table?

javascript json excel

asked by anonymous 03.02.2016 / 01:37

1 answer

How to use a collapsible without an id for the target element? Problem with condition in While in C ++

score 1 · Answer 1

They helped me by creating the ETL that transforms my JSON without hierarchy into a JSON with hierarchy. It is enough for this that the ids of categories and groups are equal to be grouped. Follow the code in JS:

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;
}