JSON to CSV Converter in JavaScript

Dirk Ecker (decker)

Oct 15, 2020·4 min read

or a simple example to use functional programming in JavaScript

Today I wanted to paste my JSON into a Spreadsheet like Excel, but that was a mess. It could not get along with it and hence it is no fun to do this by hand, I implemented a small tool to do this for me.

The input was this test.json — almost, I have created an object of it:

{
  'id': '10.1234/vvsyzs72ws.4',
  'type': 'dois',
  'attributes': {
    'doi': '10.1234/vvsyzs72ws.4',
    'identifiers': [],
    'creators': [
      {
        'name': 'Staging, UnaElsStaging',
        'nameType': 'Personal',
        'givenName': 'UnaElsStaging',
        'familyName': 'Staging',
        'affiliation': [],
        'nameIdentifiers': [
          {
            'schemeUri': 'https://orcid.org',
            'nameIdentifier': 'https://orcid.org/0000-0003-4355-8664',
            'nameIdentifierScheme': 'ORCID'
          }
        ]
      }
    ],
    'titles': [
      {
        'title': 'EmbargoDatasetBlockedByADMIN HW_ZSVPN_MNSEC_19/24/26Aug2020(EDT1.3)'
      }
    ],
    'publisher': 'Mendeley',
    'container': {},
    'publicationYear': 2020,
    'subjects': [
      {
        'subject': 'Alpha Sarcin'
      },

The interesting thing is, that the data is nested and not only on one level. What I want in result was this test.xlsx:

It shows the nested structure of the JSON file using more columns. I have done this by creating this JSON to CSV Converter that creates me a CSV that I can import very easy.

to-csv.js:

const isArray = value => value instanceof Array
const isObject = value => value != null && value instanceof Object

const traverse = (data, callback, depth = 0) => {
  if (!data) {
    return
  }
  Object.entries(data).forEach(([key, value]) => {
    if (isArray(value)) {
      callback(key, value, depth)
      traverse(value[0], callback, depth + 1)
    } else if (isObject(value)) {
      callback(key, value, depth)
      traverse(value, callback, depth + 1)
    } else {
      callback(key, value, depth)
    }
  })
}

const SEPARATOR = ';'
let maxDepth = 0
traverse(data, (key, value, depth) => (maxDepth = maxDepth < depth ? depth : maxDepth))

traverse(data, (key, value, depth) => { -- define data to use it
  const praefix = SEPARATOR.repeat(depth)
  const indent = SEPARATOR.repeat(maxDepth - depth + 1)
  const valueToLog = isObject(value) || isArray(value) ? null : value
  console.log(`${praefix}${key}${indent}${valueToLog || ''}${SEPARATOR}${value?.constructor.name}`)
})

It is called with node to-csv.js > test.csv and gives me the

test.csv:

id;;;;10.1234/vvsyzs72ws.4;String
type;;;;dois;String
attributes;;;;;Object
;doi;;;10.1234/vvsyzs72ws.4;String
;identifiers;;;;Array
;creators;;;;Array
;;name;;Staging, UnaElsStaging;String
;;nameType;;Personal;String
;;givenName;;UnaElsStaging;String
;;familyName;;Staging;String
;;affiliation;;;Array
;;nameIdentifiers;;;Array
;;;schemeUri;https://orcid.org;String
;;;nameIdentifier;https://orcid.org/0000-0003-4355-8664;String
;;;nameIdentifierScheme;ORCID;String
;titles;;;;Array
;;title;;EmbargoDatasetBlockedByADMIN HW_ZSVPN_MNSEC_19/24/26Aug2020(EDT1.3);String
;publisher;;;Mendeley;String
;container;;;;Object
;publicationYear;;;2020;Number
;subjects;;;;Array
;;subject;;Alpha Sarcin;String
;contributors;;;;Array
;dates;;;;Array
;;date;;2020-08-25;String
;;dateType;;Available;String
;language;;;;undefined
;types;;;;Object

That can be imported into almost each spreadsheet, I think.

The programm lacks the support of an input file, you have to define a const data variable with the json to use it — see the source above.

So how it is implemented?

First I have to calculcate the max horizontal depth of the spreadsheet, how many columns it needs. So I have to traverse through the whole document and count the depth.

The next step is to create the data for the CSV. Again I have to traverse the data structure. So it makes sense to create a method that can traverse a JSON structure using recursion. I named it traverse.

const traverse = (data, callback, depth = 0) => {
  if (!data) {
    return
  }
  Object.entries(data).forEach(([key, value]) => {
    if (isArray(value)) {
      callback(key, value, depth)
      traverse(value[0], callback, depth + 1)
    } else if (isObject(value)) {
      callback(key, value, depth)
      traverse(value, callback, depth + 1)
    } else {
      callback(key, value, depth)
    }
  })
}

It takes the data and a callback to be called during traversion.

For the calculation of the depth the callback is simply used to compare the actual depth to the actual max depth.

let maxDepth = 0
traverse(data, (key, value, depth) => (maxDepth = maxDepth < depth ? depth : maxDepth))

After this, I have calculated the max depth for the data.

It remains the next step, creating the CSV output. Therefore I put another function into the call of traverse.

traverse(data, (key, value, depth) => { -- define data to use it
  const praefix = SEPARATOR.repeat(depth)
  const indent = SEPARATOR.repeat(maxDepth - depth + 1)
  const valueToLog = isObject(value) || isArray(value) ? null : value
  console.log(`${praefix}${key}${indent}${valueToLog || ''}${SEPARATOR}${value?.constructor.name}`)
})

This function calculates the semicolons to prefix and the indent semicolons for the current key and value. And only primitive values should be written in the value column, Array and Object will be ignored.

And that’s it.

See you