1. GENERATE AND DOWNLOAD A NEW FILE

1. From the SS management menu, from Business Intelligence Tools choose Data on Demand.

2. Previously requested reports will display; click the button that says Generate New Report.

3. Choose the options; generally you will want

   1. UTF-8

   2. Choose CSV without Subjects. (Later we can consider adding subject terms.)

   3. Journals content

   4. If you need a report with SSIDs, ask Serials Solutions to prepare that for you.

   5. Uncheck split file.

   6. Select Save

4. Wait; it will take between 5 and 10 minutes to generate the report. You will receive an email when the file is ready for download.

5. Click the report link to download the zipped file to Alexandria\ERM\Monthlyholdingsreports\[newfolder named yyyymm]. Save and unzip the file to your hard drive.

2. INITIAL FILE CLEANUP

1. Move the Resource column to be the first column and rename it to Provider.

2. Delete the Type column.

3. Delete any resources lacking a StartDate or lacking a URL.

4. Clean up embargos:

   1. Insert a new column between EndDate and URL called Embargo.

   2. Sort by End Date and look for values such as “1 month ago” that are not in mm/dd/yyyy or yyyy format.

   3. Copy these nonstandard end dates to the new Embargo column.

      1. WATCH for dates beginning 11/ 10/ or 12/ in the 1800s. Excel treats these like text instead of dates and they can get mixed in. Move these back to the EndDate column if accidentally transferred to the Embargo column.

      2. Embargo periods less than 1 month can be deleted. For example: 1 day ago, 2 weeks ago, 6 days ago.

      3. Nonstandard end dates that have the word “calendar” in them. For example, “2 calendar years ago.” In this case, replace the “2 calendar years ago” in the EndDate column with the year that it refers to. For instance, if I am completing this task in the year 2015, I would replace “2 calendar years ago” with 2013.

   4. Delete the nonstandard values from the EndDate column.

   5. In the Embargo column find and replace ‘[space]ago’ with nothing.

   6. In the Embargo column find and replace “Month” with “month” and "Year" with "year". To do this you will need to click “Options” when in the ‘Find and Replace’ dialog box, and check the box that says “Match case”.

   7. In the Embargo column find and replace '[space]days' with nothing. (Retain the number of the embargo, just remove the word days.)

5. Clean up seasonal EndDates

   1. In the EndDate column, you will see dates beginning Fall, Spring, etc.

   2. Highlight the EndDate column. Find and replace these:

      1. Replace ‘Fall[space]’ with ‘09/21/’

      2. Replace ‘Spring[space]’ with ‘03/21/’

      3. Replace ‘Summer[space]’ with ‘06/21/’

      4. Replace ‘Winter[space] to ‘12/21/’

6. Clean up seasonal StartDates

   1. Sort the spreadsheet by the StartDate column.

   2. Look for seasonal dates beginning Fall, Spring, etc.

   3. Highlight the StartDate column. Find and replace these:

      1. Replace ‘Fall[space]’ with ‘09/21/’

      2. Replace ‘Spring[space]’ with ‘03/21/’

      3. Replace ‘Summer[space]’ with ‘06/21/’

      4. Replace ‘Winter[space]’ with ‘12/21/’

7. Clean up relative StartDates

   1. While still in the StartDate column, you may see some relative StartDates such as “5 calendar years ago” or “6 months ago” or “current calendar year.” The Innovative System can interpret these dynamically, as long as the Coverage Spreadsheet Conversion Rules knows how to calculate the date.

See the Sierra manual for conversion rules OR the Coverage Spreadsheet Conversion Rules tab.

• In Sierra ERM or Admin, go to Admin > Parameters > General > Coverage Spreadsheet Conversion Rules. Note: Permissions are required to access this section.

• In the date section are listed conversion rules; generally the calculation is _today-[days, years, or weeks]

• Rules are case-sensitive

• If needed, enter a new rule and Save.

ii. For “calendar years” it may be best to enter a “hard” start date, as done in the procedures for EndDate

iii. In several of the Freely Accessible databases, some StartDates and EndDates have a nonstandard format such as "Jan-14" or "Mar-97". Due to how Excel reads the dates, these may not be easily sorted out when compiled with all databases. Filter the spreadsheet by Provider: "Freely Accessible" to identify the nonstandard dates. Change the cell format of the nonstandard dates to Category: "Date" and Type: "mm/dd/yyyy" [default: may look like 3/14/2012].

h. *Special Instructions* : When prepping databases "Business Insights: Essentials" and "Medline Complete" add an additional column at the end of the spreadsheet and label it "Public_Note". Add the note: "Business Insights: Essentials [Selected Articles]" or "Medline Complete [Selected Articles]" for each title in its respective database.

i. Check for URL problems.

i. Review proxied URLs (those with format "http://0-") to confirm that there are none with the format "https://0-". If a URL for a paid title is discovered with an additional "s", inform the Web Librarian.

1. One special case is ACSESS Digital Library. These URLs need to start with the https and use dashes instead of periods, example:
https://dl-sciencesocieties-org.library.uark.edu/publications/aj
Sometimes these export from Serials Solutions as http and those need to be corrected before loading into the system, example:
http://0-dl-sciencesocieties-org.library.uark.edu/publications/aj

ii. Some platforms, such as GALE, require specific site codes as part of the URL.

1. Mullins is Faye28748

2. Law is younglaw

3. Certain State Library resources use ar_a-ual

   1. Codes are case-sensitive

4. Make sure that URLs for Gale databases have the appropriate code

  j. Custom sort alphabetically by provider and title. Save.

3. SAVE TO SERIALS DRIVE

1. Open the Serials Drive and select the ‘ERM’ folder.

2. Open the ‘Monthly Holdings Reports’ folder.

3. Open the folder for the appropriate month and save the document with the name ‘coverage[yeardaymonth]’

4. PREPARE FOR COVERAGE LOAD

1. You are now ready to save portions of the Excel file as *txt (Unicode-text) formatted files and load them into Sierra

   1. You will want to make sure your columns are wide enough so that fields in the date columns do not read “########.” This will copy over as text when you save to a text file.

   2. Save the monthly coverage file as a Unicode Text (*.txt) file.

   3. It is easiest to load one resource at a time. Sort by Provider and copy the relevant lines to Google spreadsheet. Remember to copy over the headings.

   4. Confirm that your data omits any blank rows or columns by highlighting and deleting empty rows.

   5. Confirm that the header row has column names in the format:

      1. Provider

      2. Title

      3. ISSN

      4. eISSN

      5. StartDate

      6. EndDate

      7. Embargo

      8. URL

      9. Public_Note

   6. These can be in any order but the column names are case-sensitive and must match.

   7. Check that start and end dates are correctly formatted.

   8. Save your Google Spreadsheet under the name of the database.

   9. Select ‘File’ and then ‘Download As.’ Download the file as a Comma-Separated Values (csv) file, and save it in the proper database folder in the ERM folder.

COVERAGE SPREADSHEET CONVERSION RULES

#------------------------------------------------------#

# special date handling

#_today is a reserved word which calculates to current local date

# can add or subtract years (y), months (m) or days (d) from _today

#-------------------------------------------------------#

date|present     |

date|Present     |

date|PRESENT     |

date|inception     |

date|today        |_today

date|Today        |_today

date|TODAY      |_today

date|20 years ago  |_today-20y

date|10 years ago   |_today-10y

date|5 years ago    |_today-5y

date|5  years ago   |_today-5y

date|4 years ago    |_today-4y

date|3 years ago    |_today-3y

date|2 years ago    |_today-2y

date|1 year ago     |_today-1y

date|18 months ago   |_today-18m

date|9 months ago   |_today-9m

date|6 months ago   |_today-6m

date|4 months ago   |_today-4m

date|3 months ago   |_today-3m

date|2 months ago   |_today-2m

date|1 month ago    |_today-1m

date|30 days ago    |_today-30d

date|29 days ago    |_today-29d

date|28 days ago    |_today-28d

date|21 days ago    |_today-21d

date|15 days ago    |_today-15d

date|14 days ago   |_today-14d

date|7 days ago    |_today-7d

date|5 days ago    |_today-5d

date|tomorrow     |_today+1d

date|next month   |_today+1m

date|next year    |_today+1y

date|Winter 1995-1996   |199512

date|1 week ago     |_today-7d

date|2 weeks ago    |_today-14d

date|current month    |_today-30d

date|current week     |_today-7d

#------------------------------------------------------#

# provider conversion

#------------------------------------------------------#

After the monthly holdings report of all journals has been downloaded and edited, create a subset file of paid titles to send to ILL.

1. Open Monthly Holdings Excel file for the current month on the ERM folder on Nautilus.
2. Save the File as RAPIDMMDDYYYY.
3. Sort the file by URL and remove all rows for non-athenized URLs (those without https://go.openathens.net/) and are not ProQuest, Ebsco, or Gale.
4. Sort the file again by Provider and Title.
5. Remove the titles in the following packages:
   • Academic Search Alumni Edition
   • BNA Premier Package, a Law package that is not available to our users.
   • Business Insights: Essentials, has incomplete full text coverage.
   • Business Source Alumni Edition
   • Journal of Visualized Experiments
   • Literature Resource Center, has information that changes rapidly
   • MEDLINE Complete, has incomplete full text coverage.
6. Sort the list again by title and save it.
7. Send the file to the Head of Interlibrary Loan.