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

Asked 13/1, 2012 at 8:29 Answered 11/2 at 18:56

171

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?

Kipper answered 13/1, 2012 at 8:29 Comment(7)

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 – Taber 13/1, 2012 at 8: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), ... ? – Wharve 13/1, 2012 at 8: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":[:" – Kipper 13/1, 2012 at 8:52

@Taber yes but for some reason I am not able to get it to work. – Kipper 13/1, 2012 at 8:54

@Taber thanks for the link and the help mate. Don't know where your comment went. In your link 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. – Kipper 13/1, 2012 at 10:23

I deleted them, i realized that i didn't checked your json object. Missed my eye. – Taber 13/1, 2012 at 10:35

Have you tried data.page/json/csv ? This is what I use if I ever need to convert json to csv. (Disclaimer: this is not my website) – Hoopoe 22/6, 2021 at 23:37

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

Kipper answered 19/1, 2012 at 10:56 Comment(5)

I tried this . i have three columns but in excel all things will be coming in a single column – Overmeasure 16/8, 2012 at 9:10

Nithesh you should specify ',' as a delimiter – Endor 30/6, 2013 at 8:17

Thanks for sharing this here. Just used it and it works perfect. – Moritz 3/6, 2016 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) – Aprylapse 14/12, 2017 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, ';') – Aprylapse 14/12, 2017 at 19:19

292

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)

Tryst answered 21/7, 2015 at 10:27 Comment(13)

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. – Attar 12/8, 2015 at 12:32

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. – Tryst 24/11, 2015 at 20:27

@Attar : I updated a new example with JSON.stringify - it should handle the cases you described. – Tryst 8/6, 2016 at 12:36

the line return JSON.stringify(row[fieldName] || ''); will print zeros as "" – Cuckooflower 19/10, 2016 at 19:0

@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. – Tryst 20/10, 2016 at 7: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? – Leroi 8/8, 2017 at 20:15

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? – Alyciaalyda 9/8, 2017 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. – Tryst 20/8, 2017 at 7:38

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. – Doralin 3/10, 2018 at 22:54

In case you don't want the headers in the csv: console.log(csv.substring(csv.indexOf("\n") + 1)) – Vanesavanessa 26/3, 2020 at 13:33

@ChristianLandgren: Is this way of creating json to csv using client side js code safe from CSV injection vulnerability ? – Dactylology 28/4, 2021 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, paragraph 2.7. – Deplume 10/12, 2021 at 19:5

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. – Matherne 25/1, 2022 at 2:7

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

Kipper answered 19/1, 2012 at 10:56 Comment(5)

I tried this . i have three columns but in excel all things will be coming in a single column – Overmeasure 16/8, 2012 at 9:10

Nithesh you should specify ',' as a delimiter – Endor 30/6, 2013 at 8:17

Thanks for sharing this here. Just used it and it works perfect. – Moritz 3/6, 2016 at 20:38

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);
}

Oneidaoneil answered 29/7, 2014 at 18:8 Comment(3)

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(',') – Vituperate 27/4, 2015 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. – Akira 21/5, 2015 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(); } – Bristol 29/8, 2017 at 15:0

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)

Competent answered 9/4, 2019 at 2:55 Comment(2)

this doesn't seem to work for things when there is a single quote in one of the elements e.g. Cap D'antibes – Doddering 11/6, 2020 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. – Shericesheridan 12/7, 2022 at 16:2

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(','));

Doralin answered 2/2, 2018 at 16:46 Comment(5)

could just be me but I got TypeError: Cannot read property 'replace' of undefined – Instable 24/1, 2021 at 22:42

@KylePennell Exactly, I get this as well and I really need his fix. – Mi 7/4, 2021 at 13:28

If JSON.stringify is returning undefined then you may have bigger issues? Without the .replace line, are you getting data returned? – Doralin 7/4, 2021 at 14:3

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. – Insure 26/4, 2022 at 21:49

For JSON.stringify is returning undefined, you can use const replacer = (key, value) => value ?? "--"; – Currycomb 28/9, 2023 at 10:46

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.

Court answered 25/8, 2017 at 6:48 Comment(2)

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

Thanks! I used PapaParse's Papa.unparse(data) (papaparse.com/docs#json-to-csv). Quick fix to my issues! – Genetic 8/11, 2020 at 16:53

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();
}

Bricklaying answered 4/9, 2019 at 12:38 Comment(3)

How did this work for 50k- 1lakh records. – Coulombe 27/6, 2022 at 7:3

@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. – Isiah 6/7, 2022 at 17:42

suggestion working Prasanna – Coulombe 7/7, 2022 at 9:28

<!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>

Wow answered 4/7, 2018 at 6:42 Comment(0)

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);
};

Mimas answered 1/2, 2022 at 6:8 Comment(0)

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>

Selfassurance answered 10/2, 2022 at 10:10 Comment(1)

I am trying this type of input json and getting error. Please help to resolve the issue. [ { "obj-3": { "test": "url" } }, { "obj-3": "hello" } ] – Richelieu 21/11, 2022 at 8:36

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

Stunt answered 16/6, 2020 at 21:45 Comment(0)

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://mcmap.net/q/63772/-how-to-remove-all-line-breaks-from-a-string
}

Himyarite answered 26/6, 2020 at 21:6 Comment(1)

This works perfect for copying and pasting straight into Excel/Sheets! Thanks! – Kaleighkalends 26/6, 2020 at 21:9

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>

Kallick answered 26/2, 2021 at 20:32 Comment(0)

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

Bentinck answered 15/2, 2019 at 9:41 Comment(0)

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

Lintel answered 22/8, 2019 at 18:59 Comment(0)

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

Instable answered 28/9, 2019 at 15:55 Comment(0)

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

Raincoat answered 15/7, 2020 at 20:55 Comment(0)

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.

Pancreatin answered 22/3, 2022 at 11:40 Comment(0)

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

Malversation answered 6/2, 2017 at 4:57 Comment(1)

I don't think this has much value. Please edit to add some explanations on how this is helping to answer the question. – Allegro 8/2, 2017 at 10:33

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

Thermography answered 14/6, 2019 at 9:32 Comment(0)

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

Spew answered 31/5, 2021 at 17:41 Comment(0)

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

Christiansen answered 27/5, 2022 at 4:59 Comment(0)

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.

Readjust answered 7/10, 2022 at 11:9 Comment(1)

Elegant answer! – Von 28/7, 2023 at 13:37

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.

Pesce answered 31/10, 2021 at 2:44 Comment(0)

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

Schmidt answered 2/2, 2023 at 16:46 Comment(0)

Python data science way:

import pandas as pd

df = pd.read_json("./data.json")
df.to_csv("./data.csv")

Leaseback answered 7/11, 2023 at 16:52 Comment(0)

Not exactly what the op asked for, but could be helpful. I have this function in my ~/.zshrc It retrieves the json from an url and saves it as csv, thanks to jq. you might have to install jq with brew install jq

json_to_csv() {
  url=$1
  filename=$2

curl -s "$url" | jq -r '(.data[0] | keys_unsorted) as $headers | $headers, (.data[] | [.[$headers[]]]) | @csv' > "$filename"
}

# Usage: json_to_csv <url> <filename>
# when using '?' for a query parameter in the url, make sure to use single or double quoates around the url

Roundhouse answered 11/2 at 18:56 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Update ES6 (2016)

Recommended topics

Hot tags