UB Stat 5: Unfilled Requests, Import Directions

1. Save the ubstat_5_xxx_mmyy_for_import.txt file file to your computer from your library's CARLI FTP account.
2. Open Microsoft Excel.
3. In the File Menu, choose Open, and then browse for the file on your computer. Select the file to open in Excel.
4. The Text Import Wizard will open.
5. In the Text Import Wizard, step 1:
   
   1. Set the file type as "Delimited".
   2. Check the checkbox for "My data has headers".
   3. Click Next.
6. In the Text Import Wizard, step 2:
   
   1. Set the delimiter to "Other" and enter the pipe symbol "|" (the pipe is Shift + backslash "\").
   2. Click Next.
7. In the Text Import Wizard, step 3:
   
   1. update the formatting for all of the fields to "Text".
      • If desired, you can chose to set the DATE_REQUESTED to the Date formatting, YMD style.
   2. Click Finish.
8. Your data is now imported.
9. Save the file as an Excel Workbook.
10. Optional edits: In the View menu, choose Freeze Panes, then "Freeze Top Row".

1. Save the ubstat_5_xxx_mmyy_for_import.txt file file to your computer from your library's CARLI FTP account.
2. Open your Voyager Microsoft Access Database.
3. Note: If you have imported a dataset previously, you may want to rename the table named "UBStat5Import" that is already in your database so that it is not overwritten by the steps below. Right click on the existing UBStat5Import table, and rename it with the date it contains. For example, "UBStat5Import1605."
4. Click on the External Data tab; under Import & Link, select Text File.
5. The "Get External Data" window will open. Browse for the file on your computer.
   Under "Specify how and where you want to store the data in the current database", choose: "Import the source data into a new table in the current database." Click OK.
6. The Import Text Wizard will open.
7. In the Import Text Wizard, step 1:
   
   1. Set the file type as "Delimited".
   2. Click Next.
8. In the Import Text Wizard, step 2:
   
   1. Set the delimiter to "Other" and enter the pipe symbol "|" (the pipe is Shift + backslash "\").
   2. Check the checkbox for "First Row Contains Field Names".
   3. You may get a warning that says "The first row contains some data that can't be used for Access Field Names. In these cases, the wizard will automatically assign valid field names."
   4. Click Next.
9. In the Import Text Wizard, step 3:
   
   1. Click on each column in order to highlight it; make sure every column in the data is set to "Short Text" (or "Text" in some versions of Access).
   2. The fields you will most likely need to change are DATE_REQUESTED, CALL_NO_TYPE, and FINAL_REQUEST BIB_ID.
   3. Click Next.
10. In the Import Text Wizard, step 4:
    
    1. Let Access add a primary key.
    2. Click Next.
11. In the Import Text Wizard, setp 5:
    
    1. Set the "Import to Table" name to be: UBStat5Import
       You must use this table name to run the associated shared SQL, discussed in the next section on Analyzing the Data: Access Queries and VuFind.
    2. Click Finish.

Microsoft Access Queries

Notes that apply to reducing duplication in all queries:

• Most titles have only one NORMAL_HEADING_2450, however, it is possible for a title to have more than one, especially if the title is in another language. 
  • Titles with multiple NORMAL_HEADING_2450 entries may make it appear that the title had additional patron requests.
  • If a title has multiple rows (both in the original report or in the results of Microsoft Access shared SQL reports), and all of the data for the title is identical (same DATE_REQUESTED, same FINAL_REQUEST, same FINAL_REQUEST BIB_ID), check to see if there are multiple, varying NORMAL_HEADING_2450 entries. 
    • If the NORMAL_HEADING_2450 entries are identical, the multiple rows likely represent multiple unique patron requests.
    • If the NORMAL_HEADING_2450 entries are different, the multiple rows represent the same patron request.
  • Highlight or delete your preferred extra NORMAL_HEADING_2450 row before sharing the statistics or sharing the report with collection developers.

• The UBStat5 Conspectus Query set
  This query set from the CARLI Shared SQL compares the titles listed in the UBStat5 report (ubstat_5_xxx_mmyy_for_import.txt imported into Access following the directions above) with the Conspectus Categories to help collection development by subject area.
  • This query set may contain multiple rows for a single request, if the requested item's call number is categorized under multiple conspectus categories.
  • This query set will contain a request even if it does not match with a Conspectus Category. In those cases, the DIVISION and CATEGORY fields will be blank.
  • When importing the data into Microsoft Access, the directions above suggest that you allow Microsoft Access to add a primary key; as a result, each single patron request in the table is tagged with a unique "ID" number in the ID column.
  • Steps to use the primary key to denote the duplication for your collection developers:
    1. Export (or copy/paste) the query results from Microsoft Access into Microsoft Excel.
    2. Highlight the entire "ID" column (column O).
    3. On the Excel Home tab, select Conditional Formatting> Highlight Cell Rules> Duplicate Values. Select the formatting you'd like to apply to the duplicated IDs and click OK.
    4. Each ID that is highlighted as a duplicate value is already included elsewhere in your spreadsheet, most likely under a different conspectus category.
    5. You can further highlight these rows to let your collection developers know which IDs represent a single request, but were included in the results under multiple conspectus categories.
        
• UBStat5- Normalized Title Match- Titles In Collection
  • This query set returns a list of results where the normalized titles (NORMAL_HEADING_2450) in your library's database match the normalized titles requested by your patrons.
  • This query's results have a high chance of containing multiple rows for a single request.
    • Multiple rows will happen when:
      • the requested item has more than one NORMAL_HEADING_2450 value.
      • multiple distinct items have the same normalized title.
      • your library owns multiple versions of the same title on different bibs, perhaps with different authors or different publication dates.
  • The Author, Publisher, and Publisher Date columns from your library and from the request have been included for comparison.
    • The publication date the patron requested may not match the publication date your library owns.
      • Review the YourLibPubDate and RequestPubDate columns in the query results.
  • If your library owns more than one BIB with the same normalized title (BIB_INDEX 245 field =NORMAL_HEADING_2450), there will be multiple results due to the title match.
  • You can use the IDForDeduplication column to de-duplicate many of the results.
    • Each IDForDeduplication is unique per a single I-Share request made by your patron, with the exception of rows created by the requested title having more than one NORMAL_HEADING-2450 value (which may have more than one row in the original report).
  • Interpreting query result, examples:
    UB Stat 6 and UB Stat 5 are formatted the same; these UB Stat 6 examples are applicable for UB Stat 5.
    • Example 1: Three separate patron requests match one bib at home library
    • Example 2: Single patron request matches three separate bibs at home library
    • Example 3: Single patron request has a false match with 31 bibs at home library
    • Example 4: Four patron requests match on two bibs at home library; one exact match, one similar match
    • Example 5: One patron request matches eight bibs at home library; no exact match, two similar matches
    • Example 6: One patron request had two NORMAL_HEADING_2450 results, matching on one bib at home library
       
• UBStat5- Normalized Title Match- Titles Not In Collection
  • This query set returns a list of results where the normalized titles (NORMAL_HEADING_2450) requested by your patrons did not match the the normalized titles your library's database.
  • This query's results have a high chance of containing multiple rows for a single request.
    • Multiple rows will happen when:
      • the requested item has more than one NORMAL_HEADING_2450 value.
  • Since there was not a match in your library's database, the results of this report display the record information for the requested title. Using the OCLC Number (NETWORK_NUMBER), ISBN or ISSN, plus Title, Author, Publisher, and PubDate fields can help you identify a title, should your library wish to order it for your collection.
     
• UBStat5- OCLC Number Match- Titles In Collection
  • This query set returns a list of results where the OCLC numbers (NETWORK_NUMBER) in your library's database match the OCLC number for titles requested by your patrons.
  • This query's results have a high chance of containing multiple rows for a single patron request.
    • Multiple rows will happen when:
      • the requested item has more than one NORMAL_HEADING_2450 value.
      • your library owns multiple versions of the same title on different bibs.
  • Matching on the OCLC number is a more specific match than NORMAL_HEADING_2450; as such, the query results require less interpretation than the "UBStat6- Normalized Title Match- Titles In Collection" results.
  • Since OCLC numbers can change over time as duplicate records are merged and deleted in OCLC's global WorldCat catalog, a match could be missed by this query if either the OCLC number in the REQUEST LIB BIB_ID or your own library's record was not updated to the newest OCLC number.
  • Either your library, or the REQUEST LIB, may occasionally store non-OCLC numbers in the NETWORK_NUMBER field. Unless both libraries have the same value entered, a match will not be made.
     
• UBStat5- OCLC Number Match- Titles Not In Collection
  • This query set returns a list of results where the OCLC Numbers (NETWORK_NUMBER) for titles requested by your patrons did not match the the OCLC Numbers your library's database.
  • This query's results have a high chance of containing multiple rows for a single patron request.
    • Multiple rows will happen when:
      • the requested item has more than one NORMAL_HEADING_2450 value.
  • Since OCLC numbers can change over time as duplicate records are merged and deleted in OCLC's global WorldCat catalog, a match could be missed by this query if either the OCLC number in the REQUEST LIB BIB_ID or your own library's record was not updated to the newest OCLC number.
  • Since there was not a match in your library's database, the results of this report display the record information for the requested title. Using the OCLC Number (NETWORK_NUMBER), ISBN or ISSN, plus Title, Author, Publisher, and PubDate fields can help you identify a title, should your library wish to order it for your collection.
  • Either your library, or the REQUEST LIB, may occasionally store non-OCLC numbers in the NETWORK_NUMBER field. Unless both libraries have the same value entered, a match will not be made.

Reviewing Data in VuFind

If you would like to know more information about the requested item than is present in the ubstat_5_xxx_mmyy_for_import.txt file itself, you can use the data from two columns to locate the Bib record in VuFind.

• The FINAL_REQUEST column contains the three letter code of the library where the request expired (became no longer eligible for further promotion).
• The FINAL_REQUEST BIB_ID column contains the BIB_ID number at that library.

Plug this data into the base VuFind URL:
http://vufind.carli.illinois.edu/vf-xxx/Record/xxx_#
where xxx is the FINAL_REQUEST library's three letter code, and # is the FINAL_REQUEST BIB_ID.

Example:

Return to top