JSON to CSV with separated columns

Question

In an angular project im trying to implement a function to generate a CSV file from an array of objects.

The array of objects populates a ng-Table so at first i tried to use: http://bazalt-cms.com/ng-table/example/15 This worked great in chrome, in IE it doesnt work at all though because it needs to use the download attribute...

Then i tried this approach

     var objArray = [
         { name: "Item 1", color: "Green", size: "X-Large" },
         { name: "Item 2", color: "Green", size: "X-Large" },
         { name: "Item 3", color: "Green", size: "X-Large" }];

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

But, now all the data of a row is in one single column. I want need the data on seperate columns.

Expected output (once opened in Excel)

enter image description here

Anyone got an idea how to do this?

What's the purpose of converting to JSON and then back again? — Pointy, Mar 13 '14 at 14:51
Well that code works just fine, as far as I can tell. Be warned that you (strictly speaking) can't rely on the order of properties in objects. Depending on the "life history" of each object in the array, you won't necessarily get the properties in the same order ("name", "color", "size" could be shuffled, in other words). — Pointy, Mar 13 '14 at 15:08
It creates a csv, thats not the problem. The problem is the format of the outputed csv. I need name to be in one column, color in a second and size in a third. Atm they are all in the same column — Fredkr, Mar 13 '14 at 15:12
Not when I run it; there are commas between them. Maybe I don't know what you mean by "column". What are you doing with the string after that bit of code? — Pointy, Mar 13 '14 at 15:14
You should post your expected output. The output of this [JSFiddle](http://jsfiddle.net/37YBq/) looks proper csv — shyam, Mar 13 '14 at 15:15
The problem is that the code you posted here does not explain your problem. After that code runs, "str" contains CSV for that data. The important thing is what happens to it *after* this code - how dies it get from that JavaScript variable into Excel? — Pointy, Mar 13 '14 at 15:25
I see. In the project im trying to implement this we are using a .swf ActionScript i cant begin to understad.. But, lets say i want to use the windows.open method, like this http://jsfiddle.net/THkAC/ Is it at all possible? — Fredkr, Mar 13 '14 at 16:00
Well, [see this question.](http://stackoverflow.com/questions/7405345/data-uri-scheme-and-internet-explorer-9-errors) Looks like Internet Explorer is pretty restrictive about data URIs. — Pointy, Mar 13 '14 at 16:05
You should try adding "sep=," to the start of the file. it forces excel to open it as a csv. see updated answer. — j.wittwer, Mar 14 '14 at 05:51

score 3 · Accepted Answer · edited May 23 '17 at 11:58

3

This looks like an Excel question.

But, now all the data of a row is in one single column. I want need the data on seperate columns... (once opened in Excel)

You are creating the file like this: Convert JSON format to CSV format for MS Excel

The problem is that your file doesn't have a csv extension. Instead of double clicking the file or allowing IE to automatically open it, open Excel and choose File > Open. Browse to the file and click Open. This will invoke the Text Import wizard where you can specify comma as the delimeter.

Update
You can tell Excel how to process the file by adding "sep=," to the first line. In your code, add the following line after your loop:
str = 'sep=,\r\n' + str;

If you open the file in Notepad, it will look like this:
sep=,
name,color,size
Item 1,Green,X-Large
Item 2,Green,X-Large
Item 3,Green,X-Large

However, when the file is opened in Excel, only the headers and data will be present:
csv opened in Excel

Here is a demo, forked from Joseph Sturtevant's fiddle posted in the answers to the related question: http://jsfiddle.net/wittwerj/6JySt/

edited May 23 '17 at 11:58

Community

1
1

answered Mar 13 '14 at 18:03

j.wittwer

9,436
3
28
32

He's not creating a file. He's creating a JavaScript string inside a web page. The string is in the form of a CSV file. – Pointy Mar 13 '14 at 21:06
Understood. But his comments clarify that a file is being created, and Excel isn't opening it as a CSV. – j.wittwer Mar 13 '14 at 21:59
Thanks! if you take a look at my fiddle: http://jsfiddle.net/THkAC/3/ If the file is opened in excel i get the output im looking for. The last issue is that it still doesnt have a .csv file ending, any idea how to fix that as well? – Fredkr Mar 14 '14 at 10:45
I suggest accepting this answer, and posting another question. You can include all of the details about your environment, and ask specifically how to create a file with CSV extension. – j.wittwer Mar 14 '14 at 13:10

JSON to CSV with separated columns

1 Answers1