Home

SiteMap

JS tip - Convert Excel data into JSON format

← Prev

Not using an API or a library. Sharing a simple JS code that will convert Excel data into JSON format. It has limitation. However, its useful in some cases. All you have to do is, copy Excel data and paste it in a box and the code will automatically convert the data into JSON format.
<body>
  <!--Copy and paste Excel data in the textarea.-->
  <p>
    <textarea onkeyup='writeText(this)' id='taYourExcelData'></textarea>
  </p>

  <div id='json'></div>       <!--Data converted to JSON format-->
</body>

<script>
  let t;

  let writeText = (ele) => {
    t = ele.value.replace(/\n\r?/g, '<br />');
    document.getElementById('json').innerHTML = '';
    const arr = t.split('<br />');

    let arr_h;
    const el = document.getElementById('json');    // Show JSON here.

    el.innerHTML = '[<br />';
    for (let iCnt = 0; iCnt <= arr.length - 1; iCnt++) {
      if (iCnt > 0) {
        arr_h = arr[0].split('\t').reduce((a, v, i) => ({ 
          ...a, [v]: arr[iCnt].split('\t')[i]
        }), {});

        if (arr_h[0] != "") {
          // Convert plain text into JSON format.
          el.innerHTML = 
            el.innerHTML + JSON.stringify(arr_h, null, '<br />');
        }
      }
    }

    // final formating.
    el.innerHTML = el.innerHTML.split('}{').join('},<br />{') + '<br />]';
  }
</script>
Try it

👉 Now, check whether the JSON (that you just converted from Excel) is valid or not.

Happy coding. 🙂

← Previous