ERM: Serials Solutions Monthly Update

Purpose: To update the journal titles and subjects from the Serials Solutions monthly report.  This procedure takes 30 minutes to one hour (more if you run into problems along the way).  The first time this is done on a new computer, two Excel macro files will need to be imported.  Instructions for doing this are at the bottom of this page.

Get the data from Serials Solutions

  1. Log into Serials Solutions Client Center
  2. Go to Data On Demand.
  3. Click “Generate new report”
    1. Latin-1
    2. CSV with Subjects in multiple columns
    3. Journals Content
    4. Deselect “Split File”
  4. Click “Save” (report will take 5-20 minutes to generate; refresh the screen to see progress update)
  5. You are waiting for the file “report.zip” with the current date to have the status “Ready” (See “Data On Demand” image below)
  6. Download the report (report.zip)

Serial Solutions Data On Demand Screen Shot

Prepare the data

  1. Open CSV file with Excel
  2. Click on “View” tab
  3. Click “Macro”
  4. Select “SerialsSolutions”
  5. Click “Run” (If Excel asks if you want to overwrite the existing file named “SSJournalsCurrent.xlsx”, click Yes)
  6. Click “Macros”
  7. Select “InsertSSLocSubMap”
  8. Click “Run”
  9. On the worksheet named SSJournalSubjectMapping, check for subject names that have not been changed to numbers. If there are some, you may need to add these to the subject lists and then update the Excel macro.  **(See the procedure for this at the bottom of this page)
  10. Save and Close

Import the data to Access

  1. Open inetsupport on library18.library.umaine.edu
  2. Open folder “ejournals”
  3. open folder “web”
  4. open ej.mdb (If asked “What do you want to do with this file” click “Open”
  5. If there is a security warning along the bottom of the ribbon, click “Options”, choose “Enable this Content”, and click OK
  6. Select the “External Data” tab
  7. In the “Import” section choose “Saved Imports”
  8. SSJournals (This copies the journal ID#, Titles, ISSN, coverage dates, and URL into the “SSJournals” table)
    1. Choose “Import-SSjournals”
    2. Click Run
    3. Click “Yes” to overwrite existing tables (The following step may take several minutes and may look as though nothing is happening)
    4. When it has finished importing, you should get a message saying “All objects were imported successfully”
    5. Click “OK”
  9. SSJournalSubjectMapping (Matches journal ID# to their Serials Solutions subject ID#)
    1. Choose “Import-SSJournalSubjectMapping”
    2. Click Run
    3. Click “Yes” to overwrite existing tables
    4. When it has finished importing, you should get a message saying “All objects were imported successfully”
    5. Click “OK”
  10. SSLocalSubjectMapping (Matches our local subject ID# to Serials Solutions subject ID#)
    1. Choose “Import-SSLocalSubjectMapping”
    2. Click Run
    3. Click “Yes” to overwrite existing tables
    4. When it has finished importing, you should get a message saying “All objects were imported successfully”
    5. Click “OK”
  11. SSLocalSubjectMappingRef (Matches Serials Solutions subject ID# to subject name)
    1. Choose “Import-SSLocalSubjectMappingRef”
    2. Click Run
    3. Click “Yes” to overwrite existing tables
    4. When it has finished importing, you should get a message saying “All objects were imported successfully”
    5. Click “OK”
  12. SSSubject (Lists Serials Solutions subject structure: ID#, subject name, level in hierarchy, parent subject, URL)
    1. Choose “Import-SSSubject”
    2. Click Run
    3. Click “Yes” to overwrite existing tables
    4. When it has finished importing, you should get a message saying “All objects were imported successfully”
    5. Click “OK”
  13. Click “Close”
  14. Close ej.mdb

Update the SQL database

  1. Log in to Library18.library.umaine.edu:4001 via remote desktop.
  2. Open a command prompt (Should be a shortcut on the Desktop, but if shortcut is missing Click Start -> Run -> type “cmd” and hit Enter)
  3. Issue the command:
    cd \inetsupport\ejournals\ejupdate\ejupdate
    the command line prompt should show
    C:\inetsupport\ejournals\ejupdate\ejupdate>
  4. Issue the command:
    dtexec /f “ejupdate2.dtsx”
  5. You should see rather voluminous output (see below) ending with
    DTExec: The package execution returned DTSER_SUCCESS (0).
    Started: 9:59:17 AM
    Finished: 9:59:26 AM
    Elapsed: 8.563 seconds
    The times of course will vary
  6. Close the Command Prompt window
  7. Close Remote Desktop

**Procedure for adding new subjects – Does not need to be done monthly

  1. Add this subject to the worksheet named “SSSubject”
  2. In the ID column, find the highest value in the column and add 1 to it
  3. Go to http://qq4nm4zz3u.search.serialssolutions.com/
  4. Use the subject pull down to find the new subject
  5. You will need to take note of four things:
    1. The subject above it in the hierarchy –  “Parent” column
    2. How many levels down it is in the hierarchy – “Level” column – If the subject is listed on the subject pull down, it is level “1”;
    3. The last six characters of the URL – “URL” column
  6. Go to worksheet “SSJournalSubjectMapping”
  7. Replace the subject names with the newly assigned ID numbers
  8. Update the macro:
    1. Click on “View” tab
    2. Click “Macros”
    3. “View Macros”
    4. Select “SerialsSolutions”
    5. Click “EDIT”
    6. Find the subroutine called “SSSubject Macro”
    7. Add a new line below the line that reads “Columns(“B:B”).Select”
    8. Copy and paste one of the “Selection.Replace”… lines
    9. Replace the values of “What:=” with your new subject
    10. Replace the values of “Replacement:=” with your new ID number
  9. Save and Close

Procedure for Importing Excel Macros

  • Open Excel
  • Click on the View tab
  • Click Unhide
  • Select “Personal.xlsb.” (If personal.xlsb does not exist, you will need to create it)
  • Click OK
  • Click on Macros
  • Click View Macros
  • Click Edit
  • This should have opened the Visual Basic Editor
  • Go to File > Import File
  • Browse to S:\Serials\SerialsSolutions\MonthlyUpdates\ExcelMacro\SerialsSolutions.bas
  • Click Open
  • Go to File > Import File
  • Browse to S:\Serials\SerialsSolutions\MonthlyUpdates\ExcelMacro\InsertSSLocSubMap.bas
  • Click Open
  • Go to File > Close and Return to Microsoft Excel
  • Check to title bar to make sure you are looking at Personal.xlsb
  • Click “Hide”
  • Close Excel
  • You may be asked “Do you want to save the changes you made to the Personal Macro Workbook? If you click Yes, the macros will be available the next time you start Microsoft Office Excel.”
  • Click Yes

Procedure for Creating the Personal.xlsb file

  • Start Windows Explorer.
  • Navigate to C:\Documents and Settings\user name\Application Data\Microsoft\Excel\XLStart.
  • In the right pane of Windows Explorer, right-click, point to New, and then click Text Document.
  • Rename the file personal.xls.
  •  If Windows asks you whether you want to change the file name extension, click Yes.
  • Start Excel and open personal.xls
  • Click on the View tab
  • Click “Hide”
  • Close Excel
  • When asked if you want to save your changes, click Yes.
  • SSJournals is the list of journals from Serials Solutions
    • ID
    • Title
    • ISSN
    • Start Date
    • End Date
    • Resource (Database)
    • URL
  • SSJournalSubjectMapping  – This matches the journals with subjects
    • JournalID = “ID” from SSJournals table
    • SubjectID = “ID” from SSSubject table
  • SSLocalSubjectMapping – This matches Serials Solutions Subjects to Fogler’s Subjects
    • LocalSubjectID – Local Subject ID
    • SSSubjectID = “ID” from SSSubject table
  • SSLocalSubjectMappingRef – The helps us match the Serials Solutions Subject names to our Local Subjects
    • SSSubject = “Subject” From SSSubject table
    • LocalSubjectID – Local Subject ID
    • URI – (No Longer Used)
  • SSSubject
    • ID – Number we have assigned to represent Serials Solutions Subjects
    • Level – What level in the Subject hierarchy?
    • Subject – This is the name of the subject that Serials Solutions uses
    • Parent
    • URL

Contact: um.library.technical.services@maine.edu

Return to Technical Services Table of Contents.