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

2019-01-02 22:00发布

站内问答 / JavaScript
1546 7 5

I have a link that opens up data in the broswer but unfortunately I have no clue how to read it. Is there a way to convert this data using Comma Separate () format and save it in a variable. I have just started learning and have absolutely no clue about , it would be great if someone could write a script for it.

The data seems 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 is 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.

Thanks in advance.

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?

7条回答
三岁会撩人
2楼-- · 2019-01-02 22:40

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

查看更多
0人赞 添加讨论(0) 举报
女痞
3楼-- · 2019-01-02 22:47

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;
}
查看更多
0人赞 添加讨论(0) 举报
别忘想泡老子
4楼-- · 2019-01-02 22:48

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

查看更多
0人赞 添加讨论(0) 举报
Lonely孤独者°
5楼-- · 2019-01-02 22:48

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 = 'UserExport.csv';
$("#lnkDwnldLnk")
.attr({
    'download': filename,
    'href': csvUrl
}); 

$('#lnkDwnldLnk')[0].click();    
document.body.removeChild(link);
}
查看更多
0人赞 添加讨论(0) 举报
聊天终结者
6楼-- · 2019-01-02 22:54

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.

查看更多
0人赞 添加讨论(0) 举报
老娘就宠你
7楼-- · 2019-01-02 22:56

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

console.log(csv.join('\r\n'))

Output:

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

Update ES6 (2016)

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

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

console.log(csv)
查看更多
0人赞 添加讨论(0) 举报
1 2 下一页
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)