How to group excel file rows in Node.js

Question

I have an excel file. And I need to group its rows with some rules.

As you see in excel sheet, there are two groups. Each group's question begins with 'Q' and a number like 'Q1, Q2, Q3....'

• A column first row is question
• B column first row is style
• C column first row is season
• if there is Blank tag, it has to be empty array.
• Below parts are detail.

I want to create a json format like

const data = [
    [],
    {
        "question": "this is example question.",
        "style": "table",
        "thead": "Gender Identity",
        "season": "",
        "detail": [{
                "answer": "Male",
                "percent": "50%",
                "response": "5"
            },
            {
                "answer": "Female",
                "percent": "50%",
                "response": "5"
            },
            {
                "answer": "Non-binary",
                "percent": "0%",
                "response": "0"
            },
            {
                "answer": "Trans*",
                "percent": "0%",
                "response": "0"
            },
            {
                "answer": "Prefer not to say",
                "percent": "0%",
                "response": "0"
            },
            {
                "answer": "Self describe (please specify)",
                "percent": "0%",
                "response": "0"
            }
        ]
    },
    {
        "question": "example question 2",
        "style": "table",
        "thead": "Age",
        "season": "",
        "group": "",
        "detail": [{
                "answer": "50-59 years old",
                "percent": "0%",
                "response": "0"
            },
            {
                "answer": "60-69 years old",
                "percent": "0%",
                "response": "0"
            },
            {
                "answer": "70-79 years old",
                "percent": "30%",
                "response": "3"
            },
            {
                "answer": "80-89 years old",
                "percent": "50%",
                "response": "5"
            },
            {
                "answer": "90+ years old",
                "percent": "20%",
                "response": "2"
            }
        ]
    }
]

I tried xlsx library and read xlsx file. But it gives me multiple array but not an appropriate one. For importing and reading data, I used

const filename = path.join(__dirname, `./tempXLSX/dump.xlsx`)

const readOpts = {
  cellText: false,
  cellDates: true
};

const jsonOpts = {
  header: 1,
  defval: '',
  blankrows: true,
  raw: false,
}

const workbook = XLSX.readFile(filename, readOpts);
const worksheet = workbook.Sheets['Sheet'];
const isXLSXFiletoJSON = XLSX.utils.sheet_to_json(worksheet, jsonOpts)

Answer 1

you need to extract the data you need yourself.

You could add various indicators to know where you are, and when you need to add what. For example, add question indicator to detect start of the question and new data object, blank row check, skip next row and stats/total row, and finish data object when you reach new question, reset it etc.

Try this:

const filename = path.join(__dirname, `./tempXLSX/dump.xlsx`)

const readOpts = {
    cellText: false,
    cellDates: true
};

const jsonOpts = {
    header: 1,
    defval: '',
    blankrows: true,
    raw: false,
}


const workbook = XLSX.readFile(filename, readOpts);
const worksheet = workbook.Sheets['Sheet'];
const isXLSXFiletoJSON = XLSX.utils.sheet_to_json(worksheet, jsonOpts)


const data = [];

// question, indicates a new object
const questionIndicator = /^q\d\.\s/i;

// or whatever you'll do with group..
let group = 1;

// your data object
let obj = {
    "question": "",
    "style": "",
    "thead": "",
    "season": "",
    "group": group,
    "detail": []
};

// stats/totals row after question row, skip it or whatever..
let prevQ = false;

// detect blank, empty row to add empty array
let prevEmtpy = false;

// check manually if blank is at the beginning and add it
if (Object.keys(worksheet)[1] !== 'A1') {
    data.push([]);
}

isXLSXFiletoJSON.map((row, i) => {


    // blank row
    if (!row[0] && !row[1] && !row[2]) {

        // add empty
        if (!prevEmtpy) {

            // add question
            data.push(obj);

            group++;

            obj = {
                "question": "",
                "style": "",
                "thead": "",
                "season": "",
                "group": group,
                "detail": []
            };

            data.push([]);
        }
        prevEmtpy = true;

    // stats/totals row, skip
    } else if (!row[0] && row[1] && row[2]) {

        // do something with totals/stats

        prevEmtpy = false;

    // question
    } else if (questionIndicator.test(row[0])) {

        // add question if blank didn't, and if it's populated
        if (i>0 && !prevEmtpy) {

            data.push(obj);

            group++;

            obj = {
                "question": "",
                "style": "",
                "thead": "",
                "season": "",
                "group": group,
                "detail": []
            };
        }

        // question values
        obj.question = row[0].replace(questionIndicator, '');
        obj.style = row[1];
        obj.thead = row[2];

        prevQ = true;
        prevEmtpy = false;

    // add details
    } else {

        // skip next row after question - "Answer Choices"
        // add details if it's not
        if (!prevQ) {

            obj.detail.push({
                "answer": row[0],
                "percent": row[1],
                "response": row[2]
            });
        }

        prevQ = false;
        prevEmtpy = false;
    }

    // done, add last question
    if (i + 1 === isXLSXFiletoJSON.length) {
        data.push(obj);
    }

});


console.log(JSON.stringify(data, null, ' '));