Page tree
Skip to end of metadata
Go to start of metadata

See Textbooks FAQs for help with questions about how the data is used.

For the Monographs textbook workflow, see  Textbook Workflow - Monographs

Monographs will request the Textbooks list for the new term from the campus book store on schedule. Ashleen Norris will email an Excel list of newly adopted textbooks for the current term to DPP.  There are 3 lists per term. Each list will be unfiltered with duplicate ISBNs within the term and for previous terms.

Before proceeding, create a new semester subfolder under the DPP\Textbooks\semesters folder on the G drive, if needed. Also create a Book Store Lists subfolder, a Sirsi Temp Orders, and a Textbook Search List subfolder (Note that no Sirsi Temp orders subfolder is needed for summer semesters).

Creating the new Bookstore List.

  1. Download the bookstore list in the shared bookstore Google drive to Excel.  Delete all previously worked sheets, keeping only the current list in the workbook.

  2. Verify that there is a new folder for each new semester (ex. "Fall 2016") here: G:\Acquisitions & Discovery\Data Projects & Partnerships Unit\Textbooks\semesters.  

  3. In that folder, save the bookstore list as an .xlsx file with file name *YrSemBookStoreList[Tab Date\].xlsx*.  Save as List 2 & 3 in this folder, as well.  Check the ISBN-13 and -10 cell formats.  Make sure that the number category has no decimal places so that the values don't revert back to scientific notation. You will typically receive three lists from the bookstore, but you will receive and process each list at different times.   (For further/future investigation: ISBN-10s with no leading zeros, and also those with check digits that include leading zeros.) Save your edits.

  4. Copy all columns.

  5. Open the "Textbooks" database here G:\Acquisitions & Discovery\Data Projects & Partnerships Unit\Textbooks.

  6. In the textbooks database, create a new table and paste the copied columns from the spreadsheet.  Check to see all columns were copied.   Delete Requested? and up to and including the Return columns (which will have no data).  Retain Copyright and Edition columns.  Delete the extra space after the underscore in List_New, Net_Used, List_Used, and Net_Used fields.

  7. Name the table NewBookstoreList.  When processing each subsequent list from the bookstore, repeat this step, overwriting the data in the NewBookstoreList each time.

  8. OK to omit this step as of 10/2019? On the database's Main dashboard, hit the Delete Pipe Delimiter Columns From New Bookstore List button. 

  9. Make a COPY of the resulting table and save as YrSemBookStoreList. Each list for the same semester will overwrite the previous version of this table. Note that the NewBookstoreList table is still needed for the next few steps.

  10. Run the NewDeptCodes query to identify new codes created since the previous semester.  
    1. If results are found, forward to Collections and Research Strategies head for decision on which library/branch will hold the textbook on reserve.  When location decisions are made, add code with location to the DeptCodes and DeptCodeExceptions (where applicable) tables in the database, and also to the Course Codes Exceptions page, if needed.  If the code is attached to a Hill location, also add it to the HillCrsCodes table.  
    2. If no codes are found, move on to the next step.

Beginning with Spring 2018, the Current Semester Book Store List will provide the most up-to-date title list provided by the bookstore.  Upload the current list to the page, and make sure the Notify watchers box is checked. Also email the update to  

Create a new Textbook Search List (new workflow as of Spring 2021 – see procedure at end of page).

Once Dawn has been sent the file to run her script, continue processing the new bookstore list by Creating the Deduped Bookstore List.  When Dawn sends back the file for the Search List, finish that process, as well.

Creating the Deduped Bookstore List.

  1. In the Textbooks database, Hit the Deduped Bookstore List button. This will generate a list of titles with unique ISBNs. (Paste a copy of these results into a new table: DedupedSemYrList#).)
  2. Next, hit the Textbook ISBN List for Batch Search Against NCSU Catalog button.
  3. Either copy results into a text file or export results as a text file called Textbooks [tab date].txt  (e.g. Textbooks 10-23-15.txt) in the semester folder on the G drive.  
  4. Delete ISBN-13 header.
  5. Open MarcEdit.
  6. Select the MarcEdit Z.3950/SRU Client → select Batch Search
  7. the Query Database should be NCSU.  If that database needs to be added, go to Edit Settings

Find Host and Port data here

  1. Click the yellow folder to select the Source file, which is the text file with the ISBN-13s and ISBN-10s.

      9. Select the Retrieve Unicode box, then MARC8 from the dropdown.

     10. Hit the Search button, and from the new screen popup, select the same folder destination with same file name, Textbooks,  and same tab date.  The file will be saved as an mrc. 

     11. Highlight the Results box, use Control+A to select all, then Control+C to copy Results into a text editor (use NotePad?).   Lisa: See folder notes about Type: "File" v. Type: "Text Document" in the File Explorer.  As a general rule, add .txt to file name to make it convert properly.

In the text editor:

  1. Use Replace to delete everything but the ISBNs whose search produced "0 records found." OR (depending on version of MarcEdit), go to bottom of list and copy all ISBNs after *****Zero Results****** into a new text file.

  2. Save as Textbooks ISBNs Not Found (Date).txt in semester folder.

  3. In the database, import the file as a new table named TextbookISBNsNotFound.  Keep as Fixed width, rename Field 1 as ISBN, and make the Data type "Short Text" and select the No primary key option.

  4. The existing TextbookISBNsNotFound table will be overwritten with each new bookstore list. If the "not found" phrase shows up after import, delete the field.

Creating the Order list

  1. In the database, hit the Order List button to run the order list query.  When prompted, enter the tab date of the list.  The query result will get put into a new table called Table1.  Copy Table1 rename the copy YrSemPrelimOrder table (to preserve the original results). Table1 does not have to be deleted, it will be overwritten each time a new list for the same semester is received.

    Compare the results of the Order List query (Table1) with results from using Conditional formatting in Excel.  See Creating textbook order list using Excel (draft doc currently located here).  Append any new titles identified through this method to Table1.  Make a copy and save as YrSemOrdered.

    Repeat the above step for the second and third list, where Table1 will be overwritten each time the Order List query is run.  For these second and third lists, create the preliminary order list, use the Excel conditional formatting procedure to identify any titles missed that should also be ordered.  Add these additional titles to Table1, making sure that the tab date has been added to them.  Then copy and append all of the rows in Table1 to the YrSemOrdered table. The best way to append is to select the Author column, hold SHIFT and click on the last column in the row (exclude the ID column if there is one), copy.  Then in the YrSemOrdered table, select the cell of new row in the Author column, SHIFT+RT ARROW over to the last column, paste.

  2. Go back to Table1 and export as an Excel spreadsheet named Library List with Selections (tab date) in the Book Store Lists folder.  For Summer orders, name the folder Order List, since summer orders are not placed with the bookstore.

  3. For Fall and Spring semesters, send the completed Order list, named Library List With Selections (Date) to Ruth and Lynn, ccing Jacob.  Monographs will assess whether or not ebooks can be purchased in lieu of print, and then they'll update the Textbooks folder on Google Drive with EDITED list (for fall and spring orders).  For the summer semester, they will retain the revised list to place Amazon orders .  
    Ruth will send new order lists to Alex Reher (Access Services) and Silvia Sheffield (NRL).

Closing out the data by updating the AllTitles table

IMPORTANT: Once the YrSemOrdered table has been established, and the order list has been sent to Monographs, Ashleen's newest list (the NewBookstoreList table) needs to be appended to the AllTitles table. The AllTitles table is a comprehensive, de-duped list of all textbooks titles assigned/used since 2008. This provides a set of all titles for which we would have ideally purchased at least one copy of for the Textbooks program.  Update and de-dup the AllTitles table using the following steps:

  1. Verify that that AllTitles table has a Term field.

  2. Copy the entire NewBookstoreList table (delete ID field if it exists) and append it to the AllTitles table by selecting the first cell in a new row, then SHIFT ARROW over to Edition, then paste.

  3. Export the AllTitles table and save as AllTitles.xlsx in the current semester folder.

  4. Each new list in the semester will be saved in the current semester folder, the previous file (each of the two earlier lists of the semester) will be overwritten each time.  (There is only one summer list.)

  5. Open the sheet.

  6. De-dupe it by selecting all, go to Data tab, Remove duplicates on ISBN-13. 

  7. Starting at the Term column, select over to the last column (Edition) and copy.

  8. Paste into a new table in the database.

  9. Name it AllTitles to replace the one that was just appended with the new list titles. The results will be a comprehensive list with no ISBN-13 duplicates. Again verify that the Term field was not lost in the paste job. 

Pull List for Monographs head (process for each dated list)

Even though we discontinued the creation of a Pull List for Hill and the branch libraries, we still provide the Monographs head with a list of titles we own (pull list) for each bookstore list.  This is used to assess owned ebooks availability, and increase number of users where possible. Use these steps to prepare the Pull List. Highlight any Kindle editions for Lynn that don't have ebook counterparts.

(The entire discontinued process for preparing the Pull Lists for Hill and branches is archived here: G:\A&D\DPP\Textbooks\Documentation\Pull list procedure to ID ONLINE titles for the archived process.)


Shipped list

Ashleen will return the list with order and shipment information. If more than one list is sent at a time, keep them separate for easier data prep.
Here are the possible status updates that may appear on the updated spreadsheet:

   BO - Backordered: If the notes say Backordered they were not in stock at the time and once we started getting more books in we would go back and check out what was still missing on your lists.

   CX - Cancelled: These are no longer needed for a course or the course has been cancelled. These orders will be cancelled in Sirsi

   SO - Special Order: Special orders are for specific people (teachers/students) one copy at a time when asked. Those books are not for classes and are not books that you need to purchase. Ignore them as you would a cancelled book.

   IEP courses - that is a special class designation. do not order.

For more information, see the first section of this document: Book Store Workflow Breakdown.

To enhance a NEW order list that's been returned by Ashleen (not an update) that now needs processing for the shipment:   Save each processed list in the Book Store Lists folder, as Library List with Selections Processed (Tab date).xlsx in the Book Store Lists folder. (If updated lists from earlier tabs are sent, open that previously processed list and add in backorder Net prices, DO NOT change "BO" to "N" or "U", and keep any "BACKORDERED" notes, then Save.  See ** below.)

           In Excel, open the Processed list.

  1. Custom sort by Filled? (N/U)
  2. If necessary, add in a Tab_Date column to the new spreadsheet (if necessary, move this column position so that it matches the Shipped table position and add an underscore if it's lacking)
  3. Date_Filled column not necessary; delete other columns (Library Notes, Return Date) not seen in previous shipped tables in database.  
  4. add price by copying appropriate column (net new or net used) and pasting into a new column at the end of the sheet called Amount_Charged, so that after Edition, the order of columns is Tab_Date, N/U, Notes, Amount_Charged. 

When working with the first list of a new semester:

  1. create a Shipped table in the database; preferred method: select the first cell in processed list sheet, move mouse for white cross to appear, select all data rows and columns, (also can import the spreadsheet into the database but extra blank rows and columns may be created).
  2. save as YrSemShipped table for the first list of semester

For the second and third lists, append them to the Shipped table (make sure there are no blank rows in the shipped table).  

If any title has not yet been shipped, Amount_Charged field should be blank, not $0.00
**If Ashleen sends an updated (for filled backorders or canceled courses) list from a previous date tab:

  1. Once the previous Library List with Selections Processed list has been updated with backorder pricing,
  2. filter the Shipped table by tab date, then
  3. DELETE all of the records and REPLACE by pasting in the newly updated Processed list for that tab date 

(OR filter the Shipped table by corresponding tabbed previous date, manually add prices for any highlighted backordered titles)

check totals each time a list is sent or updated

this will provide the most up-to-date status in Shipped table
Copy and paste the ENTIRE Shipped table (unfilter if necessary) into a Price List Google sheet for Ruth. When working with the 2nd and 3rd shipping lists, or any updated backorders, the Price List is replaced each time (or add another sheet clearly marked as an update).
sum the Amount_Charged

Compare the total charged to the YrSemShipped table to ensure that no titles were missed.
Any edits (Returns info) Ruth made to a previous version of the semester Price List will be lost when you paste the new one but that's ok
She will maintain separate Returns/Backordered list.

Share Price List doc with Ruth. 

Any textbooks that need to be returned to the book store will be held by Ruth until the end of the entire ordering process.

Create temp order records

NOTE: Per Ruth, it is usually unnecessary to run any separately shipped backorders through this process providing that the shipment is relatively small.  Monographs can usually manually create orders and catalog the backordered textbook titles. (For backordered titles, the dept. codes of filled backorders will have to be checked against the bookstore list and the exceptions table to determine the correct destination library.) So, usually only three temp order lists per semester will be created, corresponding to the three original versions of each list (tab date) sent back by Ashleen.  However, if a large number of backorders are filled at the same time, consult with Ruth to see if a separate temp order record file should be created for batch loading in order to save time.  If Monographs feels the need for this to happen, run the Filled BOs With Dept query (update SQL with current semester) to create a temp order file for Dawn to load.

*Make sure that the latest bookstore list  has not only been saved as YrSemBookStoreList\[Tab Date\].xlsx in the semester folder, but that the YrSemBookStoreList table has been updated by overwriting* any previous version from that semester.  See *Creating the new Bookstore List* (above).  Check record totals in the database against the last  .xlsx list in the semester folder.  If the YrSemBookStoreList was not updated, the table record total will be smaller than the totals on the last .xlsx list, and the *TempOrderQuery* below won't work because it won't be able to find the most recent tab date.

update table references in TempOrderQuery for each new semester; change ISBN data type to Short Text in the Shipped List

run the query; if "TabDate" parameter error occurs, add in an underscore to match the query 

enter the date parameter for the latest Tab_Date on Shipped list (Remember that all results should have a $ amount in Amount_Charged, no $0.00.)

paste results into spreadsheet

under the Data tab, Remove Duplicates based on isbn-13

delete tab date

now sort by 

- Sort spreadsheet by course code (Dept).  Exclude headers.

-*IMPORTANT* : Check the ISBN13s to make sure they are not expressed in scientific notation.  Reformat category to "number" with 0 decimals, if necessary.

 Organize the orders by library based on the course codes:

 - Cut and paste all non-Hill titles at the bottom of the list.  (NOTE: during 2020 COVID-19 pandemic, the Design Library is open, so continue to set that location for appropriate department codes.  During this time, assign any NRL department codes to HUNT, but let Ruth know of any so that she can add a note to the order record designating it as an NRL title.)  Add the name of Hunt or branch to the next empty column at the end of the newly pasted row.  Delete any spaces left at the top of the list when the row was cut.

  • Delete Dept/course code column
  • Delete headers

  • Save in the Sirsi Temp Orders folder as Ordered Titles by Library (Date).xlsx.

In MarcEdit, under Tools, select Delimited Text Translator.  Select the Excel file as Source file, then create an .mrk file with same name, as Output. Make sure that the UTF-8 box is NOT auto-checked once you've selected the files. 

See below:

Hit Next.

Select Load Template button.  In the Textbooks folder, select the NEWTextbook Temp Orders.mrd template file under the MAIN Textbooks folder.  Review fields to make sure that they are correctly matched.  The Arguments will auto-populate.  Make sure that the Ignore Header Row box is NOT checked.


If template is not available, manually enter fields as below:

Select each field as below and hit Add Argument

  • Select field 0, map to 100$a, indicator is \1
  • Select field 1, map to 245$a, indicator is 00
  • Select field 3, map to 020$a, indicator is _ _
  • Select field 2, map to 020$a, indicator is _ _
  • Select field 4, map to 980$b, indicator is _ _
  • Select field 2, map to 949$i, indicator is _ _

After creating the file (in Sirsi temp orders\Ordered Titles By Library (date).mrk), open it in MarcEditor.  To use the macro to add field data, go to Tools>Manage Tasks, and Import the task file found in the DPP Textbooks folder on the G drive, named TextbooksTempOrders.task. 

If you've previously imported the task file, select under Assigned Tasks.  Once the task file has been added to the MarcEditor tools, choose it from the Assigned Tasks menu and run.  The following fields should be added to each record.:

  • Field 500 Field Data _ _$aBook Store Order
  • Field 982 Field Data _ _$b300101$APD
  • Field 984 Field Data _ _$a20120813$cNCSUBKSTORE (Make sure to change $a to the CURRENT date)
  • Field 985 Field Data _ _$aLSFIR-TEXT$eNCSUBKSTORE (Check to make sure that this field looks like this: =985  \\$aLSFIR-TEXT...  with two backslashes (for blank indicators) before $a.  If it doesn't, the field will be out of alignment with the other fields so add the backslashes.
  • Field 986 Field Data _ _$aRUSH
  • Field 987 Field Data _ _$aTXBK

    If the task file is not available, the fields can be added manually:
    Tools> Add/Delete field and add the above fields (REMEMBER: In Field Data enter two backslashes (no spaces before or in between), then the subfield.  It's probably a good idea to check the report for errors each time the fields are edited.):

Then under Tools, select Edit Subfield Data and check off the New subfield only box (also keep Match case) and replace text with these:

  • Field 980 Subfield g Replace with 1
  • Field 949 Subfield k Replace with ON-ORDER


Edit the 949 by clicking on the file. Do a Find and Replace as seen below:

  • Find what =949  _ _$i

Replace with  =949  _ _$hHIL-TEXTBK$i (after the "949"spacespacebackslashbackslash - with no space between the backslash indicators, which are denoted here as two underscores) After the mrk file is created eyeball it to make sure all of the spacing is correct in the fields, ESPECIALLY the 949.

Edit the 980, if the price listed in $b still carries the $ in front of the dollar amount. It needs to be removed, since it should look like this: =980 \\$b21.6$g1. Do a Find and Replace as seen below:

Go to the END or LAST PAGE of the file and manually edit the $hHIL-TEXTBK$kON-ORDER on the non-Hill library orders to correct library.  There are generally only a few of these and this is the reason why they were sorted to the bottom of the Ordered Titles by Library.xlsx at the beginning of this process. Match up the branch titles at the bottom with the record in the MarcEdit report and edit the 949s.    Ex: $hHUN-TEXTBK or $hNRL-TEXTBK or $hDES-TEXTBK

Save your changes.

Example of what the record should look like:

After the temp order records are created, send this Ordered Titles by Library (Date).mrk file to Dawn and she will load them into Sirsi using a script that creates the order records automatically. Dawn prefers the .mrk flat file format (for various reasons). However, if she is on vacation (as she may be for the first list of the fall semester) send an .mrc file to Dawn AND cc Adam Constabaris, who will load the temp records in Dawn's absence. If there are only a few records (e.g. backordered titles are sent), Monographs can probably enter the order records manually. Check with Ruth if circumstances deem this to be the preferred method.   Dawn's script may produce some errors, which will be forwarded to Ruth (see email example below) because they are likely duplicates.  (Within one semester, multiple files will be sent to Dawn, so it's best to append each of the second and third request emails to the first, for keeping better track.) Also, once the temp orders have been loaded, Dawn will email back. If she doesn't attach any errors, double check with her to be sure there are none.

Email Ruth any of the errors, and also let Ruth, Lynn and Jacob know that the records have been loaded.

When the next new list is sent from the bookstore, it's time to create:

Textbook Search List

MAIN CONCERN: Need to make sure that our columns match those in the search list tool.  SCOPE: We only send lists based on bookstore data.   We don't do manual additions.

NOTE: This procedure may be slightly edited as DLI makes improvements on the search tool, most notable is the ability for DPP to upload the list to the tool without assistance.

Former DLI affiliate Jason Casden created a tool that allows students to search for copies of textbooks assigned for the new semester that are owned by the library. It allows students to search by course to find the available library-owned textbooks. After each shipment, DPP provides DLI with a list that powers this tool.  See the NCSU Textbook Search Tool search page.

Begin by opening the YrSemBookStoreList Access table,

Copy all of the 10 and 13 digit ISBNs and paste as a single column into a text file(ok to leave blank lines but delete both column headers). Save as Full ISBN List (Today's Date).txt in the current Textbook Search List folder. Using the MarcEdit Z39.50 client, run the ISBN list against the NCSU catalog using Batch Mode, and export the results as an mrc file called (SEMYr)Textbooks (Today's Date).mrc to the folder. This process takes a few minutes.

Close the Z39.50 client window when search is done.

From the main MarcEdit menu, select Tools > Export > Export Tab Delimited Records, select the mrc file just created by the Z39.50 search and name the new file Catkeys - Full ISBN List date.txt.  Export tab delimited records with only the catkey in 918 field.


Edit the Catkeys - Full ISBN List date.txt file by deleting 918 $a and the "s.
Copy and paste into Excel.

Click “Remove Duplicates” on the Data tab to remove duplicates and use Find and replace “;*” to delete multiple catkeys in one field. 

Save as Textbook Catkeys Today'sDate.xlsx in the Textbook Search List folder.

Send Dawn the file of catkeys and she will run a script which creates an Excel file with these columns : Catkey, Title, Author, Callnum, ItemID, ItemType, Home Location, Current Location, Library, and a field for each ISBN (sometimes numbering up to 30).  (While waiting for this file from Dawn, resume processing of the newest bookstore list by Creating the Deduped Bookstore List.)

Save the downloaded file in the Textbook Search List folder for future reference, keeping the same date Dawn assigns the spreadsheet, like DawnTextbooks_YRMODAY.xlsx Import the entire Excel worksheet into a new table in the Textbooks database with the name DawnTextbookData(with date of her file:YYYY-MM-DD) Remember to check off the box saying "First Row Contains Column Headings" when importing. WARNING: If you just copy and paste Dawn's spreadsheet into a new table, the field names may not be renamed with Dawn's headers, so the headers show up in row 1 instead. If this happens, the cleanup query described below will not work. The fix for this is to export the spreadsheet into another new table, and then check off the box "First Row Contains Column Headings."

Scan the multiple ISBN column names to make sure that they are in proper numerical sequence. Any completely blank columns can be deleted (check by filter function before deleting).

Select the entire table to run two Find and Replace actions to delete the "\a" and "\z" in the ISBN columns.  Make sure the “Match” dropdown menu is set to “Any Part of Field.”

To remove the text from the ISBN columns, first open the Design view of the Dawn Textbook Data ISBN Clean Up Query, update for the latest DawnTextbookData(Date) table name, edit the query for the correct number of ISBN columns, then run. Use “Find and Replace” to delete a left parens and anything after it (see screenshot below); do the same for a colon and anything that follows (colon backslash c*, e.g. ":\c$14.00").   There might also be some "\q*"s to delete, or "pbk." Use the filter function on each column wherever possible, and a visual scan, to find any oddballs.

In order to match up a bookstore ISBN with a catkey, the Textbook ISBNs & Catkeys Query will need to be updated and run.
1. Using the SQL View, verify that the Textbook ISBNs & Catkeys Query is pulling the appropriate number of columns for the file that Dawn produced and delete (or add) as necessary.  Copy the text of the query into a text file and use Find and Replace (Ctrl+H) to update the previous date with the new date.  Copy the updated text and paste it back into the Textbook ISBNs & Catkeys Query

2. Run the Textbook ISBNs & Catkeys query.

3. In order to update the Textbook Search List query with correct tables for the list you're working on:

Right click on the query for Design view, copy the query and paste into a text editor. Use Find & Replace to edit all bracketed tables with correct semester dates.  Select all, copy and replace the query. Check the Bookstore List table to ensure that data types are consistent for similar fields (i.e. text or number).  Typically this will happen on the ISBN-13 or ISBN-10 columns; convert the Book store list number columns to short text columns to solve the problem.

Run the Textbook Search List query. (This takes a few seconds.) On the External Data tab, export the results of the query into an Excel file with the name Textbook Search List [Date], into the Textbook Search list folder (test: make sure ISBN column types are imported as Text). In most cases, use the date Dawn ran the script, or the latest bookstore tab date for the file.  In Excel, select all, click “Remove Duplicates” on the Data tab and dedupe based on all of the columns, since there may still be same name titles used for multiple sections, and all would need to be retained in that case.  If there are blank catkey fields it means that these titles have not yet been shipped.

Add “short desc” column at the beginning of the Textbook Search List (Date).xlsx and use the Fill down tool to fill in the name of the term (make sure that it's in this form: Fall '18) for all rows.  Also, check that column names match the previous Textbook Search List file that was sent to DLI.  Variations may occur, and the textbook search needs consistent column names.  Note that some inprocess barcodes may be formatted for scientific notation.  Leave these as is, because chances are we have not received these titles yet and therefore these barcodes are not typical .  When in doubt, check the catkey in Sirsi to confirm status.  

***Filter the sheet for any titles with CX status and delete them before uploading to the search tool. 

***Save As "Textbook search list (Date) with Kindles."  Select the ISBN-13 column and format the cells as Text (if not already saved that way from import).  Then filter the sheet for Kindle titles: Data, set Filter for Call number, scroll down list and check off any Kindle call number along with its ebook or print counterpart, if it has one.  From this filtered list, identify any Kindle title that does not have an ebook or print counterpart.  Copy that row to a new Sheet1.  Continue working through the filtered rows result, deleting any Kindle title (these will have TECHLEND locations) that DO have an ebook (EBOOK/NET) counterpart listed.  Then note any Kindle title whose counterpart is NOT online.  These print titles will have COREBOOK/TEXTBOOK item type and location.  Copy the catkeys for these print titles onto Sheet2.  When finished, turn off the filter on the Callnum column.  There should be no Kindle titles left on the Textbook Search list sheet.  Copy Sheet1 and Sheet2 to forward to Lynn as Kindle titles with either no counterpart or print counterpart only.  She'll review for potential ebook ordering.  Delete Sheet1 and Sheet2.  Save the main sheet as Textbook Search List (Date) to replace the initial sheet made before CXs and Kindles were deleted.  The list is now ready to be uploaded to the search tool.  As of the Spring 2021 order cycle, also send a copy to Alex R. in AS to help with CDLA process.

As of May 2018, DPP will upload the sheet to the search application (requires Admin login).  The file upload may take several minutes.  Notify unit head or specialist if any errors occur.  Once the search tool has been updated with the new list, select the Review Textbooks tab on the Admin side to spot check data for accuracy against the Search List sheet.  Also do some random searches in the tool itself.

Notify the Textbook Program group at when the search tool has been updated.

Repeat this process for each list that is sent by the bookstore (for any one fall or spring semester, the search tool list will have been updated four times).


AFTER the last search list has been sent, the entire process for any given bookstore shipment is complete. Any titles ordered after this time (last minute additions, etc.) would not be included in the search list unless manually added, but they are still discoverable via the catalog.

Miscellaneous followup once all textbooks arrive and are evaluated:

Ruth will evaluate all backordered titles, and create a new spreadsheet for tracking their status. She will notify Ashleen when the outstanding orders are to be canceled and then ordered from Amazon or elsewhere.

Ruth will make a shared Google spreadsheet list of duplicate titles/coursepacks returned to the bookstore.  Ruth will compare the amounts charged ON THE ACTUAL INVOICE with the most recent bookstore list sent back from Ashleen (LIST NEW OR LIST USED), to verify that there are no price typos which would create discrepancies in refund amounts. Be aware that price changes can and do occur between the span of two bookstore lists, so using the most recent list is important for reconciling the refunds expected with actual amounts paid.

Create a YRSemesterTextbooksReturns table in the Textbooks database, copy and paste her list here. Use one table for the entire term.

A few weeks after the textbooks process is completed, Monographs will request a report of any outstanding textbook orders from Amazon and the book store.  Run the Sirsi report, Item list-Delimited-TEXTBOOKS On Order, and the Textbook Order Cleanup query in the Fund Statistics TEST database.  Download the Sirsi report and export the Textbook Order Cleanup query results. Send both files to Ruth; copy Monographs head.

It's also important for Monographs to check the BookStore Queries section of their database so that cleanup issues in Sirsi can be identified.  Book Store and other NCSU PeopleSoft Transactions, All Textbook Orders, and Textbook Order Cleanup are queries that should be run to identify invoice data discrepancies.

Compact and Repair the Textbooks database.

The Summer List

Monographs will request a list for the Summer session at the end of April/early May.  For many years, no textbooks were purchased for the summer semester, but since 2017, new textbooks titles will be ordered, so the list is processed as usual, by starting with Creating the new Bookstore List.  However, the summer order is NOT sent to the bookstore..  Send the order list to Monographs who will purchase textbooks via outside suppliers to expedite receipts for the short summer session turnaround time.  DPP will upload the Summer List to the textbook search tool to refresh it for the upcoming semester. 


If a situation once again arises where no summer textbook order will be placed,  proceed with the following steps.  DO NOT update the AllTitles table if no textbooks are purchased for the summer sessions.   DPP will upload the Summer List to the textbook search tool to refresh it for the upcoming semester.

– download it and save as YrSummerBookStoreListDate.xlsx in a separate SummerYr subfolder in the Textbooks folder (no need to add tab date column)
– copy all columns up to the Requested? column
– paste as a new table in the Textbooks database
– name the table NewBookstoreList (this will replace the previous data in table)
– hit the Delete Pipe Delimiter Columns From New Bookstore List button
– run the NewDeptCodes query to identify any new codes created since the previous semester.  Forward to C&RS for decision on which library/branch will hold the textbook on reserve.
– make a COPY of the New Bookstore List table and save as YrSummerBookStoreList
– copy the ISBN10s and ISBN13s into a text file
– use the MarcEdit Z39.50 client to run the ISBN list against the NCSU catalog using Batch mode
– export the results as an mrc file called *(SEMYr)Textbooks (Date).mrc
– use Export Tab Delimited Records to export file with catkeys only (refer to Textbook Search List section above)
– send the list of catkeys to Dawn
– when Dawn sends back the spreadsheet, import as a new table into the Textbooks database, and follow rest of instructions for cleanup, as outlined above in the Textbook Search List section
– upload the list to the search app via Admin login

  • No labels