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

Question

提问by praneybehl

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?

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

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

我能找到的最接近的是：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.

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

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

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

I tried this code:

我试过这个代码：

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

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

    DownloadJSON2CSV(json3.items);

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

      var str = '';

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

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

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

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

</head>

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

</html>

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

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

Answer 1

采纳答案by praneybehl

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

普拉尼

Answer 2

回答by Christian Landgren

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

一种更优雅的将 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)

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)

更新 ES6 (2016)

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

使用这种不太密集的语法和 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])
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)

Answer 3

回答by Samuel Joy

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

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

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

Answer 4

回答by user1274820

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.

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

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

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

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

这使用 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);
    });
});

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

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

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

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

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

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

Answer 5

回答by nagu

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，那么你可以简单地调用：

d3.csv.formator d3.csv.formatRowsfunctions to convert an array of objects into csv string.

d3.csv.format或d3.csv.formatRows将对象数组转换为 csv 字符串的函数。

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

d3.csv.formatRows使您可以更好地控制将哪些属性转换为 csv。

Please refer to d3.csv.formatand d3.csv.formatRowswiki pages.

请参阅d3.csv.format和d3.csv.formatRows维基页面。

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

还有其他可用的库，例如jquery-csv、PapaParse。Papa Parse 没有依赖项——甚至没有 jQuery。

For jquery based plugins, please check this.

对于基于 jquery 的插件，请检查这个。

Answer 6

回答by waz

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.

如果有人也想下载它。
这是一个很棒的小函数，可以将一组 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);
};

Then call it like this:

然后像这样调用它：

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

Answer 7

回答by agravat.in

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

Answer 8

回答by YouBee

Try these Examples:

试试这些例子：

Example 1:

示例 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 :

示例2：

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 :

您甚至可以使用以下代码下载 csv 文件：

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

Answer 9

回答by Kyle Pennell

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.

我想重复@Christian Landgren 上面的回答。我很困惑为什么我的 CSV 文件只有 3 个列/标题。这是因为我的 json 中的第一个元素只有 3 个键。所以你需要小心这const header = Object.keys(json[0])条线。假设数组中的第一个元素具有代表性。我有一些杂乱的 JSON，有些对象或多或少。

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

所以我添加了一个array.sort，它将按键数对 JSON 进行排序。这样您的 CSV 文件将具有最大列数。

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

这也是您可以在代码中使用的函数。只需喂它 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)
}

Answer 10

回答by Nicola Munz slimxshady

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.

对于较新的 js 版本，这是一种以面向对象的方式为动态深层对象执行此操作的方法。您可能需要在区域之后更改分隔符类型。

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

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

提问by praneybehl

采纳答案by praneybehl

回答by Christian Landgren

Update ES6 (2016)

更新 ES6 (2016)

回答by Samuel Joy

回答by user1274820

回答by nagu

回答by waz

回答by agravat.in

回答by YouBee

回答by Kyle Pennell

回答by Nicola Munz slimxshady

相关推荐

最近更新

标签

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

提问by praneybehl

采纳答案by praneybehl

回答by Christian Landgren

Update ES6 (2016)

更新 ES6 (2016)

回答by Samuel Joy

回答by user1274820

回答by nagu

回答by waz

回答by agravat.in

回答by YouBee

回答by Kyle Pennell

回答by Nicola Munz slimxshady

相关推荐

Javascript 当用户名和密码在html中相等时如何创建登录页面

如何使用 php 和 javascript 从不同的域获取 cookie

Javascript 从javascript获取（移动）设备名称

Javascript 将数组转换为 Json

相关推荐

最近更新

标签