Download
October 28, 2020
4 mins read

Converting CSV To JSON Using JavaScript

JSON is a lightweight data-interchange format that’s used to store numbers and collections of key-value pairs. It can be used to represent object hierarchies, as well as arrays and scalar values. JSON is often used for serializing objects for storage or transmission, such as in JavaScript’s response to the fetch API method. In Node.js, it can be parsed with the built-in JSON module from the “standard library” with json = require('json').

When working with data, it’s not always the case that you can just open up Microsoft Excel and start writing. You will often need to find a way to convert things like a CSV file into data structures that your programming language understands. In this article, we’ll take a look at how to do exactly that with JavaScript and why it’s important in the process of making your program work.

What are the Benefits of JSON?

JSON is a human-readable data format that’s commonly used for transmitting information over a network. It’s also a lightweight data exchange format and is easier to parse than XML. It’s also easier to maintain because it doesn’t require all the same tagging as XML. JSON is object oriented, so it can represent complex objects and arrays in a very elegant way. Another big advantage of JSON is that it doesn’t require commas or newlines to delimit values which makes it much easier to read and write.

Why do you need to convert CSV to JSON?

CSV and JSON are two ways of storing data. CSV is built for Excel but does not store complex data structures like JSON does. If you need to store complex data in Excel, then you will need to convert your CSV to JSON so that it can be added to a spreadsheet.

Importance of Data Conversion

At some point, you’ll probably have to deal with data that is stored in one format and needs to be used in another. Sometimes this is easy and you can export the data from the first format into a second. However, sometimes the conversion process can be quite complicated. Converting comma-separated values (CSV) to JavaScript Object Notation (JSON) is one example of a complicated conversion process.

How to Convert CSV to JSON

Converting CSV To JSON

Converting CSV to JSON is a difficult task that often requires many lines of code. However, it’s somewhat easier to handle this task with JavaScript and the JSON.parse() method. Oftentimes when converting data from one type of spreadsheet to another there is missing data or duplicate entries which can lead to imprecise data conversion. The JSON format tends to be more precise when it comes to transcription because all values must be numeric and cannot be empty or null.

Let’s create a script to convert CSV to json. I will demonstrate how to do this with and without using a library.

Open up a blank text editor and let’s write a script to help us accomplish this task. Let’s say we have a simple json object like so:

[
  {
    "name": "Order 1",
    "color": "Red",
    "size_range": {
      "max": "XS",
      "min": "XL"
    }
  },
  {
    "name": "Order 2",
    "color": "Green",
    "sizes": ["medium"],
    "owner": {
      "name": "Yoshi",
      "address": {
        "line1": "312 Mountain Dr",
        "suburb": "San Mateo"
      }
    }
  },
  {
    "name": "Order 3",
    "color": "Blue",
    "sizes": [
      "small",
      "medium",
      "large"
    ]
  }
]

We would like to map the json structure to csv. We can do so by first creating a function to iterate over the fields of the object and return the object as a comma delimited string.


function object2CSVRow(dataObject) {
  var data_container = [];
  for (var obj in dataObject) {
    var innerValue = typeof dataObject[obj] == 'undefined' ? '' : dataObject[obj].toString();
    var result = innerValue.replace(/"/g, '""');
    result = '"' + result + '"';
    data_container.push(result);
  }
  return data_container.join(',') + '\r\n';
}

function findbystring(o, s) {
  s = s.replace(/\[(\w+)\]/g, '.$1'); // convert indexes to properties
  s = s.replace(/^\./, ''); // strip a leading dot
  var a = s.split('.');
  for (var i = 0, n = a.length; i < n; ++i) {
    var k = a[i];
    if (k in o) {
      o = o[k];
    } else {
      return;
    }
  }
  return o;
}

function push_unique(arr, item) {
  if (item != "" && !arr.includes(item))
    arr.push(item);
}

function get_labels(name, item, labels) {
  if (typeof item == 'object') {
    for (var index in item) {
      thisname = ""
      if (name != "") thisname = name + ".";
      thisname += index;
      get_labels(thisname, item[index], labels);
    }
  } else {
    push_unique(labels, name);
  }
}

function JSON2CSV(objArray) {
  var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
  var str = '';

  var labels = [];

  for (var i = 0; i < array.length; i++)
    get_labels("", array[i], labels);

  str += object2CSVRow(labels);
  
  for (var i = 0; i < array.length; i++) {
    var line = [];
    for (var label in labels) {
      line.push(findbystring(array[i], labels[label]));
    }
    str += object2CSVRow(line);
  }

  return str;
}

function main(input) {
  // Convert Object to JSON
  var jsonObject = JSON.parse(input.text, null, 2);
  input.text = JSON2CSV(jsonObject);
}

While the above code accomplishes the task of converting a csv to json we can utilize a much better library such as Papaparse to help use accomplish this task.

const pp = require('@polypad/papaparse.js');

function main(input) {
    const { data } = pp.parse(input.text, { header:false });
    input.text = JSON.stringify(data, null, 2);
}

Conclusion

Converting JSON to CSV using JavaScript was a somewhat tedious process but, not too bad considering we can accomplish the task with less than 100 lines of code.

convert csv json