Technical Services: Uploading Monthly New Acquisitions to Web

Purpose: To explain the procedure for adding current month’s list of newly cataloged books to the New Acquisitions portion of the Subject Portals.

Get data from URSUS review file | Create first Excel spreadsheet | Upload Excel file to the Portal | Create Locations spreadsheet | Upload Locations sheet to Web Site

Each month Meg Guly will notify web staff by email when the latest list of newly cataloged items is available.  She generally includes the review file number.  Once the list is available, web staff will upload the Excel file to the database (ej.mdb).

Step 1. Get the data.

  • Go to the Create Lists function in Sierra
  • Select the file for the current month (File name is generally in the format: “NewAcq MonthYear
  • Click on Export Records
  • The Export Item Information dialog box choose the following options (see image below):
    • (Line 1) Type:  Item (i); Field: CALL#
    • (Line 2) Type:  BIBLIOGRAPHIC; Field: AUTHOR
    • (Line 3) Type:  BIBLIOGRAPHIC; Field: TITLE
    • (Line 4) Type:  BIBLIOGRAPHIC; Field: PUBLISHER
    • Field Delimiter: Choose “ASCII Character” and type in: @
    • Text qualifier: Choose “ASCII Character” and type in: “
    • Repeated field delimiter: Choose “ASCII Character” and type in: ;
    • Maximum field length: <none>

Sierra Export Screen

  • Click on Browse
  • Choose a convenient location and a descriptive file name (e.g. NewAcqJan10)
  • Files of type should be .txt (e.g. NewAcqJan10.txt)
  • Click Save
  • Click OK (It may take a minute or two to export the records)

Set up for Excel 365

  • Click File > Options > Data
  • Go to Show legacy data import wizards
  • Select From Text (Legacy)

Excel 365 File > Options

Step 2. Create the Excel file

Once the Legacy Wizard is enabled,

  • Select the Data tab > Get & Transform Data > Get Data > Legacy Wizards > From Text (Legacy)

Get Data > Legacy Wizards

  • In the Import Text File dialog box, double-click the text file you created
  • Click Import
  • In the Text Import Wizard
    • Choose “Delimited”
    • Change the File Origin to “65001 : Unicode (UTF-8)”
    • Check the “My data has headers” tick box
    • Click Next
    • Under Delimiters check “Tab” and
    • Under “Other” enter “@” in the text box
    • Text Qualifier should be 
    • Click Next
    • Click Finish
  • In the Import Data dialog box confirm:
    • Existing worksheet =$A$1
    • Click OK
  • Check that the column headers read exactly as follows:
    • Column A1: CALL #(ITEM)
    • Column B1: AUTHOR
    • Column C1: TITLE
    • Column D1: PUBLISHER
  • Check for apostrophes in the Call Numbers [Rarely found but worth the search as a stray apostrophe wreaks havoc with program]
    • Click on Column A: CALL #(ITEM)
    • Issue a Find command (Ctrl-F)
    • Enter in the “Find What” box
    • Click “Find All”
    • If there are apostrophes, you will need to get rid of them (e.g., change “Children’s Collection” to “Child Coll.”
  • Remove repeated fields:
    • Select all columns
    • Issue a Replace command (Ctrl-H)
    • In the “Find What” box, enter ;”*” (semi-colon, quote, asterisk, quote)
    • Leave the “Replace With” box empty
    • Click “Replace All”
  • Remove Extra Data Beyond Column D
    • Click on Column Heading E
    • While Holding SHIFT:
      • Press “End” key
      • Press right arrow key
    • Right Click [on your mouse]
    • Choose “Delete”
  • Choose Save As
  • Save Location: Desktop
  • Save as Type: Excel 97-2003 Workbook (*.xls)
  • Give it a descriptive name (e.g. NewAcqJan10)
  • Close the Excel file

Step 3. Upload to Portals. A form in the portal admin interface  has been created to facilitate this process. (Or Return to Step 4)

  • Open web browser
  • Go to: http://library.umaine.edu/subjectPortal/admin/logon.aspx
  • Login as “admin”
  • Click “Uploading Files”
  • Select the appropriate month from the “Month” drop-down menu.
  • Confirm that “Set Current Month” is checked and the auto-populated Note field coincide with the data being uploaded.

*ATTENTION* When loading December acquisitions in January of the following year, make sure “Set Current Month” is checked, but edit the text to the previous year. Example: Set Current Month will yield “December, 2020.” When loading this file in January, 2021 you must edit the “note” field to read, “December, 2021” to maintain the correct sequence of dates.

Acquisitions Upload Interface

  • Click “Choose File” to select the file to upload.
  • Under “Select a file to upload” click on the “Browse” button:
  • Find your Excel file
  • Click Open
  • Click Upload
  • If the upload is successful, you will receive the message “File successfully uploaded to the database” with a readout on the number of rows added.*

Acquisitions Interface Upload Successful Message

To overwrite a previous data load:

  • Uncheck the box next to “Set Current Month”
  • Under “Month” change the number to match the month you are uploading
  • Under “Note” type in the month and year as they should display on the New Acquisitions web pages (e.g. April, 2005)  (NOTE: Failure to include the comma between the month and year will result in misordering of the months.)
  • Click “Choose File” to select the file to upload.
  • Under “Select a file to upload” click on the “Browse” button:
  • Find your Excel file
  • Click Open
  • Click Upload
  • If the upload is successful, you will receive the message “File successfully uploaded to the database” with a readout on the number of rows added.*

Step 4. Confirm Success

  • Check the former Subject Portals for any subject using the line “http://library.umaine.edu/subjectPortal/acquisitions.aspx?subject=” and appending the desired subject, such as “Nursing” to make sure that the changes went through correctly. For example:

*If you receive a server error double-check the data in the Excel file, save it with a new name, try the upload with this new Excel file. If you receive a “505 Bad Gateway” error, email Curtis and attach a copy of the Excel file you created for analysis.

Step 5. Create Locations spreadsheet
(NOTE 03/12/2018: Per Deb, the Excel spreadsheet uploaded to WordPress must include location codes. Meg typically generates this spreadsheet and emails it with notification that each month’s acquisitions are available for upload. If, for some reason, Meg does not provide a spreadsheet, generate one using the following steps):

  • Go to the Create Lists function in Sierra
  • Click on Export Records
  • If necessary add a field for Locations by doing the following:
    • Place cursor on Line 1, click Insert
    • Under Type, enter “i”
    • Click Tab
    • Under Field, enter “1”
    • Click Tab

The export should read a follows (See illustration below):

(Line 1) Type: Item (i); Field: LOCATIONS
(Line 2) Type: Item (i); Field: CALL#
(Line 3) Type: BIBLIOGRAPHIC; Field: AUTHOR
(Line 4) Type: BIBLIOGRAPHIC; Field: TITLE
(Line 5) Type: BIBLIOGRAPHIC; Field: PUBLISHER

  • Field Delimiter: Choose “ASCII Character” and type in: @
  • Text qualifier: Choose “ASCII Character” and type in: “
  • Repeated field delimiter: Choose “ASCII Character” and type in: ;
  • Maximum field length: <none>

Sierra Export Dialog Box

  • Click on Browse
  • Choose a convenient location and a descriptive file name (e.g. NewAcq-LocationsJan10)
  • Files of type should be .txt (e.g. NewAcq-LocationsJan10.txt)
  • Click Save
  • Click OK (It may take a minute or two to export the records)

Repeat Step 2 above to create a spreadsheet, then follow with Step 6 (below):

Step 6. Update Recent Acquisitions Web Page

  • Login to Fogler Library WordPress website
  • Click on All Resources link
  • Find the “Recent Acquisitions [month year]” resource and click edit
    • Next to “Published on: [date],” click edit
    • Change the date to the first day of the month of acquisitions being posed.
    • Click OK
  • Under “Replace the Document”
    • Hover mouse over the chain link graphic,
    • Click Delete Permanently
    • Click OK
  • Next to “Replace the Document”
    • Click the Choose File button
    • Select the appropriate file from your desktop
    • Click Update
  • Change the month and year in the page title display (DO NOT change the permalink)
  • Click the Update button
  • Check appearance of the published page at https://library.umaine.edu/resource/new-acquisitions/

Step 7. Log Out

When you are done working in the Subject Portal Staff Interface, it is important to click the “Log Out” link, as it will properly close out your work session. Just closing or quitting out of your browser can lead to unresolved work sessions. If you encounter issues, clear your browser history, cache, and cookies. Close out your browser, then open a new browser window and navigate to the login page.

Technical Support

The subject portal was originally created by independent contractor, Curtis Meadows. If and when you encounter malfunctions, please follow department protocols for contacting Meadows to provide technical support.

Return to Technical Services Table of Contents.

Contact: um.library.technical.services@maine.edu   | Last updated: 07/10/2019 KJS