The Clarivate website offers a step by step approach (including a video) for creating a tab-delimited file from an Excel spreadsheet, which can be loaded into EndNote:
dated 16 June 2022
The following text was provided by UTS Library in 2019 and is available from the following web archive address: https://web.archive.org/web/20190115185921/https:/www.lib.uts.edu.au/question/5951/how-can-i-transfer-references-endnote-excel-spreadsheet
The process described below is quite complex, and has lots of steps, but is actually quite straightforward. The reason for all the steps is because we need to tell EndNote exactly which bits of the spreadsheet belong to which fields of the references. And we also need to tell EndNote what reference type each of the references should be.
We will be using line 1 of the spreadsheet for the field names used in all the references (author, title, etc), and column A of the spreadsheet for their reference types. So you will need to insert a new, empty column A; and possibly a new, empty line 1.
Type the words Reference Type into cell A1. Leave the rest of column A empty for the moment. Field names (see step 3) should be added into line 1 starting with cell B1. The order of the field names along line 1 doesn't matter, so long as each field name corresponds to the data in its column (eg the field name Author should be above the column that contains all the author names).
You must take the field names from EndNote as follows:
- In EndNote, click on Edit (on a Mac, click on EndNote X5), Preferences, Reference Types, Modify Reference Types
- Use the drop down menu to select different reference types. For each reference type, the field names you need to use are in bold, in the column headed by the word "Generic". So for example, for journal articles, your column of journal names should be headed in line 1 by the field name "Secondary Title".
The rest of the spreadsheet should contain all the references, starting in line 2, one reference per line, so that the columns below each of the field names contain the correct data for that field (authors, titles, page numbers, etc).
The order of the references doesn't matter, and it's OK to leave empty cells here and there; but don't leave whole columns empty.
Authors and editors should be surname, first name (or initials). If there are multiple authors or editors, type // between them, eg Smith, Jenny//Jones, K//Brown, VM
Then type all the appropriate reference types into column A.
If you have any double quotes (") anywhere (eg in some of your titles), change them all to single quotes (')
Save this Excel file with a new name.
Then click on File, Save As...
Change Save as type to Text (Tab delimited), call the file any name you like, and save it somewhere convenient (eg desktop). Say OK and Yes to any notices that pop up.
Close the Excel file, this time saying No to saving changes.
Open up the saved text file using Notepad or Textedit on a Mac. Put the cursor in top left corner. Hit <Enter>, move the cursor back to top left corner, and type *Generic
Click on Edit, Replace. In the Find what box, type a double quote ". Leave the Replace with box empty. Then click on Replace All. (If we don't get rid of the double quotes around the multiple authors at this point they end up in our EndNote references and are much more trouble to get rid of. This is why we changed double quotes to single quotes in step 8).
Close the text file, saving changes.
Open EndNote, open a reference library, click on File, Import. Choose File - choose your saved text file. Import Option - choose Tab Delimited. Click Import. The first time you do this, try it on a new, empty reference library just to test the process out! Once you're sure it works OK, repeat with the same text file into your real reference library.
References should now be in EndNote correctly. Once you've checked them, if they're OK you can delete the saved text file, and any of the Excel files that you don't want to keep.