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.]
Cynthia D'Angelo 
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!
Thanks Keisha! Let me know if you have any other questions. Hope you’re well. :)
This was really helpful, thank you very much!!
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.
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!
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!
Glad to hear it!
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.
Thanks Ben! I love R, so this sounds like a great option.
This doesn’t seem to work with Bib file with more than 5 references?
Hmm. I definitely had WAY more than 5 references when I did it, but that was a while ago. It could be something else.
I think they mean using r. I found in my set there was one reference, when included, it did not work. I’m not sure why but omitting that particular reference it worked ok. If your stuck with r someone on stackoverflow will no doubt know why.
Did it have any special characters or something like that?
Thank you so much Ben. This worked perfectly well for me.
Thank you, this was very helpful!!
Hi,
I used your Templates to try to to export my mendeley library to another software. It was VERY helpful. However, I need to export the abstract and the author keywords fields and you templates do not include either. I can’t really understand how to tweak the template files to include these fields. Could you PLEASE PLEASE help me?
Hmm. Let me check on that. I know I definitely had authors export and I believe the abstracts as well. Will get back to you later today.
Hi there. So I double checked and the author field should definitely show up (it is explicitly called in the file). The abstract will show up in one of the Custom fields (Custom1 the way I have it set up right now.) Important caveat: I did all of this over five years ago so their backend may have changed in what it calls certain fields. Hope that helps!
Hi Cynthia!
First of all thanks for this, you are making my life way easier! But, I’m experiencing some problems as the .csv exported file from JabRef groups mendeley tabs in only one space in excel. How can I make the csv exported file have the author, year, abstract and the other labels distributed in different columns? Also I can’t understand the difference between the layout file and the begin layout file. The final exported file looks like a total mess and I don’t know if I’m doing something wrong or if this method don’t work the way I want it to work.
Thank you very much for your help!
If they’re not showing up as separate columns you might need to import it differently into Excel – you should try a different deliminator most likely.
Thank you very much, Cynthia! It worked perfectly! Really really helpful. Life is so much easier now!
My Mendeley tags have commas in them! Any idea how to get around this (e.g. alternative to .csv file format?)?
Hmm. My first suggestion would be to get a time machine and travel back so you don’t have commas in your tags. My second suggestion would be to still do the csv export and then merge them back together once you’re in Excel or whatever. That will be a bit of a pain, but assuming you’re not doing this step multiple times, is probably the easiest. Good luck!
H, JabRef Dev here, it’s an issue with the minus in the fieldname, the tags content itself is no problem. I summarized the answer here: https://discourse.jabref.org/t/custom-export-file-with-mendeley-tags/2112
@cynthiadangelo
PS: Your styles are now located here : https://github.com/JabRef/layouts.jabref.org#csv-export-filter-with-mendeley-tags
We moved from sf.net to github many years ago and also recently published the major milestone 5.0
Regards
Thanks!