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)

Step 2. Create the Excel file (NOTE: It does not matter in which order the columns are in, provided they are labeled correctly in Row 1 of Sheet 1 of the workbook).

  • Open Excel
  • Create a new document
  • Click on the Data tab
  • In the Get External Data section click on “From Text”
  • Select the text file that you created
  • Click Import
  • In the Text Import Wizard
    • Choose “Delimited”
    • Change the File Origin to “65001 : Unicode (UTF-8)”
    • Click Next
    • Under Delimiters check “Tab” and “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  [first cell of the blank sheet, upper left corner]
    • 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”  [typically around 5-6 thousand changes; if greater than 10K probably indicates file generation issue]
  • 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” [note compression of remaining columns with content to the left]
  • 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”
  • 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.

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:
  • Check the Recent Acquisitions page to see a full list of monthly reports for a 12 month cycle

*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 Gene and 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/

Return to Technical Services Table of Contents.

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