Some introductory notes
Perhaps “reverse engineer” is a bit of a strong term. Bit it sketches the general idea I want to illustrate—how I tried to figure out in what way Mendeley stores document information in its database.
Also, this is a long blog post. I have a friend who has “coached” me a bit on bloging, and he warned that if I write long posts, people won’t read them. Something about short attention spans and clicking out of long web pages. While he doesn’t fit into that category himself, here’s my limerick to those people:
In an age of Twitter
With minds fully a-flutter
Every single Twit
Will continually want to flit
From this to that quick wit!
Nonetheless, be warned: This is a long post. The aim is to show you how some of Excel’s techniques and functions are used in practise to manipulate text data. Secondarily also, it explains how I derived the various Mendeley document field tables in my forthcoming book on Mendeley. For each Excel function or tool mentioned in this post, I have provided a link to the Microsoft support site explaining that function or tool. So because this post is so long already, I have not included explanations of the functions themselves, as that would really make it too long. But the reality is that the explanation below probably won’t make sense if you don’t have an inkling of how the various functions work.
In the discussion below, I have indicated each Excel function/technique using Strong style formatting.
Also, you will want to download the example workbook [Mendeley fields for different source types (example for book)] and look at it as you read through the discussion. The discussion uses the names of the worksheets for headings, so it helps you find your place in the workbook.
The following functions and techniques were used in the creation of this workbook (just kidding about CONCATENATE—no one in their right mind uses concatenate!). Each item contains a link to the Microsoft support site, so that you can learn more about that feature from there, which I hope will help you understand how I used it in the example workbook: Mendeley fields for different source types (example for book):
And now, without further ado:
For my book on Mendeley, I had to explain what all the fields in the Mendeley document database were about, so that readers could understand what information belongs where. The problem, of course, is that Mendeley has not made this kind of information freely available. So I had to do some guesswork and some sleuthing. And that’s where Excel comes in. I thought demonstrating the process I used would:
- Illustrate that Excel can be a powerful tool for working with text data
- Be a good demonstration of some advanced Excel techniques like macros, array formulas, conditional formatting, etc.
The first point is interesting. Excel gets used and abused with numerical data all the time. But few people see it as a good tool for working with text data, which is an unfortunate misconception. Excel has its flaws when it comes to text, but in my work as a research consultant, I work with a lot of text data together with a lot of numerical data. Knowing how to work with text in Excel can be very useful.
So here’s the first challenge—figuring out which fields are actually in the database. This is challenging, not only because Mendeley has not published their database design. Rather, two more complex reasons tend to befuddle things: Firstly it is clear on inspection that, for different document types, they have named the same fields differently (i.e., even though the field appears, in the interface, to be called one thing, the same field, for a different document type, is called something else). Secondly, the interface only gives you the relevant fields for each document type, which means that with no document type are you ever looking at all the fields. To get a clear picture of what all the fields are, you have to piece together the information from all 20 document types provided by Mendeley and build a coherent whole. That’s where Excel comes in.
Doing the work
Document types–Mendeley to CSL
Mendeley to CSL Fields
Field mapping–Mendeley to CSL
So I started by going to this page, which maps the Mendeley document types against the CSL types:
The page contains two tables. The first maps the Mendeley document types against the CSL document types. The second maps the Mendeley fields against the CSL fields. I copied and pasted these into two worksheets (note that it is a general principle/best practice of mine to keep one block of data to one worksheet, hence the two sheets). The first I formatted as a table. The problem with the second one (the Mendeley to CSL Fields worksheet) is that some of the rows show that multiple Mendeley fields get mapped against single CSL fields. This means that Mendeley’s database has a lot more detail in terms of field distinctions than currently exists in the CSL specification. Although the process is not shown, a quick Text to columns using the commas as the delimiter, and then copy with Paste Special–Transpose, allowed me to get each Mendeley field in one row. All I had to do then was use Fill Down (Ctrl+D) to fill down the CSL fields for the multiple Mendeley fields against which they were mapped (thus, from the Mendeley to CSL Fields worksheet, to the desired end result in the Field mapping–Mendeley to CSL worksheet). This was then also formatted as a table. Getting from the original table pasted into the worksheet, to the one-field-per-row version, in other words, took probably much less than two minutes (sorry, I didn’t record a screen cast, and I didn’t run my ManicTime stopwatch, so you’ll have to take my word for it).
This then accounts for the first three sheets.
Generic to Working paper (20 in total)
Next I had to create a worksheet for each document type. Fortunately, I had written a macro a long time ago that creates worksheets using the names in a selected range of cells (you don’t need to write these, you can search for one on the net easily). But the code could be as simple as:
For Each c In Selection.Cells
Set ws = .Add(After:=ActiveWorkbook.Worksheets(.Count))
ws.Name = c
Note that this code does the job, but doesn’t check for error such as worksheet names that contain illegal characters, are too long, or already exist, amongst others. But it does the job.
Using the table of Mendeley document types (from the Document types–Mendeley to CSL worksheet), I have my 20 worksheets in just a few seconds. Not too bad. Probably five minutes into the process in total, and I have 23 worksheets, 22 of which I will be using, all of which have been named, and two of which have already been populated.
The next step was more tedious, and took a lot longer. I went to the Mendeley web interface and stepped through the process of adding a document, for all twenty document types. I selected each document type in turn, activated the Additional Fields option, and copied all the fields that Mendeley gave me, pasting them into their host worksheet (the worksheet with the same name as the current document type). When you copy and paste information from the net like that, a lot of crud comes with it in the form of the html formatting, but this is quickly removed by applying the Normal Cell Style (on Excel’s home ribbon).
On each of these twenty sheets, I added two columns. Column C (Web: Basic/Additional) shows whether the field is in the initial listing, or only available once the Additional Fields button has been selected. This was easy to do, as the top items were the initial ones, and the lower items the additional ones. So it was a simply matter of adding the two terms (Basic or Additional) and then filling down for the other rows. Some minor changes were made later: The Note field was added (at the end), as was the Tags field (at the end of the basic list).
Column B was more work. I opened the Document Details pane in the Mendeley Options dialog on the Mendeley Desktop application, and noted which fields were displayed as marked. These were marked as “Core.” Then I also scanned the full list and indicated which fields I, personally, would recommend also using, or would also consider using. So far, so good.
The next column (D) was to show which CSL field this particular Mendeley field was mapped against. The Mendeley Support site lists the fields mapped for each document type here: http://support.mendeley.com/customer/portal/articles/364144-csl-type-mapping. I copied and pasted each list to the relevant worksheet. Using Text to Columns from the Data ribbon quickly gave me two columns (Columns G:H), one for Mendeley, one for CSL. Once I had that information, it became quite easy to grab that and populate column D with a VLOOKUP:
However, note the use of the IFERROR function (introduced with Excel 2007), which is a great wrapper to use with VLOOKUP (I would guess that more than half of my VLOOKUPs these days are wrapped in IFERRORs). I also grouped the twenty sheets and added the formula once for all the worksheets together, thus the slightly excessive use of the whole of columns G:H for my lookup table (the exact number of rows in G:H varies amongst the different worksheets). The formula was filled down, and there it was. I had one problem, though. The main table in columns A:D was showing all possible fields, and the mapping provided by Mendeley was only for the fields they map against CSL for that document type. But the other fields get mapped for other document types, and these are not shown. This was corrected by adding the Filter, and filter out the successfully matched items (filter to show only blanks).
The information for the full field mapping was already available in the table on the Field mapping–Mendeley to CSL worksheet, so I just added a new VLOOKUP:
=VLOOKUP(A9,’Field mapping–Mendeley to CSL’!$A$1:$B$89,2,FALSE)
This formula was added in the top row after the filter had been applied, and filled down across all the unfiltered rows (thus not wiping out the formulas that have already returned something).
Thus, if you scan down column D, you will see these two different VLOOKUP formulas intermingled. Where they appear is determined by how the filter filtered out non-blank lookups from the first one.
One last column (E) was added, to indicate whether I wanted to include this field in the table for that document type in my book (see Appendix A of the book). Here the OR function comes in handy—if the Mendeley field is mapped against a CSL field, or is one of the fields listed in the new document dialog in the desktop application (‘Core’) or is not shown only when the Additional fields link in the web application is clicked (not ‘Additional’), then it must be added:
I had an additional check in cell F1 (using the COUNTA and COUNTBLANK functions), but to tell the truth, it became moot when I started adding the extra ‘Also consider’ or ‘Recommended’ options in column B.
Field listings for each type
All fields from all types
Fields by type
One thing I noticed was that for each of the twenty document types, the Web client showed, when additional fields were displayed, a total of 67 fields were returned (I also later added the Files field from the Desktop application). The worksheet Field listings for each type shows the use of an INDIRECT function to pull these fields over from each sheet (and the ROW function tells the INDIRECT function where to look):
However, demystifying the Mendeley database was not simply a matter of reading across the rows, since the initial items (the Web client’s ‘Basic’ fields) were different. So the same item (e.g., Length, or Series Editor, to mention a few) would not always be in the same row). Because of the field name changes for different document types, an alphabetical sort would also not solve the problem.
So now the last trick was to pull all this together. The challenge, of course, was Mendeleys renamed fields—those ‘fields’ that are actually the same field, but have different field names for different document types—I had to find these, and that meant figuring out when the different name was an alias, or an actual different field.
First another macro that was gleaned (and then modified) many years ago from the Excel-G list and stored in my personal macro workbook copied the contents of each of the twenty worksheets and dumped it into one sheet—All fields from all types. I didn’t actually do all that much with this worksheet—it just served as a starting point for another worksheet. What I did do was to copy and paste the worksheet names (from column A of the same sheet) to Column H, and then use Remove duplicates to list only the unique items. Then a quick MATCH function confirms that each of them is in the document type mapping table (if the MATCH returns #N/A then the item is not found):
=MATCH(H2,’Document types–Mendeley to CSL’!$A$2:$A$21,0)
I then did the same Copy and Paste and then Remove duplicates operation with the fields, so that column K listed all the unique field names across all document types. At this stage, the list would consist of ‘real’ field names as well as aliases, but the important thing is that it was complete—it listed 90 entries, to Mendeley’s 67, meaning I had to find and eliminate 23 aliases.
This list of unique field names and aliases was copied and pasted to a new sheet (Fields by type), which would be the one mapping fields across document types. This list was somewhat longer than the previous lists, but it would help me sort out what was going on. Again a VLOOKUP wrapped in an IFERROR would tell if this field was, for this document type, a core field or not. But oh no! I now had to VLOOKUP to twenty different worksheets! That would mean twenty different formulas, one for each column, to refer to the right worksheet. Or maybe not…. After all, each worksheet is named for the document type (thanks, in part, to my macro, and also to good naming conventions). So with one formula that includes an INDIRECT function to build the worksheet name into the reference, I can VLOOKUP the right sheet for that column:
Note that the function will return a blank if the field is not found (the IFERROR sees to that), but if the field is found, then the function will return either the text from column B of the lookup sheet (if it has text), or a zero. Zero thus means the item is on that sheet, but it is not a core field. I could have “translated” that into something else with my function, but I was happy to leave it as is for the moment. What I did want to do was to use Conditional Formatting to show me on which sheets a field is not found. This would help me to see how commonly a field was being used, which could also give me clues as to when the field name was actually an alias (it would not appear in many document types ‘under the alias’). To quantify this, Column B counts the various text options and displays them separated by slashes:
Field listings for each typeFIN
Almost done. I just need to look at this, and figure out what was being used where. Some were obvious (e.g., the Abstract field, which is there in all twenty document types). The worksheet Field listings for each typeFIN is just the content of the Field listings for each type worksheet copied and pasted as values with Paste Special. Here I used the ‘Generic’ document type as my reference point, and I Sorted alphabetically. Then I looked for mismatches—fields that did not match the same name as was in the Generic field. This was easy to do with another application of Conditional Formatting. Note how the conditional format uses a partially relative reference, so that I could set up one conditional format, that is row-specific—each column in the table is compared to the field name for the Generic field in Column C. Now the gumshoe work came in. For the mismatches, I had to look at the previous sheet, and try to discern what was the most likely name for that field by reading across all the columns. So, for example, for the Case document type, I was able to figure out that Reporter is not an actual field, but is the alias for Authors, as was also Date Decided the alias for Last Updated. So Mendeley, when defining the document information capture for the Case document type, captures the Reporter data to the Authors field, and the Date Decided field to the Last Updated database field. The Film and Television Broadcast fields do the same. To make this task easier, I used the filter to help me zoom in on the fields that need attention. Column B counts how many of the field names for the various document types are the same as for the Generic document type—if it was nineteen, then all twenty agreed, and that field needed no work:
So I filtered out all the 19s, and worked on the rest. What the sheet does not show is that each time I looked at a name (I started with those with the lowest occurrences), I tried to figure out what that field was an alias for. I then entered that alias in the row for the field for which I believed it stood (e.g., adding the value ‘Reporter’ for the Case document type in the ‘Authors’ field), and then I deleted that field row. This process of manual elimination whittled down the fields until I was left with 68 (the 67 former field count, plus 1 for Files).
Field listings for each typeCOR
The last thing to be done was to map all of this out sensibly, so that it could become a table in the book. The worksheet Field listings for each typeCOR shows again all the document types across the columns, and the now-finalised list of fields down the rows. Again a VLOOKUP to the previous worksheet with an INDIRECT to reference the right worksheet for that column, brings in the name of the document type when the field is listed as core or recommended (not when listed as “also consider”), but to execute that decision, it requires the use of the OR function, nested in an IF function: Quite a mouthful:
=IF(OR(VLOOKUP(‘Field listings for each typeFIN’!C3,INDIRECT(“‘”&D$2&”‘!$a$1:$b$100″),2,FALSE)=”Core”,VLOOKUP(‘Field listings for each typeFIN’!C3,INDIRECT(“‘”&D$2&”‘!$a$1:$b$100″),2,FALSE)=”Recommended”),D$2,””)
Then I used Column A to count for how many document types the field was listed. This is an interesting formula, as I could not simply use a COUNTA or even a COUNT minus a COUNTBLANK, because the VLOOKUP formulas meant that the cells were not actually empty. Thus I had to use an Array formula, testing the length of the value returned by each cell’s formula—one of those instances where an array formula is the only thing that can get a result:
This told me in how many document types, that field was used by Mendeley. However, presenting such a big table (essentially 69 rows x 22 columns) in a book is problematic. For my final step, I had to summarise columns D:W of this worksheet. I would have thought to use CONCATENATE, except that I still believe it is the most useless Excel function ever (ill-)conceived. Now I have a confession to make: I actually used Charles Williams’ excellent CONCAT.RANGE (which forms part of the FastExcel SpeedTools which I was beta testing). But if I distributed this workbook as a demo, that wouldn’t do, since most people don’t have those tools (and I won’t soon, either!). So I had to come up with an alternative. So the original:
=IF(A3=20,”All”,CONCAT.RANGE(D3:W3,”; “, , TRUE,,))
=IF(A3=20,”All”,IF(D3=””,””,D3&”; “)&IF(E3=””,””,E3&”; “)&IF(F3=””,””,F3&”; “)&IF(G3=””,””,G3&”; “)&IF(H3=””,””,H3&”; “)&IF(I3=””,””,I3&”; “)&IF(J3=””,””,J3&”; “)&IF(K3=””,””,K3&”; “)&IF(L3=””,””,L3&”; “)&IF(M3=””,””,M3&”; “)&IF(N3=””,””,N3&”; “)&IF(O3=””,””,O3&”; “)&IF(P3=””,””,P3&”; “)&IF(Q3=””,””,Q3&”; “)&IF(R3=””,””,R3&”; “)&IF(S3=””,””,S3&”; “)&IF(T3=””,””,T3&”; “)&IF(U3=””,””,U3&”; “)&IF(V3=””,””,V3&”; “)&IF(W3=””,””,W3&”; “))
Now that’s a huge formula, and I am way too lazy to do all that typing. But here’s the last technique I used: Excel can actually be used to (sort of) build its own formulas.
So I entered this into D73 (D72 shows a simplified version):
Note how the ” has to be preceded by another ” if it is to be displayed in the cell results (it always takes me a moment to get my head around it when I have to write formulas like that). The formula results in this text:
Now all I did was fill D73 across to W73, then copy D73:W73, paste it into my text editor (UltraEdit), where each cell is separated by a tab and do a search and replace to strip out the tabs. I copied the result, and went to cell A3, where I typed the =IF(A3=20,”All”, bit, pasted my text, backspaced out the last ampersand (I could have done that in UltraEdit too), and added the final parenthesis. All this is a lot quicker than typing all of what is in cell A3. The formula then just gets filled down, and there I have my information for the book’s table!
After all of this, I was in the position to simply copy the relevant parts out of this Excel workbook, and paste them into the Word document for the book, formatting them with a table style. If you want to see what these final tables look like, you would, of course, have to get your hands on the book (due out in about a month and a half). The table names (I won’t give table numbers right now, as these are not yet final) as they appear in the book are:
Mapping of Mendeley Document Types to CSL Document Types
Mendeley Document Information Fields
Mendeley Fields Mapped to CSL fields
Mendeley Field Types Showing Core Document Types and Alternative Names
And then all the tables in Appendix A: Mendeley fields per document source type.