Convert JSON format to CSV format for MS Excel

Question

I received a JSON file but don't know how to read it. Is there a converter where I can produce a nice CSV file so it can be loaded into MS Excel? I don't understand JSON, so it would be awesome if someone wrote a script or link me to one that would do the job.

I found something close at http://json.bloople.net but, unfortunately, it's JSON to HTML.

Edit: jsonformat.com gets even closer, however it's still not CSV.

there is a reason. CSV is not hierachical/structured/wellformed, JSON/HTML/XML is. do you just want to get a massive string? (not caring about the ordering)? — RPM1984, Nov 09 '10 at 05:53
I just need something that can be read on a table format, I thought using CSV was the right way since I can just open it up on Excel and start editing the data. — stockoverflow, Nov 09 '10 at 05:58
it is worth noting that not all JSON can reliably be converted to CSV. Here are some examples of JSON that could not: http://json.org/example.html It sounds like the structure of the JSON you're doing with is relatively simple, however. — Amanda, May 21 '11 at 17:38
[json-csv.com](https://json-csv.com) converts JSON to CSV online — Stack Man, May 23 '15 at 21:09
this app can be used https://www.microsoft.com/en-us/store/p/json-into-spreadsheet/9nvgjblt01mt — Pomodoro Technique Game, Nov 03 '17 at 15:05

score 71 · Accepted Answer · edited Aug 27 '12 at 16:49

71

I'm not sure what you're doing, but this will go from JSON to CSV using JavaScript. This is using the open source JSON library, so just download JSON.js into the same folder you saved the code below into, and it will parse the static JSON value in json3 into CSV and prompt you to download/open in Excel.

<!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="scripts/json.js" type="text/javascript"></script>
    <script type="text/javascript">
    var json3 = { "d": "[{\"Id\":1,\"UserName\":\"Sam Smith\"},{\"Id\":2,\"UserName\":\"Fred Frankly\"},{\"Id\":1,\"UserName\":\"Zachary Zupers\"}]" }

    DownloadJSON2CSV(json3.d);

    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] + ',';
            }

            // Here is an example where you would wrap the values in double quotes
            // for (var index in array[i]) {
            //    line += '"' + array[i][index] + '",';
            // }

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

            str += line + '\r\n';
        }
        window.open( "data:text/csv;charset=utf-8," + escape(str))
    }

    </script>

</head>
<body>
    <h1>This page does nothing....</h1>
</body>
</html>

edited Aug 27 '12 at 16:49

the Tin Man

150,910
39
198
279

answered Nov 09 '10 at 06:05

Zachary

6,452
20
32

1

Sorry, not really a programmer here... how do I 'use' this? – stockoverflow Nov 09 '10 at 06:11
1

Sorry... please bear with me... which program do I paste this code into? Does it have to be in the same directory as the JSON files? – stockoverflow Nov 09 '10 at 06:49
1

This is javascript, you can just paste this into a web page and use it to convert JSON to CSV... If you want it to save into a file, you can replace "return str;" with "window.open( "data:text/csv;charset=utf-8," + escape(str);". – Zachary Nov 09 '10 at 07:07
The above code does not seem to handle the case where json value has "," in it. eg."Sam, Smith". Maybe someone could confirm this. – Michael Z Apr 12 '12 at 05:46
If you want to support embedded comma's like "Sam, Smith", you'll need to embed quotes around the value. – Zachary Jun 27 '12 at 17:37
@Zachary what if the json is a lil bit complex http://stackoverflow.com/questions/11257062/converting-json-object-to-csv-format-in-javascript – user1371896 Jun 29 '12 at 09:43
@Zachary to ensure that resulting CSV is valid, one should also escape quotes if one of the values happens to contain some – Konrad Morawski Aug 27 '12 at 17:04
1

Probably makes sense to use a javascript CSV library like http://agnes.codeplex.com/ or https://code.google.com/p/jquery-csv/ than to roll your own CSV writing script. – mooreds Mar 03 '13 at 21:48
Wow, that "window.open( "data:text/csv;charset=utf-8," + escape(str))" line, it's the healing. I was looking for something to do this and gave up and now, I'm ungiving up. – Irwin Apr 20 '13 at 17:43
@Zachary, this is good! The file is always .part, have you a solution to that? – noneJavaScript May 14 '13 at 14:23
The line window.open( "data:text/csv;charset=utf-8," + escape(str)) throws a Uncaught TypeError in Chrome. is says Property 'open' of object [object global] is not a function. Does anyone have a fix for this or know why it is happening? – jordan Nov 08 '13 at 15:49
@Zachary hi, using this example the output file is just download, how can i change this? it wont be a .csv file – pato.llaguno May 28 '15 at 20:13
This works for me, but when I have special characters like Č,Ć,Š it doesn't work. I get something like "%u017D" instead of them. Do you have any idea how to make this code to works with special characters? Thanks. – Milan Poznan Dec 26 '18 at 12:48

score 64 · Answer 2 · edited May 23 '17 at 12:02

64

I created a JsFiddle here based on the answer given by Zachary. It provides a more accessible user interface and also escapes double quotes within strings properly.

edited May 23 '17 at 12:02

Community

1
1

answered Sep 06 '12 at 17:05

Joseph Sturtevant

12,486
11
69
88

1

I forked it to support missing key-value pairs http://jsfiddle.net/gonzif/MfJPF/1/ Your first JSON object is used to define the column keys. – Gon Zifroni Oct 10 '13 at 19:25
2

those are cool, but neither support json arrays. so it doesn't handle [ { "Id":1, "UserName":"Sam Smith", "Details":{ "hair":"red", "eyes":"red" } }] – Dusty Mar 12 '14 at 09:15
1

Also, when I click download then that start downloading with filename "download" so there has no extension of the file. So Would you please let me know how I can rename the file. – Jewel Jul 09 '14 at 00:10
It didn't work for me – Luffydude Jul 31 '17 at 15:04
When clicked on download in the fiddle, it doesn't download with a proper extension (.csv). Could you please help on what change needs to be done ? – coderpc Jan 15 '18 at 22:34

score 1 · Answer 3 · answered Jan 15 '13 at 15:33

1

You can use that gist, pretty easy to use, stores your settings in local storage: https://gist.github.com/4533361

answered Jan 15 '13 at 15:33

Palesz

2,056
18
19

I don't understand what you want me to type in the boxes "header", "extract items", or "Convert item", nor what you want me to change in the source code. – Noumenon Dec 17 '14 at 05:53

score 1 · Answer 4 · edited Sep 18 '18 at 19:19

1

Using Python will be one easy way to achieve what you want.

I found one using Google.

"convert from json to csv using python" is an example.

edited Sep 18 '18 at 19:19

benhorgen

1,651
1
30
32

answered Nov 09 '10 at 05:53

chinuy

135
11

1

I have never used Python before, I only know that its a programming language, any alternatives? – stockoverflow Nov 09 '10 at 06:00
You mentioned earlier that you could convert JSON to HTML. Excel is able to open HTML. Have you tried that? – Henrik Nov 09 '10 at 09:31
The following [EE Article](http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/A_10413-Using-Perl-to-preview-in-a-Web-Page-and-convert-a-JSON-document-into-an-Excel-workbook.html) is Perl based, but may give you a few ideas, as it will hapily display a JSON file as a Dojo based Grid, or convert it to XLS or XLSX. There's also a Java based solution [json2excel](http://sourceforge.net/projects/json2excel/) vvailable on sourceforge. – arober11 Nov 24 '12 at 12:18

Convert JSON format to CSV format for MS Excel

4 Answers4

Linked