Create JSON with Hierarchy through Excel

Asked

Viewed 1,160 times

1

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

When I export json by 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"
            }
          ]
        }
      ]
    }
  }
]

can do this with excel ? someone knows the best way for me to create a file in this format through my table ?

javascript json excel

  • Surely you can force an export by writing a text file via VBA. Have you tried something similar? Anyway, it would be good if you edit your question to improve it, including details about how you export currently and even providing a link to an example spreadsheet (to prevent anyone interested in helping you from having to recreate your content for testing).

  • Has how to put the code used to generate?

  • Luiz, I put an excerpt (small) of the code. But I think I have the solution making an ETL in the javascript itself. I will post.

  • Paulo. I generated json just by saving my table in excel in json format (save as, choose format ..)

1 answer

1

They helped me by creating the ETL that turns my JSON without hierarchy into a JSON with hierarchy. It is enough that the ids of the categories and groups are equal to be grouped. Follows 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;
}

Browser other questions tagged javascript json excel

You are not signed in. Login or sign up in order to post.