When using tab delinated to export an endnote library to excel, is it possible to export each author in a separate column, so that the columns read “author 1” “author 2” “author 3” etc.? I am trying to count the number of authors included on the papers in my database for each year and it would be easier to do so if I could separate out the authors automatically. For example, the final product would be:
In 2000, there are 18 authors on the papers in my dataset. In 2001, there are 13 authors on the papers in my dataset.
Please let me know if you have any suggestions, other than just splitting the cells in excel, which is sort of messy.
The Tab Delimited Export will not list each author as a separate tabbed field. The information in each field is listed as a single tabbed instance. Because of this, it will import all of the data from that tabbed instance into one column in Excel. You may be able to obtain the information you need using the Subject Bibliography option. For more information on Subject Bibliographies, click on the Help menu and select “Search for Help on” or “EndNote Help” depending on your version of EndNote and platform. In the Help guide, go to the Index and browse to Subject Bibliography. Here you can click on Overview and then “Printing a Subject Bibliography” for more detailed information. To access the feature in EndNote, click on Tools > Subject Bibliography.
Each author can be separated into a separate Excel column if you: 1) modify the Author Lists bibliography setting in the Tab Delimited output style to insert a delimiter between each author’s name (see image 1); 2) export the references using the now modified Tab Delimited output style; then 2) use Excel’s “Text Import Wizard” feature to specify the delimiters used which will segregate each author’s name and place each into a separate column (see image 2).
There are two caveats with this method:
Since the number of authors will vary by record it’s important to import records into Excel by groups based on the number of authors otherwise the number of author columns will vary and not line-up. You could expedite the process by modifying the bibliography Sort Order setting in the Tab Delimited output style to sort by number of authors (see image 3). This will generate a presorted output and after importing it into Excel you could cut and paste the records into the correct columns. (An alternative to this but onerous is to create a custom Endnote field; manually notate the number of authors in the custom field; sort your references based on the custom field; then select and export the Endnote references in batches by groups based on the number of authors.)
Also note that while Endnote’s reference types records retain the record’s data within their respectively named fields the data will be imported into Excel based on the generic field names - not the reference type(s) field names. If this is an issue you may need to consider grouping them by reference type in addition to the number of authors before exporting.
Endnote’s Tab Delimited output style retains carriage/hard returns in some of the fields (e.g., ISSN, Keywords, Notes, etc.) which messes up the imported data in Excel by placing the data in separate rows instead of columns. (You can identify these problematic fields by running a test export/import using a small number of records.) Until the developers address this issue a workaround is to use Endnote’s Find and Replace feature to search a problematic field (e.g., Keywords) and change the carriage return to a semicolon-followed-by-a-space. Once the carriage/hard returns within the affective fields have been removed or changed you can proceed to export the records.
An excellent workaround provided here by CrazyGecko. As per the issues mentioned with hard returns, please see this article if you need to clean up that data.
