How to convert JSON to CSV format and store in a variable

javascript json csv
praneybehl picture praneybehl · Jan 13, 2012 · Viewed 248k times · Source

I have a link that opens up JSON data in the browser, but unfortunately I have no clue how to read it. Is there a way to convert this data using JavaScript in CSV format and save it in JavaScript file?

The data looks like:

{
  "count": 2,
  "items": [{
    "title": "Apple iPhone 4S Sale Cancelled in Beijing Amid Chaos (Design You Trust)",
    "description": "Advertise here with BSA Apple cancelled its scheduled sale of iPhone 4S in one of its stores in China\u2019s capital Beijing on January 13. Crowds outside the store in the Sanlitun district were waiting on queues overnight. There were incidents of scuffle between shoppers and the store\u2019s security staff when shoppers, hundreds of them, were told that the sales [...]Source : Design You TrustExplore : iPhone, iPhone 4, Phone",
    "link": "http:\/\/wik.io\/info\/US\/309201303",
    "timestamp": 1326439500,
    "image": null,
    "embed": null,
    "language": null,
    "user": null,
    "user_image": null,
    "user_link": null,
    "user_id": null,
    "geo": null,
    "source": "wikio",
    "favicon": "http:\/\/wikio.com\/favicon.ico",
    "type": "blogs",
    "domain": "wik.io",
    "id": "2388575404943858468"
  }, {
    "title": "Apple to halt sales of iPhone 4S in China (Fame Dubai Blog)",
    "description": "SHANGHAI \u2013 Apple Inc said on Friday it will stop selling its latest iPhone in its retail stores in Beijing and Shanghai to ensure the safety of its customers and employees. Go to SourceSource : Fame Dubai BlogExplore : iPhone, iPhone 4, Phone",
    "link": "http:\/\/wik.io\/info\/US\/309198933",
    "timestamp": 1326439320,
    "image": null,
    "embed": null,
    "language": null,
    "user": null,
    "user_image": null,
    "user_link": null,
    "user_id": null,
    "geo": null,
    "source": "wikio",
    "favicon": "http:\/\/wikio.com\/favicon.ico",
    "type": "blogs",
    "domain": "wik.io",
    "id": "16209851193593872066"
  }]
}

The closest I could find was: Convert JSON format to CSV format for MS Excel

But it downloads in a CSV file, I store it in a variable, the whole converted data.

Also would like to know how to change escape characters: '\u2019' back to normal.

I tried this code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
  <title>JSON to CSV</title>
  <script src="http://code.jquery.com/jquery-1.7.1.js" type="text/javascript"></script>
  <script type="text/javascript">
    var json3 = {
      "count": 2,
      "items": [{
          "title": "Apple iPhone 4S Sale Cancelled in Beijing Amid Chaos (Design You Trust)",
          "description": "Advertise here with BSA Apple cancelled its scheduled sale of iPhone 4S in one of its stores in China’s capital Beijing on January 13. Crowds outside the store in the Sanlitun district were waiting on queues overnight. There were incidents of scuffle between shoppers and the store’s security staff when shoppers, hundreds of them, were told that the sales [...]Source : Design You TrustExplore : iPhone, iPhone 4, Phone",
          "link": "http://wik.io/info/US/309201303",
          "timestamp": 1326439500,
          "image": null,
          "embed": null,
          "language": null,
          "user": null,
          "user_image": null,
          "user_link": null,
          "user_id": null,
          "geo": null,
          "source": "wikio",
          "favicon": "http://wikio.com/favicon.ico",
          "type": "blogs",
          "domain": "wik.io",
          "id": "2388575404943858468"
        },
        {
          "title": "Apple to halt sales of iPhone 4S in China (Fame Dubai Blog)",
          "description": "SHANGHAI – Apple Inc said on Friday it will stop selling its latest iPhone in its retail stores in Beijing and Shanghai to ensure the safety of its customers and employees. Go to SourceSource : Fame Dubai BlogExplore : iPhone, iPhone 4, Phone",
          "link": "http://wik.io/info/US/309198933",
          "timestamp": 1326439320,
          "image": null,
          "embed": null,
          "language": null,
          "user": null,
          "user_image": null,
          "user_link": null,
          "user_id": null,
          "geo": null,
          "source": "wikio",
          "favicon": "http://wikio.com/favicon.ico",
          "type": "blogs",
          "domain": "wik.io",
          "id": "16209851193593872066"
        }
      ]
    }
    //var objJson = JSON.parse(json3.items);

    DownloadJSON2CSV(json3.items);

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

      var str = '';

      for (var i = 0; i < array.length; i++) {
        var line = '';

        for (var index in array[i]) {
          line += array[i][index] + ',';
        }

        line.slice(0, line.Length - 1);

        str += line + '\r\n';
      }
      $('div').html(str);
    }
  </script>

</head>

<body>
  <div></div>
</body>

</html>

But it doesn't seem to work. Can someone please help?

Answer

Christian Landgren picture Christian Landgren · Jul 21, 2015

A more elegant way to convert json to csv is to use the map function without any framework:

var json = json3.items
var fields = Object.keys(json[0])
var replacer = function(key, value) { return value === null ? '' : value } 
var csv = json.map(function(row){
  return fields.map(function(fieldName){
    return JSON.stringify(row[fieldName], replacer)
  }).join(',')
})
csv.unshift(fields.join(',')) // add header column
 csv = csv.join('\r\n');
console.log(csv)

Output:

title,description,link,timestamp,image,embed,language,user,user_image,user_link,user_id,geo,source,favicon,type,domain,id
"Apple iPhone 4S Sale Cancelled in Beijing Amid Chaos (Design You Trust)","Advertise here with BSA Apple cancelled its scheduled sale of iPhone 4S in one of its stores in China’s capital Beijing on January 13. Crowds outside the store in the Sanlitun district were waiting on queues overnight. There were incidents of scuffle between shoppers and the store’s security staff when shoppers, hundreds of them, were told that the sales [...]Source : Design You TrustExplore : iPhone, iPhone 4, Phone","http://wik.io/info/US/309201303","1326439500","","","","","","","","","wikio","http://wikio.com/favicon.ico","blogs","wik.io","2388575404943858468"
"Apple to halt sales of iPhone 4S in China (Fame Dubai Blog)","SHANGHAI – Apple Inc said on Friday it will stop selling its latest iPhone in its retail stores in Beijing and Shanghai to ensure the safety of its customers and employees. Go to SourceSource : Fame Dubai BlogExplore : iPhone, iPhone 4, Phone","http://wik.io/info/US/309198933","1326439320","","","","","","","","","wikio","http://wikio.com/favicon.ico","blogs","wik.io","16209851193593872066"

Update ES6 (2016)

Use this less dense syntax and also JSON.stringify to add quotes to strings while keeping numbers unquoted:

const items = json3.items
const replacer = (key, value) => value === null ? '' : value // specify how you want to handle null values here
const header = Object.keys(items[0])
let csv = items.map(row => header.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(','))
csv.unshift(header.join(','))
csv = csv.join('\r\n')

console.log(csv)

Related questions

How to convert CSV to JSON in Node.js

I am trying to convert csv file to json. I am using . Example CSV: a,b,c,d 1,2,3,4 5,6,7,8 ... Desired JSON: {"a": 1,"b": 2,"c": 3,"d": 4}, {"a": 5,"b": 6,"c": 7,"d": 8}, ... I tried node-csv parser library.But the output is like array not …

Read More
How to extract data to React state from CSV file using Papa Parse?

I'm using Papa Parse to parse a CSV file for Graphs. I want to store the data in React state after the file is parsed. Papa.Parse() doesn't return anything and results are provided asynchronously to a callback function. Also, …

Read More
Parse JSON in JavaScript?

I want to parse a JSON string in JavaScript. The response is something like var response = '{"result":true,"count":1}'; How can I get the values result and count from this?

Read More