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
– Pointy, Commented Mar 13, 2014 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
– Pointy, Commented Mar 13, 2014 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
– Fredkr, Commented Mar 13, 2014 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
– Pointy, Commented Mar 13, 2014 at 15:14
You should post your expected output. The output of this JSFiddle looks proper csv — shyam
– shyam, Commented Mar 13, 2014 at 15:15

Community · Accepted Answer · 2017-05-23 11:58:38Z

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, 2017 at 11:58

CommunityBot

11 silver badge

answered Mar 13, 2014 at 18:03

j.wittwer

9,4973 gold badges32 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pointy Over a year ago

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.

j.wittwer Over a year ago

Understood. But his comments clarify that a file is being created, and Excel isn't opening it as a CSV.

Fredkr Over a year ago

Thanks! if you take a look at my fiddle: 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?

j.wittwer Over a year ago

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.

Collectives™ on Stack Overflow

JSON to CSV with separated columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related