In this article, you'll learn how to create a Node CLI tool to convert a JSON file into a valid CSV file.

JSON has become the most popular way to pass data around on the modern web, with almost universal support between APIs and server applications.

However, the dominant data format in spreadsheet applications like Excel and Google Sheets is CSV, as this format is the tersest and easiest for users to understand and create.

A common function that backend apps will need to perform, therefore, is the conversion of JSON to CSV. In this tutorial, we'll create a CLI script to convert an input JSON file to an output CSV file.

If you don't need to customize the conversion process, it's quicker to use a third-party package like json-2-csv. At the end of the tutorial, I'll also show you how to use this instead of a custom converter.

To get the most out of this article you should be familiar with the basics of Node.js, JavaScript, and async/await.

You'll need a version of Node.js that includes 

 and also have NPM installed. I recommend 

Let's now get started making a Node script that we can use to convert a JSON file to CSV from the command line. First, we'll create the source code directory, 

 so that we're ready to add some NPM packages.

$ mkdir json-to-csv
$ cd json-to-csv
$ npm init -y

Let's now create am example JSON file that we can work with called 

. I've created a simple data schema with three properties: name, email, and date of birth.

/*input.json*/
[
  {
    "name": "James Holden",
    "email": "james@rocinante.com",
    "dob": "2370-07-23"
  },
  {
    "name": "Amos Burton",
    "email": "amos@rocinante.com",
    "dob": "2368-12-12"
  },
  {
    "name": "Naomi Nagata",
    "email": "naomi@rocinante.com",
    "dob": "2372-03-11"
  },
  {
    "name": "Alex,Camal",
    "email": "alex@rocinante.com",
    "dob": "2366-03-12"
  }
]

It'd be very handy to allow our utility to take in a file name input and file name output so that we can use it from the CLI. Here's the command we should be able to use from within the

, let's require the yargs package and assign the 

 property to a variable. This variable will effectively hold any CLI inputs captured by yargs.

Nameless CLI arguments will be in an array at the 

. Let's grab these and assign them to obviously-named variables 

We'll also console log the values now to check they're working how we expect:

const argv = require('yargs').argv;

const inputFileName = argv._[0];
const outputFileName = argv._[1];

console.log(inputFileName, outputFileName);

$ node ./ input.json output.csv
# input,json, output.csv

For file operations, we're going to use the 

 package of Node.js. This will make handling files a lot easier than using the standard callbacks pattern.

Let's do a destructure assignment to grab the 

methods which are all we'll need in this project.

...

const { readFile, writeFile } = require('fs').promises;

Let's now write a function that will parse the JSON file. Since file reading is an asynchronous process, let's make it an 

. This method will take the file name as an argument.

 which will load the raw file. Next, we'll parse the contents as JSON and return it.

 block, we should console log the error so the user knows what's gone wrong. We should also exit the script by calling 

 which indicates to the shell that the process failed.

...

async function parseJSONFile (fileName) {
  try {
    const file = await readFile(fileName);
    reutrn JSON.parse(file);
  } catch (err) {
    console.log(err);
    process.exit(1);
  }
}

We'll now write a method to convert the JavaScript array returned from the 

 to a CSV-compatible format.  First, we're going to extract the 

 of each object in the array, discarding the keys. To do this, we'll 

 a new array where each element is itself an array of the object values.

 method to insert a header row to the top of the data. We'll pass to this the object

 of any one of the objects (since we assume they all have the same keys for the sake of simplicity).

The last step is to convert the JavaScript object to CSV-compatible string. It's as simple as using the 

 method and joining each object with a newline (

...

function arrayToCSV (data) {
  csv = data.map(row => Object.values(row));
  csv.unshift(Object.keys(data[0]));
  return csv.join('\n');
}
/*
const data = [ 
	{ a:"1", b:"2" },
    { a:"3", b:"4" }, 
    { a:"5", b:"6" }, 
];
arrayToCSV(data);

output:
a,b
1,2
3,4
5,6
*/

We're not quite finished - CSV fields should be surrounded by quotes to escape any commas from within the string. There's an easy way to do this: 

...

function arrayToCSV (data) {
  csv = data.map(row => Object.values(row));
  csv.unshift(Object.keys(data[0]));
  return `"${csv.join('"\n"').replace(/,/g, '","')}"`;
}
/*
const data = [ 
	{ a:"1", b:"2" },
    { a:"3", b:"4" }, 
    { a:"5", b:"6" }, 
];
arrayToCSV(data);

output:
"a","b"
"1","2"
"3","4"
"5","6"
*/

It's fairly trivial now to write our CSV file now that we have a CSV string - we just need to call 

. Just like in the parse method we'll include a 

...

async function writeCSV (fileName, data) {
  try {
  	await writeFile(fileName, data, 'utf8'); 
  } catch (err) {
    console.log(err);
    process.exit(1);
  }
}

 to the bottom of the file. Within this function, we'll call each of our methods in the sequence we wrote them, passing data from one to the next. At the end, let's console log a message so the user knows the conversion process worked.

Let's now try and run the CLI command using our example JSON file:

$ node ./ input.json output.csv
# Successfully converted output.csv!

It works! Here's what the output looks like:

"name","email","dob"
"James Holden","james@rocinante.com","2370-07-23"
"Amos Burton","amos@rocinante.com","2368-12-12"
"Naomi Nagata","naomi@rocinante.com","2372-03-11"
"Alex,Camal","alex@rocinante.com","2366-03-12"

There's a fatal flaw in our script: if any CSV fields contain commas they will be made into separate fields during the conversion. Note in the below example what happens to the second field of the last row which includes a comma:

To fix this, we'll need to escape any commas before the data gets passed to the 

 method, then unescape them afterward. We're going to create two methods to do this: 

 to create a new array where comma values are replaced by a variable 

. This token can be anything you like, so long as it doesn't occur in the CSV data. For this reason, I recommend something random like 

 method, we'll replace the token with the commas and restore the original content.

Here's how we'll modify our run function to incorporate these new methods:

(async () => {
  const data = await parseJSONFile(inputFileName);
  const escapeToken = '~~~~';
  const escapedData = escapeCommas(data, escapeToken);
  const escapedCSV = arrayToCSV(escapedData);
  const CSV = unescapeCommas(escapedCSV);
  await writeCSV(outputFileName, CSV);
  console.log(`Successfully converted ${outputFileName}!`);
})();

With that done, the convertor can now handle commas in the content.

Here's the real test of our CLI tool...can we import a converted sheet into Google Sheets? Yes, it works perfectly! Note I even put a comma in one of the fields to ensure the escape mechanism works.

While it's good to understand the underlying mechanism of CSV conversion in case we need to customize it, in most projects, we'd probably just use a package like

Not only will this save us having to create the conversion functionality ourselves, but it also has a lot of additional features we haven't included like the ability to use different schema structures and delimiters.

Let's now update our project to use this package instead. First, go ahead and install it on the command line:

Next, let's require it in our project and assign it to a variable using destructuring:

...

const { json2csvAsync } = require('json-2-csv');

...

We can now modify our run function to use this package instead of our custom 

 method. Note we no longer need to escape our content either as the package will do this for us as well.

(async () => {
  const data = await parseJSONFile(inputFileName);
  const csv = await json2csvAsync(data);
  await writeCSV(outputFileName, csv);
  console.log(`Successfully converted ${outputFileName}!`);
})();

With this change, run the CLI command again and you should get 

 the same results. The key difference is that this package will only wrap fields in double-quotes if they need escaping as this still produces a valid CSV.

name,email,dob
James Holden,james@rocinante.com,2370-07-23
Amos Burton,amos@rocinante.com,2368-12-12
Naomi Nagata,naomi@rocinante.com,2372-03-11
"Alex,Camal",alex@rocinante.com,2366-03-12

So now you've learned how to create a CLI script for converting JSON to CSV using Node.js. Here's the complete script for your reference or if you've skimmed the article:

<h1 data-node-type="titlePlaceholder" class="titlePlaceholder">How to convert JSON to CSV in Node.js</h1><div data-node-type="summary" class="summary-block">In this article, you'll learn how to create a Node CLI tool to convert a JSON file into a valid CSV file.</div><p>JSON has become the most popular way to pass data around on the modern web, with almost universal support between APIs and server applications.</p><p>However, the dominant data format in spreadsheet applications like Excel and Google Sheets is CSV, as this format is the tersest and easiest for users to understand and create.</p><p>A common function that backend apps will need to perform, therefore, is the conversion of JSON to CSV. In this tutorial, we'll create a CLI script to convert an input JSON file to an output CSV file.</p><h1><a name="what-youll-learn" id="what-youll-learn">What you'll learn</a></h1><ul><li><p>How to create a CLI script with Node.js</p></li><li><p>How to load a JSON file from the filesystem</p></li><li><p>Writing a method to convert a JSON structure to CSV</p></li><li><p>Writing the CSV file to the filesystem</p></li></ul><p>If you don't need to customize the conversion process, it's quicker to use a third-party package like json-2-csv. At the end of the tutorial, I'll also show you how to use this instead of a custom converter.</p><h1><a name="prerequisites" id="prerequisites">Prerequisites</a></h1><p>To get the most out of this article you should be familiar with the basics of Node.js, JavaScript, and async/await.</p><p>You'll need a version of Node.js that includes <em>fs.promises</em> and supports <em>async/await</em> and also have NPM installed. I recommend <a href="https://nodejs.org/en/download/" rel="noopener noreferrer nofollow">Node v12 LTS</a> which includes NPM 6.</p><h1><a name="setting-up-the-project" id="setting-up-the-project">Setting up THE project</a></h1><p>Let's now get started making a Node script that we can use to convert a JSON file to CSV from the command line. First, we'll create the source code directory, <code>json-to-csv</code>. Change into that and run <code>npm init -y</code> so that we're ready to add some NPM packages.</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-vBuAp" id="cm-vBuAp">$ mkdir json-to-csv
$ cd json-to-csv
$ npm init -y</textarea><h1><a name="example-json-file" id="example-json-file">Example JSON file</a></h1><p>Let's now create am example JSON file that we can work with called <em>input.json</em>. I've created a simple data schema with three properties: name, email, and date of birth.</p><textarea data-iscodemirror="true" data-mode="application/json" data-uniqueid="cm-Pcmkc" id="cm-Pcmkc">/*input.json*/
[
  {
    "name": "James Holden",
    "email": "james@rocinante.com",
    "dob": "2370-07-23"
  },
  {
    "name": "Amos Burton",
    "email": "amos@rocinante.com",
    "dob": "2368-12-12"
  },
  {
    "name": "Naomi Nagata",
    "email": "naomi@rocinante.com",
    "dob": "2372-03-11"
  },
  {
    "name": "Alex,Camal",
    "email": "alex@rocinante.com",
    "dob": "2366-03-12"
  }
]</textarea><h1><a name="creating-the-cli-with-yargs" id="creating-the-cli-with-yargs">Creating the CLI with yargs</a></h1><p>It'd be very handy to allow our utility to take in a file name input and file name output so that we can use it from the CLI. Here's the command we should be able to use from within the<em> json-to-csv</em> directory:</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-efQco" id="cm-efQco">$ node ./ input.json output.csv</textarea><p>So let's now create an <em>index.js</em> file and install the <a href="https://www.google.com/search?q=github+yargs&amp;oq=github+yargs&amp;aqs=chrome..69i57j69i60l4j69i65j69i60l2.1736j0j7&amp;sourceid=chrome&amp;ie=UTF-8" rel="noopener noreferrer nofollow">yargs</a> package to handle CLI input:</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-iwLtt" id="cm-iwLtt">$ touch index.js
$ npm i -S yargs</textarea><p>Inside <em>index.js</em>, let's require the yargs package and assign the <code>argv</code> property to a variable. This variable will effectively hold any CLI inputs captured by yargs.</p><p>Nameless CLI arguments will be in an array at the <code>_</code> property of <code>argv</code>. Let's grab these and assign them to obviously-named variables <code>inputFileName</code> and <code>outputFileName</code>. </p><p>We'll also console log the values now to check they're working how we expect:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-WgZga" id="cm-WgZga">const argv = require('yargs').argv;

const inputFileName = argv._[0];
const outputFileName = argv._[1];

console.log(inputFileName, outputFileName);</textarea><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-eAzyg" id="cm-eAzyg">$ node ./ input.json output.csv
# input,json, output.csv</textarea><h1><a name="reading-and-parsing-a-json-file" id="reading-and-parsing-a-json-file">Reading and parsing A JSON file</a></h1><p>For file operations, we're going to use the <code>promises</code> API of the <code>fs</code> package of Node.js. This will make handling files a lot easier than using the standard callbacks pattern.</p><p>Let's do a destructure assignment to grab the <code>readFile</code> and <code>writeFile </code>methods which are all we'll need in this project.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-m0dd1" id="cm-m0dd1">...

const { readFile, writeFile } = require('fs').promises;</textarea><p>Let's now write a function that will parse the JSON file. Since file reading is an asynchronous process, let's make it an <code>async</code> function and name it <code>parseJSONFile</code>. This method will take the file name as an argument.</p><p>In the method body, add a <code>try</code>/<code>catch</code> block. In the <code>try</code>, we'll create a variable <code>file</code> and assign to this <code>await readFile(fileName)</code> which will load the raw file. Next, we'll parse the contents as JSON and return it.</p><p>In the <code>catch</code> block, we should console log the error so the user knows what's gone wrong. We should also exit the script by calling <code>process.exit(1)</code> which indicates to the shell that the process failed.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-5Uq1m" id="cm-5Uq1m">...

async function parseJSONFile (fileName) {
  try {
    const file = await readFile(fileName);
    reutrn JSON.parse(file);
  } catch (err) {
    console.log(err);
    process.exit(1);
  }
}</textarea><h1><a name="converting-json-data-to-csv-format" id="converting-json-data-to-csv-format">Converting JSON data to CSV format</a></h1><p>We'll now write a method to convert the JavaScript array returned from the <code>parseJSONFile</code> to a CSV-compatible format.  First, we're going to extract the <em>values</em> of each object in the array, discarding the keys. To do this, we'll <code>map</code> a new array where each element is itself an array of the object values.</p><p>Next, we'll use the array <code>unshift</code> method to insert a header row to the top of the data. We'll pass to this the object<em> keys</em> of any one of the objects (since we assume they all have the same keys for the sake of simplicity).</p><p>The last step is to convert the JavaScript object to CSV-compatible string. It's as simple as using the <code>join</code> method and joining each object with a newline (<code>\n</code>).</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-OTQAu" id="cm-OTQAu">...

function arrayToCSV (data) {
  csv = data.map(row =&gt; Object.values(row));
  csv.unshift(Object.keys(data[0]));
  return csv.join('\n');
}
/*
const data = [ 
	{ a:"1", b:"2" },
    { a:"3", b:"4" }, 
    { a:"5", b:"6" }, 
];
arrayToCSV(data);

output:
a,b
1,2
3,4
5,6
*/</textarea><h2><a name="adding-quotes-around-fields" id="adding-quotes-around-fields">Adding quotes around fields</a></h2><p>We're not quite finished - CSV fields should be surrounded by quotes to escape any commas from within the string. There's an easy way to do this: </p><ol><li><p>Print the string within a string template with surrounding quotes <code>"${csv.join('\n')"</code></p></li><li><p> Put at the beginning and end of each new line <code>.join('"\n"\)</code></p></li><li><p>Replace each comma in the string with a quote surrounded comma by chaining a regex <code>.replace(/,/g, '","')</code></p></li></ol><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-RZTjT" id="cm-RZTjT">...

function arrayToCSV (data) {
  csv = data.map(row =&gt; Object.values(row));
  csv.unshift(Object.keys(data[0]));
  return `"${csv.join('"\n"').replace(/,/g, '","')}"`;
}
/*
const data = [ 
	{ a:"1", b:"2" },
    { a:"3", b:"4" }, 
    { a:"5", b:"6" }, 
];
arrayToCSV(data);

output:
"a","b"
"1","2"
"3","4"
"5","6"
*/</textarea><h1><a name="writing-csv-file" id="writing-csv-file">Writing CSV file</a></h1><p>It's fairly trivial now to write our CSV file now that we have a CSV string - we just need to call <code>writeFile</code> from an async method <code>writeCSV</code>. Just like in the parse method we'll include a <code>try</code>/<code>catch</code> block and exit on error.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-jQxCt" id="cm-jQxCt">...

async function writeCSV (fileName, data) {
  try {
  	await writeFile(fileName, data, 'utf8'); 
  } catch (err) {
    console.log(err);
    process.exit(1);
  }
}</textarea><h1><a name="putting-it-all-together" id="putting-it-all-together">Putting it all together</a></h1><p>To run our CSV converter we'll add an <a href="https://developer.mozilla.org/en-US/docs/Glossary/IIFE" rel="noopener noreferrer nofollow">IIFE</a> to the bottom of the file. Within this function, we'll call each of our methods in the sequence we wrote them, passing data from one to the next. At the end, let's console log a message so the user knows the conversion process worked.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-Q2ASV" id="cm-Q2ASV">const argv = require('yargs').argv;

const inputFileName = argv._[0];
const outputFileName = argv._[1];

const { readFile, writeFile } = require('fs').promises;

async function parseJSONFile (fileName) {...}

function arrayToCSV (data) {...}

async function writeCSV (fileName, data) {...}

(async () =&gt; {
  	const data = await parseJSONFile(inputFileName);
  	const CSV = arrayToCSV(data);
  	await writeCSV(outputFileName, CSV);
	console.log(`Successfully converted ${outputFileName}!`);
})();</textarea><p>Let's now try and run the CLI command using our example JSON file:</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-RZTjT" id="cm-RZTjT">$ node ./ input.json output.csv
# Successfully converted output.csv!</textarea><p>It works! Here's what the output looks like:</p><textarea data-iscodemirror="true" data-mode="text/html" data-uniqueid="cm-zfdiW" id="cm-zfdiW">"name","email","dob"
"James Holden","james@rocinante.com","2370-07-23"
"Amos Burton","amos@rocinante.com","2368-12-12"
"Naomi Nagata","naomi@rocinante.com","2372-03-11"
"Alex,Camal","alex@rocinante.com","2366-03-12"</textarea><h1><a name="escaping-commas" id="escaping-commas">Escaping commas</a></h1><p>There's a fatal flaw in our script: if any CSV fields contain commas they will be made into separate fields during the conversion. Note in the below example what happens to the second field of the last row which includes a comma:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-cQJYU" id="cm-cQJYU">function arrayToCSV (data) {
  csv = data.map(row =&gt; Object.values(row));
  csv.unshift(Object.keys(data[0]));
  return `"${csv.join('"\n"').replace(/,/g, '","')}"`;
}
/*
const data = [ 
	{ a:"1", b:"2" },
    { a:"3", b:"4" }, 
    { a:"5", b:"6,7" }, 
];
arrayToCSV(data);

output:
"a","b"
"1","2"
"3","4"
"5","6","7"
*/ </textarea><p>To fix this, we'll need to escape any commas before the data gets passed to the <code>arrayToCSV</code> method, then unescape them afterward. We're going to create two methods to do this: <code>escapeCommas</code> and <code>unescapeCommas</code>. </p><p>In the former, we'll use <code>map</code> to create a new array where comma values are replaced by a variable <code>token</code>. This token can be anything you like, so long as it doesn't occur in the CSV data. For this reason, I recommend something random like <code>~~~~</code> or <code>!!!!</code>.</p><p>In the <code>unescapeCommas</code> method, we'll replace the token with the commas and restore the original content.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-hcKcL" id="cm-hcKcL">function escapeCommas (data, token) {
  return data.map(row =&gt; {
    return row.map((value, index) =&gt; {
      return row[index] = value.replace(/,/g, token);
    });
  });
}

function unescapeCommas (data, token) {
  return data.replace(new RegExp(`${token}`, 'g'), ',');
}</textarea><p>Here's how we'll modify our run function to incorporate these new methods:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-MEO7d" id="cm-MEO7d">(async () =&gt; {
  const data = await parseJSONFile(inputFileName);
  const escapeToken = '~~~~';
  const escapedData = escapeCommas(data, escapeToken);
  const escapedCSV = arrayToCSV(escapedData);
  const CSV = unescapeCommas(escapedCSV);
  await writeCSV(outputFileName, CSV);
  console.log(`Successfully converted ${outputFileName}!`);
})();</textarea><p>With that done, the convertor can now handle commas in the content.</p><h1><a name="testing-in-google-sheets" id="testing-in-google-sheets">Testing in Google Sheets</a></h1><p>Here's the real test of our CLI tool...can we import a converted sheet into Google Sheets? Yes, it works perfectly! Note I even put a comma in one of the fields to ensure the escape mechanism works.</p><figure class="photo figure-position-inline"><img src="https://s3.amazonaws.com/assets.fullstack.io/n/20201004070708732_Screen%20Shot%202020-10-04%20at%206.06.37%20pm.png" attachmentid="80f51984-7d59-49de-8e81-509ad6ea3ba8" contenteditable="false"><div></div></figure><h1><a name="using-the-json-2-csv-package" id="using-the-json-2-csv-package">Using the json-2-csv package</a></h1><p>While it's good to understand the underlying mechanism of CSV conversion in case we need to customize it, in most projects, we'd probably just use a package like<em> </em><a href="https://github.com/mrodrig/json-2-csv" rel="noopener noreferrer nofollow">json-2-csv</a>.</p><p>Not only will this save us having to create the conversion functionality ourselves, but it also has a lot of additional features we haven't included like the ability to use different schema structures and delimiters.</p><p>Let's now update our project to use this package instead. First, go ahead and install it on the command line:</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-2odJh" id="cm-2odJh">$ npm i -S json-2-csv</textarea><p>Next, let's require it in our project and assign it to a variable using destructuring:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-2odJh" id="cm-2odJh">...

const { json2csvAsync } = require('json-2-csv');

...</textarea><p>We can now modify our run function to use this package instead of our custom <code>arrayToCSV</code> method. Note we no longer need to escape our content either as the package will do this for us as well.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-2odJh" id="cm-2odJh">(async () =&gt; {
  const data = await parseJSONFile(inputFileName);
  const csv = await json2csvAsync(data);
  await writeCSV(outputFileName, csv);
  console.log(`Successfully converted ${outputFileName}!`);
})();</textarea><p>With this change, run the CLI command again and you should get <em>almost</em> the same results. The key difference is that this package will only wrap fields in double-quotes if they need escaping as this still produces a valid CSV.</p><textarea data-iscodemirror="true" data-mode="text/html" data-uniqueid="cm-p06C0" id="cm-p06C0">name,email,dob
James Holden,james@rocinante.com,2370-07-23
Amos Burton,amos@rocinante.com,2368-12-12
Naomi Nagata,naomi@rocinante.com,2372-03-11
"Alex,Camal",alex@rocinante.com,2366-03-12</textarea><h1><a name="conclusion" id="conclusion">Conclusion</a></h1><p>So now you've learned how to create a CLI script for converting JSON to CSV using Node.js. Here's the complete script for your reference or if you've skimmed the article:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-2odJh" id="cm-2odJh">const argv = require('yargs').argv;
const { readFile, writeFile } = require('fs').promises;
const { json2csvAsync } = require('json-2-csv');

const inputFileName = argv._[0];
const outputFileName = argv._[1];

async function parseJSONFile (fileName) {
  try {
    const file = await readFile(fileName);
    return JSON.parse(file);
  } catch (err) {
    console.log(err);
    process.exit(1);
  }
}

async function writeCSV (fileName, data) {
  await writeFile(fileName, data, 'utf8');
}

(async () =&gt; {
  const data = await parseJSONFile(inputFileName);
  const csv = await json2csvAsync(data);
  await writeCSV(outputFileName, csv);
  console.log(`Successfully converted ${outputFileName}!`);
})();</textarea>

AUTHOR

Anthony Gore

<h1 data-node-type="titlePlaceholder" class="titlePlaceholder">How to Convert JSON to CSV in Node.js</h1><div data-node-type="summary" class="summary-block">In this article, you'll learn how to create a Node CLI tool to convert a JSON file into a valid CSV file.</div><p>JSON has become the most popular way to pass data around on the modern web, with almost universal support between APIs and server applications.</p><p>However, the dominant data format in spreadsheet applications like Excel and Google Sheets is CSV, as this format is the tersest and easiest for users to understand and create.</p><p>A common function that backend apps will need to perform, therefore, is the conversion of JSON to CSV. In this tutorial, we'll create a CLI script to convert an input JSON file to an output CSV file.</p><h1><a name="what-youll-learn" id="what-youll-learn">What you'll learn</a></h1><ul><li><p>How to create a CLI script with Node.js</p></li><li><p>How to load a JSON file from the filesystem</p></li><li><p>Writing a method to convert a JSON structure to CSV</p></li><li><p>Writing the CSV file to the filesystem</p></li></ul><p>If you don't need to customize the conversion process, it's quicker to use a third-party package like json-2-csv. At the end of the tutorial, I'll also show you how to use this instead of a custom converter.</p><h1><a name="prerequisites" id="prerequisites">Prerequisites</a></h1><p>To get the most out of this article you should be familiar with the basics of Node.js, JavaScript, and async/await.</p><p>You'll need a version of Node.js that includes <em>fs.promises</em> and supports <em>async/await</em> and also have NPM installed. I recommend <a href="https://nodejs.org/en/download/" rel="noopener noreferrer nofollow">Node v12 LTS</a> which includes NPM 6.</p><h1><a name="setting-up-the-project" id="setting-up-the-project">Setting up THE project</a></h1><p>Let's now get started making a Node script that we can use to convert a JSON file to CSV from the command line. First, we'll create the source code directory, <code>json-to-csv</code>. Change into that and run <code>npm init -y</code> so that we're ready to add some NPM packages.</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-vBuAp" id="cm-vBuAp">$ mkdir json-to-csv
$ cd json-to-csv
$ npm init -y</textarea><h1><a name="example-json-file" id="example-json-file">Example JSON file</a></h1><p>Let's now create am example JSON file that we can work with called <em>input.json</em>. I've created a simple data schema with three properties: name, email, and date of birth.</p><textarea data-iscodemirror="true" data-mode="application/json" data-uniqueid="cm-Pcmkc" id="cm-Pcmkc">/*input.json*/
[
  {
    "name": "James Holden",
    "email": "james@rocinante.com",
    "dob": "2370-07-23"
  },
  {
    "name": "Amos Burton",
    "email": "amos@rocinante.com",
    "dob": "2368-12-12"
  },
  {
    "name": "Naomi Nagata",
    "email": "naomi@rocinante.com",
    "dob": "2372-03-11"
  },
  {
    "name": "Alex,Camal",
    "email": "alex@rocinante.com",
    "dob": "2366-03-12"
  }
]</textarea><h1><a name="creating-the-cli-with-yargs" id="creating-the-cli-with-yargs">Creating the CLI with yargs</a></h1><p>It'd be very handy to allow our utility to take in a file name input and file name output so that we can use it from the CLI. Here's the command we should be able to use from within the<em> json-to-csv</em> directory:</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-efQco" id="cm-efQco">$ node ./ input.json output.csv</textarea><p>So let's now create an <em>index.js</em> file and install the <a href="https://www.google.com/search?q=github+yargs&amp;oq=github+yargs&amp;aqs=chrome..69i57j69i60l4j69i65j69i60l2.1736j0j7&amp;sourceid=chrome&amp;ie=UTF-8" rel="noopener noreferrer nofollow">yargs</a> package to handle CLI input:</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-iwLtt" id="cm-iwLtt">$ touch index.js
$ npm i -S yargs</textarea><p>Inside <em>index.js</em>, let's require the yargs package and assign the <code>argv</code> property to a variable. This variable will effectively hold any CLI inputs captured by yargs.</p><p>Nameless CLI arguments will be in an array at the <code>_</code> property of <code>argv</code>. Let's grab these and assign them to obviously-named variables <code>inputFileName</code> and <code>outputFileName</code>. </p><p>We'll also console log the values now to check they're working how we expect:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-WgZga" id="cm-WgZga">const argv = require('yargs').argv;

const inputFileName = argv._[0];
const outputFileName = argv._[1];

console.log(inputFileName, outputFileName);</textarea><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-eAzyg" id="cm-eAzyg">$ node ./ input.json output.csv
# input,json, output.csv</textarea><h1><a name="reading-and-parsing-a-json-file" id="reading-and-parsing-a-json-file">Reading and parsing A JSON file</a></h1><p>For file operations, we're going to use the <code>promises</code> API of the <code>fs</code> package of Node.js. This will make handling files a lot easier than using the standard callbacks pattern.</p><p>Let's do a destructure assignment to grab the <code>readFile</code> and <code>writeFile </code>methods which are all we'll need in this project.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-m0dd1" id="cm-m0dd1">...

const { readFile, writeFile } = require('fs').promises;</textarea><p>Let's now write a function that will parse the JSON file. Since file reading is an asynchronous process, let's make it an <code>async</code> function and name it <code>parseJSONFile</code>. This method will take the file name as an argument.</p><p>In the method body, add a <code>try</code>/<code>catch</code> block. In the <code>try</code>, we'll create a variable <code>file</code> and assign to this <code>await readFile(fileName)</code> which will load the raw file. Next, we'll parse the contents as JSON and return it.</p><p>In the <code>catch</code> block, we should console log the error so the user knows what's gone wrong. We should also exit the script by calling <code>process.exit(1)</code> which indicates to the shell that the process failed.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-5Uq1m" id="cm-5Uq1m">...

async function parseJSONFile (fileName) {
  try {
    const file = await readFile(fileName);
    reutrn JSON.parse(file);
  } catch (err) {
    console.log(err);
    process.exit(1);
  }
}</textarea><h1><a name="converting-json-data-to-csv-format" id="converting-json-data-to-csv-format">Converting JSON data to CSV format</a></h1><p>We'll now write a method to convert the JavaScript array returned from the <code>parseJSONFile</code> to a CSV-compatible format.  First, we're going to extract the <em>values</em> of each object in the array, discarding the keys. To do this, we'll <code>map</code> a new array where each element is itself an array of the object values.</p><p>Next, we'll use the array <code>unshift</code> method to insert a header row to the top of the data. We'll pass to this the object<em> keys</em> of any one of the objects (since we assume they all have the same keys for the sake of simplicity).</p><p>The last step is to convert the JavaScript object to CSV-compatible string. It's as simple as using the <code>join</code> method and joining each object with a newline (<code>\n</code>).</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-OTQAu" id="cm-OTQAu">...

function arrayToCSV (data) {
  csv = data.map(row =&gt; Object.values(row));
  csv.unshift(Object.keys(data[0]));
  return csv.join('\n');
}
/*
const data = [ 
	{ a:"1", b:"2" },
    { a:"3", b:"4" }, 
    { a:"5", b:"6" }, 
];
arrayToCSV(data);

output:
a,b
1,2
3,4
5,6
*/</textarea><h2><a name="adding-quotes-around-fields" id="adding-quotes-around-fields">Adding quotes around fields</a></h2><p>We're not quite finished - CSV fields should be surrounded by quotes to escape any commas from within the string. There's an easy way to do this: </p><ol><li><p>Print the string within a string template with surrounding quotes <code>"${csv.join('\n')"</code></p></li><li><p> Put at the beginning and end of each new line <code>.join('"\n"\)</code></p></li><li><p>Replace each comma in the string with a quote surrounded comma by chaining a regex <code>.replace(/,/g, '","')</code></p></li></ol><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-RZTjT" id="cm-RZTjT">...

function arrayToCSV (data) {
  csv = data.map(row =&gt; Object.values(row));
  csv.unshift(Object.keys(data[0]));
  return `"${csv.join('"\n"').replace(/,/g, '","')}"`;
}
/*
const data = [ 
	{ a:"1", b:"2" },
    { a:"3", b:"4" }, 
    { a:"5", b:"6" }, 
];
arrayToCSV(data);

output:
"a","b"
"1","2"
"3","4"
"5","6"
*/</textarea><h1><a name="writing-csv-file" id="writing-csv-file">Writing CSV file</a></h1><p>It's fairly trivial now to write our CSV file now that we have a CSV string - we just need to call <code>writeFile</code> from an async method <code>writeCSV</code>. Just like in the parse method we'll include a <code>try</code>/<code>catch</code> block and exit on error.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-jQxCt" id="cm-jQxCt">...

async function writeCSV (fileName, data) {
  try {
  	await writeFile(fileName, data, 'utf8'); 
  } catch (err) {
    console.log(err);
    process.exit(1);
  }
}</textarea><h1><a name="putting-it-all-together" id="putting-it-all-together">Putting it all together</a></h1><p>To run our CSV converter we'll add an <a href="https://developer.mozilla.org/en-US/docs/Glossary/IIFE" rel="noopener noreferrer nofollow">IIFE</a> to the bottom of the file. Within this function, we'll call each of our methods in the sequence we wrote them, passing data from one to the next. At the end, let's console log a message so the user knows the conversion process worked.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-Q2ASV" id="cm-Q2ASV">const argv = require('yargs').argv;

const inputFileName = argv._[0];
const outputFileName = argv._[1];

const { readFile, writeFile } = require('fs').promises;

async function parseJSONFile (fileName) {...}

function arrayToCSV (data) {...}

async function writeCSV (fileName, data) {...}

(async () =&gt; {
  	const data = await parseJSONFile(inputFileName);
  	const CSV = arrayToCSV(data);
  	await writeCSV(outputFileName, CSV);
	console.log(`Successfully converted ${outputFileName}!`);
})();</textarea><p>Let's now try and run the CLI command using our example JSON file:</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-RZTjT" id="cm-RZTjT">$ node ./ input.json output.csv
# Successfully converted output.csv!</textarea><p>It works! Here's what the output looks like:</p><textarea data-iscodemirror="true" data-mode="text/html" data-uniqueid="cm-zfdiW" id="cm-zfdiW">"name","email","dob"
"James Holden","james@rocinante.com","2370-07-23"
"Amos Burton","amos@rocinante.com","2368-12-12"
"Naomi Nagata","naomi@rocinante.com","2372-03-11"
"Alex,Camal","alex@rocinante.com","2366-03-12"</textarea><h1><a name="escaping-commas" id="escaping-commas">Escaping commas</a></h1><p>There's a fatal flaw in our script: if any CSV fields contain commas they will be made into separate fields during the conversion. Note in the below example what happens to the second field of the last row which includes a comma:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-cQJYU" id="cm-cQJYU">function arrayToCSV (data) {
  csv = data.map(row =&gt; Object.values(row));
  csv.unshift(Object.keys(data[0]));
  return `"${csv.join('"\n"').replace(/,/g, '","')}"`;
}
/*
const data = [ 
	{ a:"1", b:"2" },
    { a:"3", b:"4" }, 
    { a:"5", b:"6,7" }, 
];
arrayToCSV(data);

output:
"a","b"
"1","2"
"3","4"
"5","6","7"
*/ </textarea><p>To fix this, we'll need to escape any commas before the data gets passed to the <code>arrayToCSV</code> method, then unescape them afterward. We're going to create two methods to do this: <code>escapeCommas</code> and <code>unescapeCommas</code>. </p><p>In the former, we'll use <code>map</code> to create a new array where comma values are replaced by a variable <code>token</code>. This token can be anything you like, so long as it doesn't occur in the CSV data. For this reason, I recommend something random like <code>~~~~</code> or <code>!!!!</code>.</p><p>In the <code>unescapeCommas</code> method, we'll replace the token with the commas and restore the original content.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-hcKcL" id="cm-hcKcL">function escapeCommas (data, token) {
  return data.map(row =&gt; {
    return row.map((value, index) =&gt; {
      return row[index] = value.replace(/,/g, token);
    });
  });
}

function unescapeCommas (data, token) {
  return data.replace(new RegExp(`${token}`, 'g'), ',');
}</textarea><p>Here's how we'll modify our run function to incorporate these new methods:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-MEO7d" id="cm-MEO7d">(async () =&gt; {
  const data = await parseJSONFile(inputFileName);
  const escapeToken = '~~~~';
  const escapedData = escapeCommas(data, escapeToken);
  const escapedCSV = arrayToCSV(escapedData);
  const CSV = unescapeCommas(escapedCSV);
  await writeCSV(outputFileName, CSV);
  console.log(`Successfully converted ${outputFileName}!`);
})();</textarea><p>With that done, the convertor can now handle commas in the content.</p><h1><a name="testing-in-google-sheets" id="testing-in-google-sheets">Testing in Google Sheets</a></h1><p>Here's the real test of our CLI tool...can we import a converted sheet into Google Sheets? Yes, it works perfectly! Note I even put a comma in one of the fields to ensure the escape mechanism works.</p><figure class="photo figure-position-inline"><img src="https://s3.amazonaws.com/assets.fullstack.io/n/20201004070708732_Screen%20Shot%202020-10-04%20at%206.06.37%20pm.png" attachmentid="80f51984-7d59-49de-8e81-509ad6ea3ba8" contenteditable="false"><div></div></figure><h1><a name="using-the-json-2-csv-package" id="using-the-json-2-csv-package">Using the json-2-csv package</a></h1><p>While it's good to understand the underlying mechanism of CSV conversion in case we need to customize it, in most projects, we'd probably just use a package like<em> </em><a href="https://github.com/mrodrig/json-2-csv" rel="noopener noreferrer nofollow">json-2-csv</a>.</p><p>Not only will this save us having to create the conversion functionality ourselves, but it also has a lot of additional features we haven't included like the ability to use different schema structures and delimiters.</p><p>Let's now update our project to use this package instead. First, go ahead and install it on the command line:</p><textarea data-iscodemirror="true" data-mode="text/x-sh" data-uniqueid="cm-2odJh" id="cm-2odJh">$ npm i -S json-2-csv</textarea><p>Next, let's require it in our project and assign it to a variable using destructuring:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-2odJh" id="cm-2odJh">...

const { json2csvAsync } = require('json-2-csv');

...</textarea><p>We can now modify our run function to use this package instead of our custom <code>arrayToCSV</code> method. Note we no longer need to escape our content either as the package will do this for us as well.</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-2odJh" id="cm-2odJh">(async () =&gt; {
  const data = await parseJSONFile(inputFileName);
  const csv = await json2csvAsync(data);
  await writeCSV(outputFileName, csv);
  console.log(`Successfully converted ${outputFileName}!`);
})();</textarea><p>With this change, run the CLI command again and you should get <em>almost</em> the same results. The key difference is that this package will only wrap fields in double-quotes if they need escaping as this still produces a valid CSV.</p><textarea data-iscodemirror="true" data-mode="text/html" data-uniqueid="cm-p06C0" id="cm-p06C0">name,email,dob
James Holden,james@rocinante.com,2370-07-23
Amos Burton,amos@rocinante.com,2368-12-12
Naomi Nagata,naomi@rocinante.com,2372-03-11
"Alex,Camal",alex@rocinante.com,2366-03-12</textarea><h1><a name="conclusion" id="conclusion">Conclusion</a></h1><p>So now you've learned how to create a CLI script for converting JSON to CSV using Node.js. Here's the complete script for your reference or if you've skimmed the article:</p><textarea data-iscodemirror="true" data-mode="javascript" data-uniqueid="cm-2odJh" id="cm-2odJh">const argv = require('yargs').argv;
const { readFile, writeFile } = require('fs').promises;
const { json2csvAsync } = require('json-2-csv');

const inputFileName = argv._[0];
const outputFileName = argv._[1];

async function parseJSONFile (fileName) {
  try {
    const file = await readFile(fileName);
    return JSON.parse(file);
  } catch (err) {
    console.log(err);
    process.exit(1);
  }
}

async function writeCSV (fileName, data) {
  await writeFile(fileName, data, 'utf8');
}

(async () =&gt; {
  const data = await parseJSONFile(inputFileName);
  const csv = await json2csvAsync(data);
  await writeCSV(outputFileName, csv);
  console.log(`Successfully converted ${outputFileName}!`);
})();</textarea>

Learn

The newline Guide to Building Your First GraphQL Server with Node and TypeScript

Teach

Amelia Wattenberger

Author of Fullstack D3

Community

How to Convert JSON to CSV in Node.js

Tags

Responses (0)

Masterclasses

Tutorials

Fullstack React with TypeScript