如何将 JSON 转换为 CSV 格式并存储在变量中

IT技术 javascript json csv
2021-02-06 12:02:00

我有一个可以在浏览器中打开 JSON 数据的链接，但不幸的是我不知道如何阅读它。有没有办法使用 JavaScript 以 CSV 格式转换这些数据并将其保存在 JavaScript 文件中？

数据看起来像：

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

我能找到的最接近的是：Convert JSON format to CSV format for MS Excel

但是它下载到一个 CSV 文件中，我将它存储在一个变量中，即整个转换后的数据。

还想知道如何更改转义字符：'\u2019'恢复正常。

我试过这个代码：

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

但它似乎不起作用。有人可以帮忙吗？

6个回答

一种更优雅的将 json 转换为 csv 的方法是使用 map 函数，无需任何框架：

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)

输出：

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"

更新 ES6 (2016)

使用这种不太密集的语法和 JSON.stringify 为字符串添加引号，同时保持数字不带引号：

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)
您可以在 fields.map() 之后添加一个 + "\r\n" 以获得每行一条记录。要获得不加引号的数字，您可以使用 JSON.stringify(row[fieldName]) 代替它只引用字符串并保留不加引号的数字。
2021-03-19 12:02:00
值得注意的是，这会转义引号中的字符串，使用\"它允许某些字段在 Excel 中查看时“弹出”其列（似乎更喜欢""作为引号的转义字符）。这可以通过添加.replace(/\\"/g, '""')到JSON.stringify(row[fieldName], replacer)我在上面的回答中指出的末尾来解决。
2021-03-19 12:02:00
@marathon，Good catch，用替换器更新了示例以分别处理空情况。如果没有使用替换器，则 null 将输出为null- 现在示例应该正确处理 null、undefined 和 numbers。
2021-03-24 12:02:00
我喜欢它的简洁性，但应该注意的是，它不能处理一些可能认为理想的事情。例如，每个记录都在自己的行上，数字和布尔值未加引号等。
2021-03-31 12:02:00
@scunliffe ：我用 JSON.stringify 更新了一个新示例 - 它应该处理您描述的情况。
2021-04-07 12:02:00

好的，我终于让这段代码工作了：

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

非常感谢所有贡献者的支持。

普拉尼

@Sunil 我发现如果值包含逗号，它会中断。为了我的需要，我只是这样做了： var re = new RegExp(',', 'g'); 数组[i][索引] = 数组[i][索引].toString().replace(re, ';')
2021-03-12 12:02:00
我试过这个。我有三列，但在 excel 中所有的东西都将出现在一个列中
2021-03-13 12:02:00
感谢您在这里分享。刚刚使用它，它完美无缺。
2021-03-17 12:02:00
谢谢你！如果单元格包含对象，我添加了以下内容以避免在行中出现“[object Object]”。if (_.isObject(array[i][index])) { array[i][index] = JSON.stringify(array[i][index]); } . （使用下划线，但您可以更改为香草）
2021-03-31 12:02:00
Nithesh 你应该指定 ',' 作为分隔符
2021-04-09 12:02:00

praneybehl 非常好的解决方案，但如果有人想将数据保存为csv文件并使用一种blob方法，那么他们可以参考：

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);
}
这个解决方案有效，但有一些奇怪的地方——你定义了var row两次（if 语句和 for 循环不创建闭包）。此外，标签/标题循环可能会减少到一行：Object.keys(arrData[0]).join(',')
2021-03-20 12:02:00
你的答案是有效的。但是对于诸如某些列对于某些行不可用的情况，它不会考虑丢失的列并且不会为该行数据重新对齐列数据。
2021-03-23 12:02:00
我能够让这个方法工作，但我不得不编辑一些代码： 1. 在没有 JQuery 的情况下document.getElementById("lnkDwnldLnk").download = filename; document.getElementById("lnkDwnldLnk").href = csvUrl;工作： 2. 在 IE11 中工作： if (window.navigator && window.navigator.msSaveOrOpenBlob) { window.navigator.msSaveOrOpenBlob(blob, filename); } else { document.getElementById('lnkDwnldLnk').click(); }
2021-03-30 12:02:00

我只是想在这里为将来的人们添加一些代码，因为我试图将 JSON 导出到 CSV 文档并下载它。

我$.getJSON过去常常从外部页面中提取 json 数据，但是如果您有一个基本数组，则可以使用它。

这使用 Christian Landgren 的代码来创建 csv 数据。

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

编辑：值得注意的是，JSON.stringify将通过添加\". 如果您在 excel 中查看 CSV，它不喜欢将其作为转义字符。

您可以添加.replace(/\\"/g, '""')到年底JSON.stringify(row[fieldName], replacer)在Excel这个正常显示（这将取代\"以""这是Excel中喜欢什么）。

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

可能只是我，但我得到了 TypeError: Cannot read property 'replace' of undefined
2021-03-25 12:02:00
如果JSON.stringify正在返回，undefined那么您可能会遇到更大的问题？没有.replace线路，您是否正在返回数据？
2021-03-26 12:02:00
@KylePennell 没错，我也明白了，我真的需要他的修复。
2021-04-10 12:02:00

如果有人也想下载它。
这是一个很棒的小函数，可以将一组 JSON 对象转换为 csv，然后下载它。

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

然后像这样调用它： 

this.downloadCSVFromJson(`myCustomName.csv`, this.state.csvArrayOfJson)
当元素之一中有单引号时，这似乎不起作用，例如 Cap D'antibes
2021-03-28 12:02:00
其它你可能感兴趣的问题
上一篇jQuery：获取 jQuery 中隐藏元素的高度 下一篇Javascript 中的几乎所有东西如何都是对象？