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

Question

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?

you have a good code there. the line which downloads is window.open( "data:text/csv;charset=utf-8," + escape(str)).. just skip it if you don't need it. and the csv string is kept in this variable: str — zdrsh, Jan 13 '12 at 08:36
CSV can´t handle multiple levels of data (as well) as JSON. How would you expect your JSON to look like as CSV? `2,Apple iPhone 4S Sale Cancelled in Beijing Amid Chaos (Design You Trust), ...` ? — Stefan, Jan 13 '12 at 08:41
I would like my data to look like: 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..,,,,,, etc I can easily remove this starting characters: " {"count":2,"items":[:" — praneybehl, Jan 13 '12 at 08:52
@zdrsh yes but for some reason I am not able to get it to work. — praneybehl, Jan 13 '12 at 08:54
@zdrsh thanks for the link and the help mate. Don't know where your comment went. In your link http://jsfiddle.net/5TKBx/ there are no script references but I believe I'll have to add those wouldn't I. Sorry to sound like an idiot. — praneybehl, Jan 13 '12 at 10:23
I deleted them, i realized that i didn't checked your json object. Missed my eye. — zdrsh, Jan 13 '12 at 10:35
Have you tried https://data.page/json/csv ? This is what I use if I ever need to convert json to csv. (Disclaimer: this is not my website) — Jayden Lawson, Jun 22 '21 at 23:37

Christian Landgren · Answer 1 · 2020-12-19T20:41:58.013

273

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])
const csv = [
  header.join(','), // header row first
  ...items.map(row => header.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(','))
].join('\r\n')

console.log(csv)

edited Dec 19 '20 at 20:41

answered Jul 21 '15 at 10:27

Christian Landgren

13,127
6
35
31

6

I like the terseness of this, but it should be noted that it doesn't handle a few things that some might find ideal. e.g. each record on its own line, numbers and booleans left unquoted, etc. – scunliffe Aug 12 '15 at 12:32
2

You can add a + "\r\n" after the fields.map() to get one record per row. To get numbers unquoted you can use JSON.stringify(row[fieldName]) instead which will quote only strings and leave numbers unquoted. – Christian Landgren Nov 24 '15 at 20:27
2

@scunliffe : I updated a new example with JSON.stringify - it should handle the cases you described. – Christian Landgren Jun 08 '16 at 12:36
the line `return JSON.stringify(row[fieldName] || '');` will print zeros as "" – marathon Oct 19 '16 at 19:00
1

@marathon, Good catch, updated the example with a replacer to handle null cases separately. If no replacer is used null will be outputted as `null` - now the examples should handle both null, undefined and numbers correctly. – Christian Landgren Oct 20 '16 at 07:15
Thanks @ChristianLandgren! It works great except for my nested objects, so for now I'm going to flatten the nested objects unless you have an elegant solution? – JulieMarie Aug 08 '17 at 20:15
1

es6 implementation is superb. just one query. In `const replacer = (key, value) => value === null ? '' : value ` have no means of *key* parameter as JSON data has the vaue only than why we keep this? – xkeshav Aug 09 '17 at 12:41
@pro.mean - thanks! Well, that was my tought initially too but as marathon pointed out - when a null value was passed without special handling - JSON.stringify will print the string "null", that is why I added the replacer function. – Christian Landgren Aug 20 '17 at 07:38
13

Worth noting that this escapes strings in quotes using `\"` which allows some fields to "pop out" of their column when viewed in Excel (which seems to prefer `""` as an escape character for quotes). This can be solved by adding `.replace(/\\"/g, '""')` to the end of `JSON.stringify(row[fieldName], replacer)` as I noted in my answer above. – user1274820 Oct 03 '18 at 22:54
In case you don't want the headers in the `csv`: ```console.log(csv.substring(csv.indexOf("\n") + 1))``` – Zain Mohsin Mar 26 '20 at 13:33
@ChristianLandgren: Is this way of creating json to csv using client side js code safe from CSV injection vulnerability ? – Luke P. Issac Apr 28 '21 at 10:18
Adding to @user1274820's comment: The given code will often produce **invalid** CSV - `\"` is not a valid escape in CSV - see [the standard](https://datatracker.ietf.org/doc/html/rfc4180), paragraph 2.7. – Marko Knöbl Dec 10 '21 at 19:05
1

This seems to not properly handle literal newlines, i.e. it outputs "\n" instead of a literal newline when it's in a CSV field. – Michael Jan 25 '22 at 02:07

score 70 · Accepted Answer · answered Jan 19 '12 at 10:56

Ok I finally got this code working:

<html>
<head>
    <title>Demo - Covnert JSON to CSV</title>
    <script type="text/javascript" src="http://code.jquery.com/jquery-latest.js"></script>
    <script type="text/javascript" src="https://github.com/douglascrockford/JSON-js/raw/master/json2.js"></script>

    <script type="text/javascript">
        // JSON to CSV Converter
        function ConvertToCSV(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]) {
                    if (line != '') line += ','

                    line += array[i][index];
                }

                str += line + '\r\n';
            }

            return str;
        }

        // Example
        $(document).ready(function () {

            // Create Object
            var items = [
                  { name: "Item 1", color: "Green", size: "X-Large" },
                  { name: "Item 2", color: "Green", size: "X-Large" },
                  { name: "Item 3", color: "Green", size: "X-Large" }];

            // Convert Object to JSON
            var jsonObject = JSON.stringify(items);

            // Display JSON
            $('#json').text(jsonObject);

            // Convert JSON to CSV & Display CSV
            $('#csv').text(ConvertToCSV(jsonObject));
        });
    </script>
</head>
<body>
    <h1>
        JSON</h1>
    <pre id="json"></pre>
    <h1>
        CSV</h1>
    <pre id="csv"></pre>
</body>
</html>

Thanks alot for all the support to all the contributors.

Praney

I tried this . i have three columns but in excel all things will be coming in a single column — Nithesh Narayanan, Aug 16 '12 at 09:10
Thanks for sharing this here. Just used it and it works perfect. — Ramin Arabbagheri, Jun 03 '16 at 20:38
Thanks for this! I added the following to avoid having "[object Object]" in the line if a cell contains an object. if (_.isObject(array[i][index])) { array[i][index] = JSON.stringify(array[i][index]); } . (uses underscore, but you could change to vanilla) — Claytronicon, Dec 14 '17 at 18:44
@Sunil I found if the values contain commas, it breaks. For my needs I just did this: var re = new RegExp(',', 'g'); array[i][index] = array[i][index].toString().replace(re, ';') — Claytronicon, Dec 14 '17 at 19:19

score 25 · Answer 3 · edited Feb 07 '21 at 19:49

Very nice solution by praneybehl, but if someone wants to save the data as a csv file and using a blob method then they can refer this:

function JSONToCSVConvertor(JSONData, ReportTitle, ShowLabel) {

    //If JSONData is not an object then JSON.parse will parse the JSON string in an Object
    var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
    var CSV = '';
    //This condition will generate the Label/Header
    if (ShowLabel) {
        var row = "";

        //This loop will extract the label from 1st index of on array
        for (var index in arrData[0]) {
            //Now convert each value to string and comma-seprated
            row += index + ',';
        }
        row = row.slice(0, -1);
        //append Label row with line break
        CSV += row + '\r\n';
    }

    //1st loop is to extract each row
    for (var i = 0; i < arrData.length; i++) {
        var row = "";
        //2nd loop will extract each column and convert it in string comma-seprated
        for (var index in arrData[i]) {
            row += '"' + arrData[i][index] + '",';
        }
        row.slice(0, row.length - 1);
        //add a line break after each row
        CSV += row + '\r\n';
    }

    if (CSV == '') {
        alert("Invalid data");
        return;
    }

    //this trick will generate a temp "a" tag
    var link = document.createElement("a");
    link.id = "lnkDwnldLnk";

    //this part will append the anchor tag and remove it after automatic click
    document.body.appendChild(link);

    var csv = CSV;
    blob = new Blob([csv], { type: 'text/csv' });
    var csvUrl = window.webkitURL.createObjectURL(blob);
    var filename =  (ReportTitle || 'UserExport') + '.csv';
    $("#lnkDwnldLnk")
        .attr({
            'download': filename,
            'href': csvUrl
        });

    $('#lnkDwnldLnk')[0].click();
    document.body.removeChild(link);
}

This solution works but has some weird spots--you define `var row` twice (if statements and for loops don't create closures). Also the label/header loop could probably be reduced to one line: `Object.keys(arrData[0]).join(',')` — ccnokes, Apr 27 '15 at 19:36
Your answer is working. But for cases like if some column is not available for some row it will not consider the missing column and will not realign the column data for that row data. — sms, May 21 '15 at 11:42
I was able to get this method to work but I had to edit some of the code to: 1. work without JQuery: `document.getElementById("lnkDwnldLnk").download = filename;` `document.getElementById("lnkDwnldLnk").href = csvUrl;` 2. work in IE11: `if (window.navigator && window.navigator.msSaveOrOpenBlob) {` `window.navigator.msSaveOrOpenBlob(blob, filename);` `} else {` `document.getElementById('lnkDwnldLnk').click();` `}` — Jason, Aug 29 '17 at 15:00

score 24 · Answer 4 · answered Apr 09 '19 at 02:55

If anyone wanted to download it as well.
Here is an awesome little function that will convert an array of JSON objects to csv, then download it.

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

  // Create link and download
  var link = document.createElement('a');
  link.setAttribute('href', 'data:text/csv;charset=utf-8,%EF%BB%BF' + encodeURIComponent(csv));
  link.setAttribute('download', filename);
  link.style.visibility = 'hidden';
  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
};

Then call it like this:

this.downloadCSVFromJson(`myCustomName.csv`, this.state.csvArrayOfJson)

this doesn't seem to work for things when there is a single quote in one of the elements e.g. `Cap D'antibes` — MidnightDataGeek, Jun 11 '20 at 11:54
I haven't tested it, so I don't know if this answer works. Regardless having a function / method that is responsible for more than 1 thing (a.l.a. Single Responsibility Principle) is not a good thing. I just wanted to emphasize that. PS: Having 2 functions / methods instead is the way to go; one is responsible for downloading a file and the other one is responsible for JSON to CSV conversion. — ozanmuyes, Jul 12 '22 at 16:02

user1274820 · Answer 5 · 2018-12-19T19:45:21.877

I just wanted to add some code here for people in the future since I was trying to export JSON to a CSV document and download it.

I use $.getJSON to pull json data from an external page, but if you have a basic array, you can just use that.

This uses Christian Landgren's code to create the csv data.

$(document).ready(function() {
    var JSONData = $.getJSON("GetJsonData.php", function(data) {
        var items = data;
        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');

        //Download the file as CSV
        var downloadLink = document.createElement("a");
        var blob = new Blob(["\ufeff", csv]);
        var url = URL.createObjectURL(blob);
        downloadLink.href = url;
        downloadLink.download = "DataDump.csv";  //Name the file here
        document.body.appendChild(downloadLink);
        downloadLink.click();
        document.body.removeChild(downloadLink);
    });
});

Edit: It's worth noting that JSON.stringify will escape quotes in quotes by adding \". If you view the CSV in excel, it doesn't like that as an escape character.

You can add .replace(/\\"/g, '""') to the end of JSON.stringify(row[fieldName], replacer) to display this properly in excel (this will replace \" with "" which is what excel prefers).

Full Line: let csv = items.map(row => header.map(fieldName => (JSON.stringify(row[fieldName], replacer).replace(/\\"/g, '""'))).join(','));

could just be me but I got `TypeError: Cannot read property 'replace' of undefined` — Kyle Pennell, Jan 24 '21 at 22:42
@KylePennell Exactly, I get this as well and I really need his fix. — erol_smsr, Apr 07 '21 at 13:28
If `JSON.stringify` is returning `undefined` then you may have bigger issues? Without the `.replace` line, are you getting data returned? — user1274820, Apr 07 '21 at 14:03
I'm late to the game, but my data was returning undefined for some rows and so I was receiving the `TypeError`. As `undefined === null` returns false it was keeping the value of `undefined` which you can't use `replace()` on. Updating the replacer expression to `!value ? '' : value;` resolved my issue. — Joe B., Apr 26 '22 at 21:49

nagu · Answer 6 · 2018-01-20T06:55:08.560

16

There are multiple options available to reuse the existing powerful libraries that are standards based.

If you happen to use D3 in your project, then you can simply invoke:

d3.csv.format or d3.csv.formatRows functions to convert an array of objects into csv string.

d3.csv.formatRows gives you greater control over which properties are converted to csv.

Please refer to d3.csv.format and d3.csv.formatRows wiki pages.

There are other libraries available too like jquery-csv, PapaParse. Papa Parse has no dependencies - not even jQuery.

For jquery based plugins, please check this.

edited Jan 20 '18 at 06:55

answered Aug 25 '17 at 06:48

nagu

877
10
17

2

This worked great for me. Note the D3 API has changed since 2017.v3 (it's currently v4): https://github.com/d3/d3-dsv/blob/v1.2.0/README.md#csvFormat – allanberry Jan 20 '20 at 18:50
2

Thanks! I used PapaParse's Papa.unparse(data) (https://www.papaparse.com/docs#json-to-csv). Quick fix to my issues! – Daniel Valenzuela Nov 08 '20 at 16:53

YouBee · Answer 7 · 2022-06-08T06:00:20.937

Try these Examples

Example 1:

JsonArray = [{
    "AccountNumber": "123",
    "AccountName": "abc",
    "port": "All",
    "source": "sg-a78c04f8"

}, {
    "Account Number": "123",
    "Account Name": "abc",
    "port": 22,
    "source": "0.0.0.0/0",
}]

JsonFields = ["Account Number","Account Name","port","source"]

function JsonToCSV(){
    var csvStr = JsonFields.join(",") + "\n";

    JsonArray.forEach(element => {
        AccountNumber = element.AccountNumber;
        AccountName   = element.AccountName;
        port          = element.port
        source        = element.source

        csvStr += AccountNumber + ',' + AccountName + ','  + port + ',' + source + "\n";
        })
        return csvStr;
}

Example2 :

JsonArray = [{
    "AccountNumber": "1234",
    "AccountName": "abc",
    "inbound": [{
        "port": "All",
        "source": "sg-a78c04f8"
    },
    {
        "port": 22,
        "source": "0.0.0.0/0",
    }]
}]

JsonFields = ["Account Number", "Account Name", "port", "source"]

function JsonToCSV() {
    var csvStr = JsonFields.join(",") + "\n";

    JsonArray.forEach(element => {
        AccountNumber = element.AccountNumber;
        AccountName = element.AccountName;
        
        element.inbound.forEach(inboundELe => {
            port = inboundELe.port
            source = inboundELe.source
            csvStr += AccountNumber + ',' + AccountName + ',' + port + ',' + source + "\n";
        })
    })
    return csvStr;
}

You can even download the csv file using the following code :

function downloadCSV(csvStr) {

    var hiddenElement = document.createElement('a');
    hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(csvStr);
    hiddenElement.target = '_blank';
    hiddenElement.download = 'output.csv';
    hiddenElement.click();
}

@saravanakumarramasamy Create a Blob Object and pass the 'csvStr' to it. ` let file = new Blob([csvStr], { type: 'text/csv' }); const hiddenElement= document.createElement("a"); hiddenElement.href = URL.createObjectURL(file);` and keep rest of the lines same from hiddenElement.download. — Prasanna, Jul 06 '22 at 17:42

score 6 · Answer 8 · answered Jul 04 '18 at 06:42

<!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>
</head>
<body>
    <h1>This page does nothing....</h1>

    <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"
            }
          ]
        };

        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')

        var link = document.createElement("a");    
        link.id="lnkDwnldLnk";
        document.body.appendChild(link);
        blob = new Blob([csv], { type: 'text/csv' }); 
        var csvUrl = window.webkitURL.createObjectURL(blob);
        var filename = 'UserExport.csv';
        jQuery("#lnkDwnldLnk")
        .attr({
            'download': filename,
            'href': csvUrl
        });
        jQuery('#lnkDwnldLnk')[0].click();
        document.body.removeChild(link);
    </script>
</body>
</html>

chickens · Answer 9 · 2022-02-02T11:10:36.923

An elegant way to convert object array to CSV:

const convertToCsv = (arr) => {
    const keys = Object.keys(arr[0]);
    const replacer = (_key, value) => value === null ? '' : value;
    const processRow = row => keys.map(key => JSON.stringify(row[key], replacer)).join(',');
    return [ keys.join(','), ...arr.map(processRow) ].join('\r\n');
};

To download it as file:

const downloadFile = (fileName, data) => {
    var link = document.createElement('a');
    link.setAttribute('href', 'data:text/plain;charset=utf-8,' + encodeURIComponent(data));
    link.setAttribute('download', fileName);
    link.style.display = 'none';
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
};

Robbie Chiha · Answer 10 · 2022-07-07T03:23:40.527

I found the accepted answer extremely useful but needed my solution to work with unstructured json objects.

I have modified the accepted answer to work with an array of unstructured objects of varying size and schema.

Input:

[
  {
    "name": "Item 1",
    "color": "Green",
    "sizerange": {
      "max": "X-Large",
      "min": "X-Small"
    }
  },
  {
    "name": "Item 2",
    "color": "Green",
    "size": "X-Large",
    "owner": {
      "name": "Bill",
      "address": {
        "line1": "1 test st",
        "suburb": "testville"
      }
    }
  },
  {
    "name": "Item 3",
    "color": "Green",
    "sizes": [
      "X-Large",
      "Large",
      "Small"
    ]
  }
]

Output:

"name","color","sizerange.max","sizerange.min","size","owner.name","owner.address.line1","owner.address.suburb","sizes.0","sizes.1","sizes.2"
"Item 1","Green","X-Large","X-Small","","","","","","",""
"Item 2","Green","","","X-Large","Bill","1 test st","testville","","",""
"Item 3","Green","","","","","","","X-Large","Large","Small"

// JSON to CSV Converter

//https://www.codegrepper.com/code-examples/javascript/javascript+array+to+csv+string
function objectToCSVRow(dataObject) {
  var dataArray = [];
  for (var o in dataObject) {
    var innerValue = typeof dataObject[o] == 'undefined' ? '' : dataObject[o].toString();
    var result = innerValue.replace(/"/g, '""');
    result = '"' + result + '"';
    dataArray.push(result);
  }
  return dataArray.join(',') + '\r\n';
}

//https://stackoverflow.com/a/6491621
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 pushUnique(arr, item) {
  if (item != "" && !arr.includes(item))
    arr.push(item);
}


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

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

  var labels = [];

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

  }

  str += objectToCSVRow(labels);
  
  for (var i = 0; i < array.length; i++) {

    var line = [];
    for (var label in labels) {

      line.push(findbystring(array[i], labels[label]));

    }

    str += objectToCSVRow(line);
  }

  return str;
}

// Example
$(document).ready(function() {

  // Create Object
  var items = [{
      name: "Item 1",
      color: "Green",
      sizerange: {
        max: "X-Large",
        min: "X-Small"
      }
    },
    {
      name: "Item 2",
      color: "Green",
      size: "X-Large",
      owner: {
        name: "Bill",
        address: {
          line1: "1 test st",
          suburb: "testville"
        }
      }
    },
    {
      name: "Item 3",
      color: "Green",
      sizes: ["X-Large", "Large", "Small"]
    }
  ];

  // Convert Object to JSON
  var jsonObject = JSON.stringify(items, null, 2);

  // Display JSON
  $('#json').text(jsonObject);

  // Convert JSON to CSV & Display CSV
  $('#csv').text(ConvertToCSV(jsonObject));
});

<html>

<head>
  <title>Demo - Covnert JSON to CSV</title>
  <script type="text/javascript" src="http://code.jquery.com/jquery-latest.js"></script>
  <script type="text/javascript" src="https://github.com/douglascrockford/JSON-js/raw/master/json2.js"></script>

  <script type="text/javascript">
  </script>
</head>

<body>
  <h1>
    JSON</h1>
  <pre id="json"></pre>
  <h1>
    CSV</h1>
  <pre id="csv"></pre>
</body>

</html>

I am trying this type of input json and getting error. Please help to resolve the issue. [ { "obj-3": { "test": "url" } }, { "obj-3": "hello" } ] — user2979432, Nov 21 '22 at 08:36

ctholho · Answer 11 · 2020-06-18T10:57:25.543

Sometimes objects have different lengths. So I ran into the same problem as Kyle Pennell. But instead of sorting the array we simply traverse over it and pick the longest. Time complexity is reduced to O(n), compared to O(n log(n)) when sorting first.

I started with the code from Christian Landgren's updated ES6 (2016) version.

json2csv(json) {
    // you can skip this step if your input is a proper array anyways:
    const simpleArray = JSON.parse(json)
    // in array look for the object with most keys to use as header
    const header = simpleArray.map((x) => Object.keys(x))
      .reduce((acc, cur) => (acc.length > cur.length ? acc : cur), []);

    // specify how you want to handle null values here
    const replacer = (key, value) => (
      value === undefined || value === null ? '' : value);
    let csv = simpleArray.map((row) => header.map(
      (fieldName) => JSON.stringify(row[fieldName], replacer)).join(','));
    csv = [header.join(','), ...csv];
    return csv.join('\r\n');
}

Rodrigo João Bertotti · Answer 12 · 2020-07-03T17:21:43.737

An adaption from praneybehl answer to work with nested objects and tab separator

function ConvertToCSV(objArray) {
  let array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
  if(!Array.isArray(array))
      array = [array];

  let str = '';

  for (let i = 0; i < array.length; i++) {
    let line = '';
    for (let index in array[i]) {
      if (line != '') line += ','

      const item = array[i][index];
      line += (typeof item === 'object' && item !== null ? ConvertToCSV(item) : item);
    }
    str += line + '\r\n';
  }

  do{
      str = str.replace(',','\t').replace('\t\t', '\t');
  }while(str.includes(',') || str.includes('\t\t'));

  return str.replace(/(\r\n|\n|\r)/gm, ""); //removing line breaks: https://stackoverflow.com/a/10805198/4508758
}

This works perfect for copying and pasting straight into Excel/Sheets! Thanks! — UP3, Jun 26 '20 at 21:09

score 3 · Answer 13 · answered Feb 26 '21 at 20:32

Here is the latest answer using a well optimized and nice csv plugin: (The code may not work on stackoverflow here but will work in your project as i have tested it myself)

Using jquery and jquery.csv library (Very well optimized and perfectly escapes everything) https://github.com/typeiii/jquery-csv

// Create an array of objects
const data = [
    { name: "Item 1", color: "Green", size: "X-Large" },
    { name: "Item 2", color: "Green", size: "X-Large" },
    { name: "Item 3", color: "Green", size: "X-Large" }
];

// Convert to csv
const csv = $.csv.fromObjects(data);

// Download file as csv function
const downloadBlobAsFile = function(csv, filename){
    var downloadLink = document.createElement("a");
    var blob = new Blob([csv], { type: 'text/csv' });
    var url = URL.createObjectURL(blob);
    downloadLink.href = url;
    downloadLink.download = filename;
    document.body.appendChild(downloadLink);
    downloadLink.click();
    document.body.removeChild(downloadLink);
}

// Download csv file
downloadBlobAsFile(csv, 'filename.csv');

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdn.tutorialjinni.com/jquery-csv/1.0.11/jquery.csv.min.js"></script>

score 2 · Answer 14 · edited Feb 25 '19 at 12:07

Heres a way to do it for dynamically deep objects in a object oriented way for the newer js versions. you might have to change the seperatortype after region.

private ConvertToCSV(objArray) {
    let rows = typeof objArray !== "object" ? JSON.parse(objArray) : objArray;
    let  header = "";
    Object.keys(rows[0]).map(pr => (header += pr + ";"));

    let str = "";
    rows.forEach(row => {
        let line = "";
        let columns =
            typeof row !== "object" ? JSON.parse(row) : Object.values(row);
        columns.forEach(column => {
            if (line !== "") {
                line += ";";
            }
            if (typeof column === "object") {
                line += JSON.stringify(column);
            }  else {
                line += column;
            }
        });
        str += line + "\r\n";
    });
    return header + "\r\n" + str;
}

score 2 · Answer 15 · answered Aug 22 '19 at 18:59

Personally I would use d3-dsv library to do this. Why to reinvent the wheel?


import { csvFormat } from 'd3-dsv';
/**
 * Based on input data convert it to csv formatted string
 * @param (Array) columnsToBeIncluded array of column names (strings)
 *                which needs to be included in the formated csv
 * @param {Array} input array of object which need to be transformed to string
 */
export function convertDataToCSVFormatString(input, columnsToBeIncluded = []) {
  if (columnsToBeIncluded.length === 0) {
    return csvFormat(input);
  }
  return csvFormat(input, columnsToBeIncluded);
}

With tree-shaking you can just import that particular function from d3-dsv library

score 2 · Answer 16 · edited Jul 03 '20 at 17:16

I wanted to riff off @Christian Landgren's answer above. I was confused why my CSV file only had 3 columns/headers. This was because the first element in my json only had 3 keys. So you need to be careful with the const header = Object.keys(json[0]) line. It's assuming that the first element in the array is representative. I had messy JSON that with some objects having more or less.

So I added an array.sort to this which will order the JSON by number of keys. So that way your CSV file will have the max number of columns.

This is also a function that you can use in your code. Just feed it JSON!

function convertJSONtocsv(json) {
    if (json.length === 0) {
        return;
    }

    json.sort(function(a,b){ 
       return Object.keys(b).length - Object.keys(a).length;
    });

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

    fs.writeFileSync('awesome.csv', csv)
}

score 2 · Answer 17 · answered Jul 15 '20 at 20:55

Here's my simple version of converting an array of objects ito CSV (assuming those objects all share the same attributes):

var csv = []
if (items.length) {
  var keys = Object.keys(items[0])
  csv.push(keys.join(','))
  items.forEach(item => {
    let vals = keys.map(key => item[key] || '')
    csv.push(vals.join(','))
  })
}

csv = csv.join('\n')

score 1 · Answer 18 · edited Feb 08 '17 at 10:59

Write Csv.

function writeToCsv(dataToWrite, callback) {
    var dataToWrite;
    var fs = require('fs');
    dataToWrite = convertToCSV(dataToWrite);
    fs.writeFile('assets/distanceInfo.csv', dataToWrite, 'utf8', function (err) {
      if (err) {
        console.log('Some error occured - file either not saved or corrupted file saved.');
      } else{
        console.log('It\'s saved!');
      }
      callback("data_saved | assets/distanceInfo.csv")
    });
}

function convertToCSV(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]) {
            if (line != '') line += ','

            line += array[i][index];
        }
        str += line + '\r\n';
    }
    return str;
}

I don't think this has much value. Please [edit] to add some explanations on how this is helping to answer the question. — fedorqui, Feb 08 '17 at 10:33

score 1 · Answer 19 · answered Jun 14 '19 at 09:32

Funny nothing complete nor working here (IE nor node.js). Answer on similar question, a bit structured JSON (suppose no need to copy it again), also demo snippet included. JSON To CSV conversion (JavaScript) : How to properly format CSV conversion Hope not only single type convertor, also on my Github (mentioned in profile) is similar used to analyze unknow JSON structure. I am author of code in this answer and all code on my Github (except some projects started as fork/+translation).

score 1 · Answer 20 · answered May 31 '21 at 17:41

A simple function to convert nested JS objects (without arrays) to csv format in an inline and formatted style ..

const Obj2Csv = (data, level = 0) => (
  Object.keys(data).reduce((prevValue, currValue) => {
    if (typeof data[currValue] === 'object' && !Array.isArray(data[currValue])) {
      // format a deeper object
      const formattedObjProp = `${prevValue}${','.repeat(level)}${currValue}\r\n`;
      level++;
      const formattedObj = `${formattedObjProp}${Obj2Csv(data[currValue], level)}`;
      level--;
      return formattedObj;
    }
    return `${prevValue}${','.repeat(level)}${currValue},${data[currValue]}\r\n`
  }, '')
)

const obj = {
  baz: {
    foo: {
      bar: 5
    }
  }
};

console.log(Obj2Csv(obj))

score 1 · Answer 21 · answered Mar 22 '22 at 11:40

export function convertJsontoCSV(jsonData, fileName = "sheet.csv") {
    /*  *This function converts the jsonData into CSV and then downloads it
    *the jsonData is supposed to be array of Objects with similar structure  
    *the fileName should be provided otherwise it will set the default name as below.
    */

    /* The code that converts the jsonData into CSV data*/
    let json = jsonData
    let fields = Object.keys(json[0])
    let replacer = function (key, value) { return value === null ? '' : value }
    let csv = json.map(function (row) {
        return fields.map(function (fieldName) {
            return JSON.stringify(row[fieldName], replacer)
        }).join(',')
    })
    csv.unshift(fields.join(','))
    csv = csv.join('\r\n');

    /* The code that downloads the CSD data as a .csv file*/
    let downloadLink = document.createElement("a");
    let blob = new Blob(["\ufeff", csv]);
    let url = URL.createObjectURL(blob);
    downloadLink.href = url;
    downloadLink.download = fileName;  //Name the file here
    document.body.appendChild(downloadLink);
    downloadLink.click();
    document.body.removeChild(downloadLink);
}

The following function has been written with help from the answers above.

score 1 · Answer 22 · answered May 27 '22 at 04:59

Here is what I have done. I've used this function when I have an array of objects in JSON format. First of all, there are a lot of flavors of csv. So I've approached it this way, and my files seem to open up ok in spreadsheet editors. Adapted from RFC 4180 and MIME standards/wikipedia:

Each record should contain the same number of comma-separated fields.
Any field may be quoted (with double quotes).
If double-quotes are used to enclose fields, then a double-quote in a field must be represented by two double-quote characters. (internal " is escaped with "")
some type of carriage return/line feed

I know there are more complicated faster, and more elegant ways, but here is a readable and hopefully understandable function that will take in JSON and return a csv with those constraints.

Here is a rundown of the function, again not optimized for performance as it uses 2 passes.

Run through every array entry and get and collect all the key names on the first pass.
Make a header based on the key names
On a second pass, go through the entries and write the values using the keys.

If its undefined, don't write "undefined", instead write "". If its not a string, stringify it. (After all its valid JSON, so just use JSON.stringify.) This will take care of objects, arrays, null, boolean, etc. If it is a string do nothing of course.

Now replace any interior " with "".

Now wrap each entry in an outer pair of "" separated by commas with no spaces.

Don't forget the new line \n between lines.

The result is a string that should open in most spreadsheets easily.

function makeCSVFromJSON(myJSON) {

    //FIRST PASS// -- collect field names for csv table
    let fieldNamesArray = []; 
    for (arrayEntry of myJSON) {
        for (const field in arrayEntry) {
            if (!fieldNamesArray.includes(field)) {
                fieldNamesArray.push(field)
            };
        }
    }

    //make header with field names
    let csvString = "";
    for (field of fieldNamesArray) {
        field = field.replaceAll('"', '""'); //any interior " needs to be replaced with ""
        csvString += "\"" + field + "\","; //surround each field with quotes
    }
    csvString = csvString.slice(0, -1) + "\n"; //remove last comma and add new line

    //SECOND PASS -- fill in table using field names/keys
    for (arrayEntry of myJSON) {
        for (field of fieldNamesArray) {
            let csvEntry = arrayEntry[field];
            if (csvEntry === undefined) { //if undefined set to empty string ""
                csvEntry = "";
            } else if (typeof(csvEntry) != "string") { //if its not a string make it a string
                csvEntry = JSON.stringify(csvEntry);
            }
            csvEntry = csvEntry.replaceAll('"', '""');
            csvString += "\"" + csvEntry + "\"" + ","
        }
        csvString = csvString.slice(0, -1) + "\n";
    }

    return csvString;
}

score 1 · Answer 23 · answered Oct 07 '22 at 11:09

1

function jsonToCsv(data) {
  return (
    Object.keys(data[0]).join(",") +
    "\n" +
    data.map((d) => Object.values(d).join(",")).join("\n")
  );
}

This is of course not for nested json array. But to map json into csv, I would recommend first to simplify nested json array.

answered Oct 07 '22 at 11:09

Mukarram Ali

387
5
24

Elegant answer! – deadroxy Jul 28 '23 at 13:37

score 0 · Answer 24 · answered Oct 31 '21 at 02:44

Here is my solution, as no other one here has support for dynamic columns (they use the first row to determine the columns):

function toCsv(summary, replacer = (_, v) => v) {
    let csv = [[]]

    for (const data of summary) {
        let row = []
        for (const column in data) {
            let columnIndex = csv[0].indexOf(column)

            if (columnIndex === -1) {
                columnIndex = csv[0].length
                csv[0].push(column)
            }

            row[columnIndex] = replacer(column, data[column])
        }
        csv.push(row.join(","))
    }

    csv[0] = csv[0].join(",")

    return csv.join("\r\n")
}

You can pass a replacer function if you need to convert certain column' value.

score 0 · Answer 25 · answered Feb 02 '23 at 16:46

Typescript simple method taking into account:

✅ Keys/Values can have " inside it
✅ Keys/Values can have , inside it
✅ Values can have array or objects inside it

Playground Link

const arrayToCSV = (myJSON: any[]) => {
  const escapeValue = (value: any) => {
    const content = (() => {
      if (!value) {
        return "";
      }
      if (typeof value === "object") {
        return JSON.stringify(value).replace(/"/g, '""')
      }
      return value.toString();
    })()
    return `"${content}"`
  };
  
  const fieldNamesArray: string[] = [...new Set(
    myJSON.reduce((acc, arrayEntry) => {
      return [...acc, ...Object.keys(arrayEntry)];
    }, [])
  )] as any[];

  const header = fieldNamesArray.map(escapeValue).join(',');

  const rows = myJSON.map(arrayEntry => {
    return fieldNamesArray.map((field) => {
      return escapeValue(arrayEntry[field]);
    }).join(',');
  }).join('\n');

  return `${header}\n${rows}`;
};

Hope that helps :D

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

25 Answers25

Update ES6 (2016)

Linked

Related