1

I am struggling the last days with an unresolved problem for me. I am stuck on this problem for long time that's why I took the decision to ask for your help.

I'm reading an Excel file and got the following array which I am now trying to convert it to a nested JSON to get accepted from the mongoDB but unfortunately I have difficulties to get there.

var arr = ["number,isnewLanguage,label,numberOfChoices,languages/language,questions/mainText/language,questions/subText/language,questions/choices/language,questions/fields/langugage",
  "1,false,label1,5,English,MAinText1,SubText1,choices1,false",
  "2,false,label2,5,English,MAinText2,SubText2,choices2,false",
  "3,true,label1,false,Italian,MainTextItalian1,SubTextItalian1,choicesItalian1,false",
  "4,true,label2,false,Italian,MAinTextItalian2,SubTextItalian2,choicesItalian2,false"
]

I would like to end up with the following structure. Here is a fiddle with my work https://jsfiddle.net/argusDob/3py6fd8m/2/

var json = [{
    number: 1,
    label1: "label1",
    numberOfChoices: 5,
    languages: [{
        language: "English",
        questions: [{
          mainText: "MainText1",
          subText: "subText1",
          choices: "choices1",
          fields: "false"
        }]
      },
      {
        language: "Italian",
        questions: [{
          mainText: "MainTextItalian1",
          subText: "subTextItlaian1",
          choices: "choicesItalian1",
          fields: "false"
        }]
      }
    ]
  },
  {
    number: 2,
    label1: "label2",
    numberOfChoices: 5,
    languages: [{
        language: "English",
        questions: [{
          mainText: "MainText2",
          subText: "subText2",
          choices: "choices2",
          fields: "false"
        }]
      },
      {
        language: "Italian",
        questions: [{
          mainText: "MainTextItalian2",
          subText: "subTextItlaian2",
          choices: "choicesItalian2",
          fields: "false"
        }]
      }
    ]
  }
]

var arr = ["number,isnewLanguage,label,numberOfChoices,languages/language,questions/mainText/language,questions/subText/language,questions/choices/language,questions/fields/langugage",
  "1,false,label1,5,English,MAinText1,SubText1,choices1,false",
  "2,false,label2,5,English,MAinText2,SubText2,choices2,false",
  "3,true,label1,false,Italian,MainTextItalian1,SubTextItalian1,choicesItalian1,false",
  "4,true,label2,false,Italian,MAinTextItalian2,SubTextItalian2,choicesItalian2,false"
]

var attrs = arr.splice(0, 1);
var test = [];

var result = arr.map(function(row, idx) {
  var myObj = {};
  var array = [];

  var therows = row.split(",").slice(1);
  console.log(therows[0]);
  therows.forEach(function(value, i) {

    var myKeys = attrs[0].split(",").slice(1)
    var theKeys = myKeys[i]

    if (theKeys.split('/').length === 1) {
      myObj[theKeys] = value;
    }

    //languages
    var theLanguagesKey = theKeys.split("/")[0]
    if (theKeys.split("/").length == 2) {
      if (!myObj["languages"]) {
        var theNewLanguage = {}
        myObj["languages"] = [];
        theNewLanguage[theKeys.split('/')[1]] = value;
        myObj["languages"].push(theNewLanguage);
      }
    }
    //questions
    if (theKeys.split("/").length == 3) {
      if (!myObj["questions"]) {
        var theNewQuestions = {}
        myObj["questions"] = [];
        theNewQuestions[theKeys.split('/')[1]] = value;
        myObj["questions"].push(theNewQuestions);
      } else {
        for (var i = 0; i < myObj["questions"].length; i++) {
          myObj["questions"][i][theKeys.split('/')[1]] = value;
        }
      }

    }

  })
  test.push(myObj)
})
console.log(test)

1
  • Is there a reason you have those strings in arr instead of defining your data more for what you need? Commented Aug 27, 2020 at 19:01

1 Answer 1

2

If you don't want to reinvent the wheel, check fast-csv. It has pretty good options for what you need. If your headers are at the first row, you can pass headers to true to convert the object that can accept by mongodb. Here is the example

Sign up to request clarification or add additional context in comments.

5 Comments

While links in answers are great, providing context and examples of how this can solve the problem is important.
Do you mean that it is impossible for me to get there with this data-format?
No. Actually you can.
@crashmstr Thanks crashmstr. I'll remember your suggestion.
"Actum Ne Agas – Do Not Do A Thing Already Done.™"

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.