How to convert excel to nested array - Javascript

glastiva
April 13, 2023
302 views
0 votes
2 Answers

need help converting an Excel file to a nested array

to something like this

   {
    'a': [{
        'aa': [
            'aaa',
            'aab',
            'aac'
        ],
        'ab': [
            'aba',
            'abc',
            'abd'
        ]
    }],
    'b': [{
        'ba': [
            'baa'
        ],
        'bb': [
            'bba',
            'bbb'
        ]
    }]
  }

trying to convert a large amount of data to this format, thank you

Answers

You can export your data to CSV and transform it to the object in a loop:

const csv = `a,aa,aaa
  ,,aab
  ,,aac
  ,ab,aba
  ,,abc
  ,,abd
  b,ba,baa
  ,bb,bba
  ,,bbb`;

const rows = csv.split('n').map(row => row.split(',').map(col => col.trim()));

const obj = {};
let currentKey = null;

rows.forEach(([k, subKey, value]) => {
    if (k !== "") {
        currentKey = k;
        obj[currentKey] = obj[currentKey] || [];
    }

    if (subKey !== "") {
        const o = { [subKey]: [value] };
        obj[currentKey].push(o);
    } else {
        const lastSubObj = obj[currentKey][obj[currentKey].length - 1];
        const lastSubKey = Object.keys(lastSubObj)[0];
        lastSubObj[lastSubKey].push(value);
    }
});


console.log(obj);

This is a form of refinement @protob answer. Just use library xlsx to convert Excel to a 2d array, then use some logic to convert a 2d array into a nested JSON form.

document.querySelector('input').addEventListener('change', function () {
  var reader=new FileReader();
  reader.onload= function () {
    var arrayBuffer=this.result,
      array=new Uint8Array(arrayBuffer),
      binaryString=String.fromCharCode.apply(null, array);
    var workbook=XLSX.read(binaryString, {
      type: "binary"
    });
    var first_sheet_name=workbook.SheetNames[0];
    var worksheet=workbook.Sheets[first_sheet_name];
    let data=XLSX.utils.sheet_to_json(worksheet, {
      raw: true,
      header: 1
    });
    data = data.map(d => Array.from(d, e => e??''));
    const obj={};
    let currentKey=null;

    data.forEach(([k, subKey, value]) => {
      if (k!=="") {
        currentKey=k;
        obj[currentKey]=obj[currentKey]||[];
      }

      if (subKey!=="") {
        const o={ [subKey]: [value] };
        obj[currentKey].push(o);
      } else {
        const lastSubObj=obj[currentKey][obj[currentKey].length-1];
        const lastSubKey=Object.keys(lastSubObj)[0];
        lastSubObj[lastSubKey].push(value);
      }
    });
    console.log(obj);
  }
  reader.readAsArrayBuffer(this.files[0]);
}, false);

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<input type="file" id="data" accept=".xlsx" />

Please signup or login to give your own answer.

Click here to cancel reply.

How to convert excel to nested array – Javascript

Answers