exporting Mendeley tags to csv or xls

I recently was struggling with a data problem, one that I hadn’t anticipated being so … annoying. I eventually found a solution, and thought I would share it so others wouldn’t have to go through the same thing that I did. [Update: I have a link to the files that I made at the bottom of the post.]

One of the projects that I am working on is a huge meta-analysis of research studies surrounding simulations used for learning STEM content, inquiry, and related skills. I am leading a team of researchers in this task (some of whom are at a different institution), and initially tried to figure out the best way to collaborate in reading and coding thousands of article abstracts. I decided to go with Mendeley, mostly because I knew it would be easy to get the citations into it, it’s free, it works really well, and I could set up collaborative groups to facilitate our coding process. What I hadn’t anticipated would be how difficult it would be to get the citations out of Mendeley and into a spreadsheet (which I was using for analysis of the abstract data and the next steps of our overall project). Specifically, I needed to get the tags in Mendeley out, since we were using the tag field for our coding.

[Sidenote: Using tags to code the abstracts in Mendeley actually worked pretty well in my opinion.]

Mendeley, while awesome in many respects, does not have many options for exporting data. You can do BibTeX (.bib), RIS (.ris), or EndNote XML (.xml). Through some sleuthing, I managed to figure out that BibTeX was probably my best bet as the tags were definitely part of that export (they didn’t seem to be in the xml file and I didn’t really want to deal with RIS if I didn’t have to). Now, of course, the new problem is that BibTex isn’t a spreadsheet and it’s only readable by other citation management software (and there’s a good reason why I don’t use EndNote anymore). So I still needed to get it into a csv type format. Luckily, through some interwebz searching, I found the program JabRef, which is an open source citation manager and is able to export to csv. Hooray!

However, my problems still weren’t quite over, as the default csv export filter didn’t include the tags that I wanted. Although free, open source programs are great in a lot of ways, they are usually not so great in the documentation of certain operations. And this was one of those situations.

So here’s how to make a custom csv export filter for JabRef that includes the tags that Mendeley uses. [Update: you can skip this step and just download the files I made – links at the bottom of the post.]

First, download the src files. This includes the the standard export filter layouts that you can then edit and tweak to your heart’s content. They can be found here: http://sourceforge.net/projects/jabref/files/jabref/ Open up the file openoffice-csv.layout (in your text editor of choice) and decide which fields you want to be included in your export to csv. “\mendeley-tags” is the correct way to refer to the Mendeley tags in the BibTeX file. My suggestion is to definitely delete the “\annote” field (especially, if like me, you had a bunch of random crap in the Note field in Mendeley, some of which took up a lot of space). You can of course change the order of these and delete other fields as well. After going through the rest of this process, I would have taken out more fields due to the regular expressions weirdness that came out the other side when there were strange symbols or characters (which happened a lot in the author and abstract fields – more on that later). If you don’t need the address, I would suggest deleting that since there were a bunch of formatting issues with it in the csv version.

You can also edit the openoffice-csv.begin.layout file to change the headings for the csv export – and obviously, it’s easier later on if you have ensured that the data and the headings match up. :) Make sure to rename your edited files (I called mine custom-csv.layout and custom-csv.begin.layout) and then save them somewhere convenient.

Then go back to JabRef, select Menu Options – Manage custom exports – Add new. Fill in the fields: export name – call it whatever you want; main layout file: navigate to your saved .layout file; file extension: .csv. Click Ok and Close. Then go to Menu – File – Export and your newly defined export filter should be listed there.

Now, this solution is not perfect by any means. There were definitely lots of formatting and regexp hiccups in the transformation and export processes. I’m not sure where these occurred and I’m not skilled enough at regexp to fix them. So, I had to go through my csv file and correct a lot of the issues (the concatenate function in Excel was exceedingly helpful in this regard). But, in the end I got all 2392 of my citations into an xls format and can now manipulate my data with ease.

[Update: Here are the files that I created. This is the .layout file and this is the begin.layout file.]

Author: cynthiadangelo

I am a researcher, working on educational games, science education, and data visualization. I like photography, soccer, traveling, and teaching my dog new tricks.

10 thoughts on “exporting Mendeley tags to csv or xls”

  1. I am working on a smaller but similar project (a comprehensive literature review to define the scientific reasoning) and am using Mendeley in a similar way. This post was extremely helpful! Thanks!

  2. Hi! Great work on modifying the export filter. Its a shame that mendeley/ zotero doesnt allow us to directly customize their export filters!
    For the reviews I have done, I export all the reference details to a MS Access from EndNote using a tab delimited text file. But this has problems of its own, starting from the problem of the line breaks within the abstract, which causes the rest of the abstract to go into a new line.
    Once I get the clean data into access, I maintain coding records independent of the records and do the screening using a form.

  3. Hi, thank you for this nice tutorial. It worked great.

    I personally needed to import the tags in R after that, and there was a wonderful function which allowed that without any problems : fread from package data.table

    library(data.table)
    bibliography <- fread("myfile.csv", colClasses = "character")

    and the job was done!

  4. This was super super helpful! Instructions were clear and I couldn’t find this on Mendeley website but here it was, explained so well! Very easy steps and I got it done very quickly.

    Thank you!

  5. Hi I had a the same problem and solved it a different way, posting it up here in case anyone else finds it useful. It uses R which can be found here https://www.r-project.org/ you will also want to install Rstudio. Both these programs are free.

    #step 1 – export a .bib file from mendeley and call it MyExport.bib

    #then run the following code in R (with modification for file path)

    install.packages(“RefManageR”)
    library(RefManageR)

    setwd(“/Users/user/Documents/r”) #change to a suitable file path,
    #this is for a mac for PC you’ll need
    #to modify this

    MyExport<-ReadBib("MyExport.bib",check = FALSE)
    write.csv(MyExport,"MyExport.csv")

    # then work on the MyExport.csv file in r, excel or your program of choice.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s