In Excel:

1. Add columns to match https://knowledge.exlibrisgroup.com/Leganto/Implementation_and_Migration/Migration_Tools/Reading_List_Migration_Tool_(English)
2. If you want to add course data from an external system (eg to use the course title in the reading list name, or to add instructor details)
   1. Add the external data to another sheet in the same Excel file, making sure that column A contains a unique identifier that also appears in your Equella data – eg a course code.
   2. Back on the sheet containing Equella data, in the column where you need the course data, use a VLOOKUP formula. This is one of my favourite formulae, and the way it works is eg:
      =VLOOKUP(C2,ExternalData!$1:$10485,2, FALSE)
      • C2 is the field in your Equella data that contains the unique ID (eg course code) you want to look up
      • ExternalData is the name of the sheet where you’ve pasted your external data
      • $1:$10485 selects all the rows you want to search through. You can select a smaller selection, but use $ to fix both the rows and columns so the area doesn’t shift when you fill down
      • 2 identifies the column in ExternalData that you want to get the information from, except just to confuse you it counts with numbers instead of letters. So 2 will bring back information in column B
      • FALSE makes sure you match exactly on your unique identifier. If for some reason you want fuzzy matching, you can use TRUE, but most of the time it’ll cause more problems than it’s worth.

Fill the formula down the column.

3. Fill down any fixed parameters you need eg:
   1. reading_list_status = BeingPrepared
   2. RLStatus = DRAFT
   3. visibility = DRAFT
   4. citation_status = BeingPrepared
   5. external_system_id = [a blank space, not empty]

In OpenRefine:

This allows you to do a lot of complex data cleaning using regular expressions.

1. Upload a CSV
2. For each thing you want to clean, go to “Edit column > Add column based on this column” and type in value.replace(/your regular expression/,"your new text") – for example:

1. citation_isbn – Remove hyphens with value.replace(/-/,"")   Then replace every sequence of stuff that isn’t a number or X with the delimiter ;  (semi-colon followed by space): value.replace(/[^0-9X]+/,"; ") 
2. citation_issn – leave the hyphens but delete every other unexpected character with value.replace(/[^-0-9X]+/,"; ")
3. citation_volume and citation_issue – you could theoretically delete everything that’s not a number (and this conforms to the data entry rules in Leganto) but note you’ll lose information about issues called “Spring”, “March-April”, etc. Work out the balance between clean / detailed that’s appropriate for your organisation
4. citation_issue – clean up best you can bearing in mind lots of issues are named Spring, May, etc
5. citation_start_page and citation_end_page – If you currently have only a column with a range of pages (eg “45-54”) then you can easily break it on the hyphen eg add one column with value.replace(/-[0-9]*/,"") and another with value.replace(/[0-9]*-/,"")
6. citation_chapter – we had a number of items where the citation_title was something like “Chapter 1: Introduction”  (Or “Ch. 1” or other variations). So we looked for where “Ch” appeared at the beginning (with optional extra letters then optional full-stop and/or space) followed by a number followed by any other text – and extracted that number: value.replace(/Ch[a-z]*[. ]*((\d)*).*/,"$1")
   • If you’re confident with this you can then edit citation_title to delete all of that prefix and any punctuation and just include the actual title.
7. citation_edition – similar to above, where citation_title often finished with something like “ (2nd Edition)” or “ 2nd ed.” etc
8. citation_author – this was messiest for us as lecturers had entered these into Equella in all sorts of formats. But they were delimited by semi-colon so we just split it on the first semi-colon (cf citation_start_page) so anything before was citation_author and anything after was additional_person_name.

3. When you’re happy with the data, Export as CSV

Back in Excel

1. Important!  When importing back to Excel, use Data > Get External Data > From Text and make sure you select:

• Delimited
• Unicode (UTF-8)  (otherwise diacritics will go haywire)
• Headers
• Delimiter = comma
• In the final data preview, select all the columns and format them as Text (otherwise dates and ISBNs will go haywire)

2. Check all your column names again (as OpenRefine will have created a lot of new columns) and get them back in order.
3. Save early, save often!

With a separate copy of your Equella export:

1. Reduce it to the “Item id” (or may be called “citation_originating_system_id”) column and rename that column “source”
2. Add empty columns with reference to the metadata schema in your Equella (you can browse it via the Administration Console > Metadata Schemas > Edit > Editor; or use BIRT to see which each one contains). As examples, we used the following columns – note the last three were all different places where the full-text attachment might be stored due to our local database architecture – but you’ll want at least the item ID, its version, its title (to confirm you have the right one), and of course the attachment.
   • item_id
   • item_version
   • lom/general/title
   • localData/attachments/attachment/uuid             
   • item/copyright/portions/portion/sections/section
   • item/copyright/portions/portion/sections/section/attachment
3. Save this as a CSV into an empty directory
4. In the same directory, create another empty directory for the attachment files you’re now going to download.

In EBI:

Connection tab

1. Fill out
   • Institution URL
   • Username (you’ll need admin access to get files)
   • Password
2. Test/Get Collections
3. Select a collection

Options tab

1. Set up a base path pointing to the empty directory you created for attachment files
2. Important: Tick “Export items as CSV” – the “Test Import” button should now change to “Test Export”.
3. Filename conflicts: Do not overwrite any files

CSV tab

1. Browse to the CSV file you created at the start of this process and load it.
2. It should pull in your column headings.
3. You need to edit the appropriate column data type for each heading – these are dropdown menus.
   • Source (the only column you have that isn’t empty) must be the Target Identifier
   • item_ID = Item ID
   • item_version = Item Version
   • any metadata you want like the title, author, etc = Metadata
   • anwhere you store the attachment file = Attachment Locations

When your screen looks like this you’re almost ready, but first:

If you’ve set up Equella to show users a copyright notice before they can access the file, this is going to just download a file that’s named like the file you expect but only contains the copyright notice! So go to:

• Equella > Administration Console > Collection definitions
• Then Edit the collection > Extensions > CAL Licensing Configure
• Untick “Resources with copyright status require agreement?”
• OK
• Save

Now you can either do a Test Export (exports metadata only, not attachments) or go ahead and Export (exports metadata into the CSV and attachments into the files directory).

In File Explorer

1. Some files will have duplicate names – these will be in subfolders of your attachments directory eg 1/documentName
2. In each folder, rename each file with the folder number -> underscore -> documentName eg 1/chapter 1.pdf -> 1_chapter 1.pdf
   • We had only a small number so did this manually. If you’ve got too many for that, try using a Powershell script (see #4 for an example, and google for syntax)
3. Move all files from these subfolders out into the main directory and delete the empty subfolders.
   • If any names still clash, don’t overwrite but rename them – just make sure you also rename them in the CSV sheet as below.
4. Ex Libris won’t accept files with spaces in the filenames and we definitely didn’t want to edit these manually. So we used Powershell to navigate to the files folder, and ran the command:
   dir | rename-item -NewName {$_.name -replace " ","_"}

And back in Excel

1. In the CSV in the filename column, also rename all 1/ -> 1_
2. And also replace all spaces with underscores
3. Now copy this filename column back to the file_name column of the spreadsheet you got from Process 1
4. Spot-check using item ids that the right data has ended up on the right line!  If not, take it out again and try using a VLOOKUP similar to when we pulled in extra course data but using the item_id as the unique identifier.