Tutorials & Cool stuff

Export your JSON data to CSV format

JSON format is very convenient when dealing with web applications. When you are in more traditional grounds, though, it might not be the most recommended. In this tutorial I’ll show how you can easily convert data pulled out from your MongoDB into a CSV file in Meteor.

Parsing (or unparsing, as they called) the data did work but in an inflexible way. For example, when dealing with embed documents you would end up with this:

So it doesn’t work out-of-the-box, but let’s not blame the poor guys from Papa Parse! There’s a pretty simple workaround for it. In this tutorial we will build a contacts application (I feel like I need to be more creative) that has some data and options to export either all your contacts or a single one in the convenient CSV format. To make things a little tougher we will deal with embed documents and data manipulation.

1. Creating the Project

You have now successfully created your Meteor project and added the Papa Parse, MomentJS (we will need it to format our date) and Iron Router packages. That should be enough for our purposes today.

Remove the files automatically generated by Meteor and create three folders: client, both and server.

2. Adding Some Data

In your both/ folder, create collections.js and add the following:

collection.js

JavaScript

1

Contacts=newMeteor.Collection("Contacts");

We will not explicitly set the schema (you might want to use aldeed:simple-schema) but let’s define our contacts will have: name, gender, address (simplified to just store country and city) and createdAt.

Create a CRUD interface is beyond the purpose of this tutorial. No shame on it. Go to server/, create init.js and add:

init.js

JavaScript

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

Meteor.startup(function(){

if(Contacts.find().count()===0){

Contacts.insert({

name:"Rafael",

gender:"Male",

address:{

country:"Brazil",

city:"Rio de Janeiro"

},

createdAt:newDate

});

Contacts.insert({

name:"John",

gender:"Male",

address:{

country:"Australia",

city:"Melbourne"

},

createdAt:moment(newDate).subtract(1,"days")._d

});

Contacts.insert({

name:"Marie",

gender:"Female",

address:{

country:"France",

city:"Paris"

},

createdAt:moment(newDate).subtract(7,"days")._d

});

}

});

The only trick here is to use moment to create different dates — for the sake of making the data less boring. Follow their documentation if you want to learn more.

3. Designing the Application

Now that we have data it’s time to play with it. In clients/ create two files: main.html and main.js (not the best practice, I know, but let’s stick to our needs here). In main.html:

The code should be straightforward. We define two templates, contacts and contact that will show respectively all contacts in our collection and a single one. If you are not familiar with Iron Router, go check their documentation.

Of course we haven’t defined the helpers. In main.js:

main.js

JavaScript

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Template.registerHelper("formatDate",function(date){

returnmoment.utc(date).format("DD/MM/YYYY");

});

Template.contacts.helpers({

contacts:function(){

returnContacts.find({},{name:1});

}

});

Template.contact.helpers({

contact:function(){

returnContacts.findOne(Router.current().params._id);

}

});

Note the registerHelper method we use to avoid duplicate the formatDate(date) function. Nice.

Last piece of the puzzle, time for set the router up. In both/, create a router.js file and add the following:

router.js

JavaScript

1

2

3

4

5

6

7

8

Router.configure({

layoutTemplate:'layout'

});

Router.map(function(){

this.route('contacts',{path:'/'}),

this.route('contact',{path:'contact/:_id'})

});

That’s all. Experiment to run your application and see everything is alright. You should see a list with all names in a table and a hyperlink that shows more details about that contact. Sounds like it’s time for doing some exportation.

4. Export!

Papa Parse is client-side so you will need all your JSON data in there. That’s no big deal – you would download it anyway. Nevertheless, you might want data that it’s not in the client. For example, our contacts template only show the name of the contact. Imagine the scenario when we are using subscriptions and that’s the only data available – you would have to request the remaining from the server.

All good? Now note that apart from the server gathering data, the rest of the process is the same for any export you might do. To avoid code duplication, let’s create a global helper which handles that for you. There’s many ways of achieving and it’s a matter of flavor. In this tutorial, create a file exporter.js in client/ with the following code:

exporter.js

JavaScript

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

MyAppExporter={

exportAllContacts:function(){

varself=this;

Meteor.call("exportAllContacts",function(error,data){

if(error){

alert(error);

returnfalse;

}

varcsv=Papa.unparse(data);

self._downloadCSV(csv);

});

},

exportContact:function(id){

varself=this;

Meteor.call("exportContact",id,function(error,data){

if(error){

alert(error);

returnfalse;

}

varcsv=Papa.unparse(data);

self._downloadCSV(csv);

});

},

_downloadCSV:function(csv){

varblob=newBlob([csv]);

vara=window.document.createElement("a");

a.href=window.URL.createObjectURL(blob,{type:"text/plain"});

a.download="contacts.csv";

document.body.appendChild(a);

a.click();

document.body.removeChild(a);

}

}

The first two functions are pretty alike. They call a Meteor method and wait for the data to unparse them using Papa Parse. Once you have the CSV string a private function is called and the download starts. A note: downloading a file via browser might raise some cross-browser issues. The above solution works well both in Chrome and Firefox (thanks to @mholt in this post).

Alright, now we switch roles and dive deep into the server realm. Make sure you are in server/ and create a file called export.js. Before adding any content, let’s understand how Papa works.

There are many ways to parse a JSON data to CSV. The one I liked most was the third example in the previous link. This way, you pass an object that contains an array with the fields name and after an array of arrays with the proper data. Using this approach gives us the flexibility to iterate over embed documents and format any data.

Enough of talking, add the following:

export.js

JavaScript

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

Meteor.methods({

exportAllContacts:function(){

varfields=[

"Name",

"Gender",

"Date of Affiliation",

"Country",

"City"

];

vardata=[];

varcontacts=Contacts.find().fetch();

_.each(contacts,function(c){

data.push([

c.name,

c.gender,

moment.utc(c.createdAt).format("DD/MM/YYYY"),

c.address.country,

c.address.city

]);

});

return{fields:fields,data:data};

},

exportContact:function(id){

varfields=[

"Name",

"Gender",

"Date of Affiliation",

"Country",

"City"

];

vardata=[];

varc=Contacts.findOne(id);

data.push([

c.name,

c.gender,

moment.utc(c.createdAt).format("DD/MM/YYYY"),

c.address.country,

c.address.city

]);

return{fields:fields,data:data};

}

});

Note that once we are on the server any query can be made to our MongoDB. In particular we are retrieving all fields from Contacts, even though only the name was displayed before. To satisfy our requirements, we are now able to access embed documents (c.address.country) and apply some function to the data (for example, formatting dates).

Finally, let’s make the buttons alive! Go back to main.js and add to the bottom:

main.js

JavaScript

1

2

3

4

5

6

7

8

9

10

11

12

13

...

Template.contacts.events({

"click #export":function(){

MyAppExporter.exportAllContacts();

}

});

Template.contact.events({

"click #export":function(){

MyAppExporter.exportContact(Router.current().params._id);

}

});

And that’s it. You should have an application that exports data pulled out from your MongoDB in a nice CSV file well suited for integration with other programs (think Excel).

10 Comments on “Export your JSON data to CSV format”

Nice tutorial. Too bad PapaParse is all client side; seems like it should be able to run server-side, maybe I’ll take a look.

Not that it particularly matters for this app, but your export.js should be refactored so that exportAllContacts calls exportOneContact. Just have exportAllContacts only get the _id field from the database to avoid pulling the full record twice.

The array of fields is then only needed once (in exportOneContact) as the push() call which specifies the fields to push. Having them in two places (in a real app, that is), could be a big problem later when you forget to make a change one place or the other.

You are right. Indeed, when I used it in a real app, I did exactly how you said. For the sake of this tutorial, I tried to make it simple for the understanding of the process (exporting data). Well observed, though. Thanks!

This worked great for me, but it’s only working in Chrome and Firefox… no Safari, IE, or MS Edge. I added silentcicero:saveas and the download works in IE, MS Edge, and in Safari it doesn’t download as a file but it opens as text in the browser and it can be saved.