/
DDA Pool Updates

DDA Pool Updates

Jun 13, 2023

DDA Pool Updates

Demand-driven acquisition (DDA) is a way UTL provides patrons with access to a large selection of ebooks but only purchases ebooks that patrons use. In other words, we load or activate a catalog of several thousand ebooks that UTL does not own. If patrons make substantial use of any of the ebooks in Primo makes available, they automatically trigger a purchase. Because books are purchased regularly and because the vendors add and remove books from our pool of titles regularly, it is important to update our holdings in Alma on a weekly basis. Currently, UTL has a large DDA program through Ebook Central and much smaller ones through EBSCO and JSTOR. While these programs are all run through GOBI/YBP we pull data directly from the individual vendors directly.

Ebook Central DDA

Open and prepare the Working File.

  1. You can find the EBC_dda23.xlsx file in Box . Click the ellipsis icon alongside the filename and select Open with…>Microsoft Excel, or you can choose Excel Online. Generally we use the desktop app and lock the file while working on it.  
  2. See the Box Edit [ |https://utexas.atlassian.net/wiki/download/attachments/36175935/BoxEdit.mp4?version=1&modificationDate=1535030038000&api=v2]video linked here shows how to open the file in Box Edit.
  3. Once you've opened the file you'll need to insert a new sheet after the Pool sheet and change the name of the new sheet to be the date (i.e., 20230605).

Acquire the current list of DDA titles from Ebook Central.

  1. Log into Proquest LibCentral, and click on the DDA Titles list on the left hand side
  2. The export the results into a CSV file and have it emailed to you.



Update the Working File.

  1. Copy and paste the entire list from the CSV file into the new sheet (i.e., 20230605) you created in the Box file for the program.
  2. Cut the Print ISBN and eISBN columns and insert the cut cells so that they become columns A and B, respectively. In this case, you are moving the two columns. Excel should offer an "Insert cut cells" option if you right-click on column A, then use the same method to move the eISBN into the Column A position. 
  3. Now Copy and paste (values only) over the data in those two columns. Sometimes copying and pasting from other workbooks adds extraneous information to numbers in Excel. This step will strip out that extra information and leave only the digits as text.
  4. Highlight just the columns and rows with ISBNs. At the top of the highlighted selection, click the error indicator and select "Convert to Number" from the drop-down list via the information icon ().
  5. Format the cells to have a Number format (no decimals).
  6. Move the Title column so that it's Column C.

Check for Removes

  1. On the Pool tab add a new Column A. This step will allow you to identify titles that have been removed by the vendor.
  2. Starting with your first row of data (Row 2 for this example) enter the following formula in cell A2:=VLOOKUP(b2,'20230605'!A:A,1,FALSE)(the bolded text will reflect the name of the new sheet you created)
  3. Copy that formula into the new column A for every row with data.
  4. Sort the entire spreadsheet by Column A so that the non-matched (#N/A) titles should go to the bottom
  5. Cut and paste the non-matched titles by row (so it includes Column A) at the bottom of the Removed tab and change the Column A for each one to reflect that days date.
  6. Highlight those rows yellow on the Removed tab
  7. Verify the rows with non-matched titles were deleted from the Pool sheet and delete Column A

Check for Adds

  1. On the dated sheet add a new Column A for matching
  2. Starting with your first row of data (Row 2 for this example) enter the following formula in cell A2=VLOOKUP(B2,Pool!A:A,1,FALSE)
  3. Now copy that formula into every row with data
  4. Next Sort the entire spreadsheet by Column A so that the non-matched (#N/A) titles should go to the bottom
  5. Now delete all the rows that do match leaving only the non-matched titles (#N/A)
  6. Now look at the remaining rows and you can highlight the whole sheet and do Data>Sort then click on the "My data has headers" box and set the Column to sort on Publisher as shown:

  1.  If you see any titles with Taylor & Francis, Routledge, Springer, Palgrave, Cambridge,  CRC Press, and Elsevier  then cut those rows and add them to the tab called CHECK to be reviewed for possible duplication.
  2. Now add a new Column in front of the Document ID (Column D) column and label it PP (for parser parameter) and put this formula into D2: =CONCAT("bkey=",E2) and copy and paste that down for all your titles. 
  3. Then select that entire Column and copy paste special "values only"

Add titles to Alma electronic collection.

  1. Download the Ebook Central load template from Box folder (Load Templates): Portfolio_Loader_Template_PQEBC.xlsx
  2. Copy and paste special (as text) the following fields (or enter text in quotations—not the quotation marks themselves—as shown):


Template Column

Value from Working file

A - ISBN

eISBN

B - ISBN

Print ISBN

C - TITLE

Title

D - AVAILABILITY

"ACTIVE"

E - PARSER_PARAMETERS

PP


  1. Save As "PQEBC_dda_adds_YYYYMMDD.xlsx" where YYYYMMDD equals today's date.
  2. In Alma, search for the electronic collection named "Ebook Central DDA" or Electronic Collection ID 61477430000006011.
  3. Click the more/ellipsis and Edit Service.
  4. Click the Portfolios tab.
  5. Click Load Portfolios.
  6. On the resulting wizard screen, complete the following in this order:
    1. Loading Policy Type = incremental
    2. File = the Excel files you just saved.
    3. Record Format = MARC21 Bibliographic
    4. Choose Operation = Add New
    5. Select Validation Policy = Validate online
  7. Select Next.
  8. The system will process for a short time, depending on how many rows are in the spreadsheet.
  9. On the resulting screen, you'll see information about what actions will be taken. Verify these numbers match what you expect.
  10. Download the Excel file to view information and warnings, we'll come back to these. If the number of errors isn't too large then continue to the next step.
  11. Click Load.
  12. The system will return you to the Portfolios tab while a job runs in Alma's background.
  13. When the job completes, you'll receive an email with the status. Verify the number of records processed matches what you expect and that there are no records with exceptions.
  14. Copy the rows from the new weekly sheet in the EBC DDA workbook to the bottom of the Pool sheet.

Review the Review file

  1. You need to open the review you got from step 10 above and sort by the first 2 columns.
  2. Scroll down looking for anything other than "Localize and activate" in Column A (see first image below)
  3. Now take each title that has "No action" and search them in Alma in the IZ first and then if doesn't come up there switch to the CZ
  4. If you find it in the matching platform (Ebook Central in this case) in the CZ you can activate it then switch back to the IZ to move the portfolio to the correct Electronic Collection (see second and third images below)
  5. Anything you can't find in the CZ should be removed from the dated sheet and not moved to the Pool sheet.


Check the Removes for the week

After you finish doing the Adds for the week you need to go back to your Removed tab or sheet and check all the removes to see if they need to be deleted or merged because they were purchased.

  • Search by ISBN and/or Title on the admin site for Ebook Central (Proquest LibCentral) to see if it was purchased or not

Figure 1

  • If purchased, then search by ISBN and/or Title in Alma
    • If results are only 1 bib record with portfolio in the DDA collection (as below)

 

      • Go to portfolios and move the portfolio to the Ebook Central Purchased Ebooks Electronic Collection

 

    •  
      • Edit portfolio and check the following:
        • Electronic material type: Book 
        • Activation date - some of these got mangled so if the year seems off change it 

 

          • Switch to the Linking tab and make sure the parser parameter is bkey=xxxxx instead of just a number (some of these got mangled as well)
          • Switch to the Acquisition tab and update the Public Access Model to match what's listed on LibCentral then save
          • Then go back to your spreadsheet and highlight that title's row green

    • If you get 2 results when searching and one has no portfolio, but does have a POL attached do the following:

      • Move the POL to the bib with the portfolio (after confirming collection)

      • Delete the bib without the portfolio (you can use the MDE and split screen here to copy any fields missing from the CZ record over from the bib before you delete it)
      • Now move the portfolio on the remaining bib to the purchased collection (as mentioned above), check the same portfolio information on the General and Linking tabs
      • On the Acquisitions tab connect the POL in addition to selecting the Public Access Model

      • Save the record and remove the highlight on this title's row in the spreadsheet

    • If the title WAS NOT purchased you can just search it in Alma, go the portfolios and delete the portfolio and bib (if there are no other portfolios attached) associated with the DDA collection
    • Then remove the highlight and change the text to red for those.

EBSCO DDA

Open and prepare the Working File.

  1. You can find the EBSCO_dda23.xlsx file in Box . Click the ellipsis icon alongside the filename and select Open with…>Microsoft Excel, or you can choose Excel Online. Generally we use the desktop app and lock the file while working on it.  
  2. See the Box Edit [ |https://utexas.atlassian.net/wiki/download/attachments/36175935/BoxEdit.mp4?version=1&modificationDate=1535030038000&api=v2]video linked here shows how to open the file in Box Edit.
  3. Once you've opened the file you'll need to insert a new sheet after the Pool sheet and change the name of the new sheet to be the date (i.e., 20230605).

Acquire the current list of DDA titles from EBSCO.

  1. Log into the EBSCOhost Collection Manager, and click on the DDAs option and select Active DDAs
  2. Click on the list name under Active DDAs
  3. Select Export List

Update the Working File.

  1. Copy and paste the entire list from the CSV file into the new sheet (i.e., 20230605) you created in the EBSCO_dda23 file.
  2. Cut the eISBN and Print ISBN columns and insert the cut cells so that they become columns A and B, respectively. In this case, you are moving the two columns. Excel should offer an "Insert cut cells" option if you right-click on column A, then use the same method to move the eISBN into the Column A position.
  3. Highlight just the columns and rows with ISBNs. At the top of the highlighted selection, click the error indicator and select "Convert to Number" from the drop-down list via the information icon ( ).
  4. Format the cells to have a Number format (no decimals).
  5. Select and cut the Title column, then paste it to become column C.
  6. Insert a blank column to the right of the Book ID column so that the new column becomes column D and label it PP (parser parameter)
  7. Create the PP by doing the following formula in D2 and copy it down to all the titles: =concat("ID=",E2)

Check for Removes

  1. On the Pool tab add a new Column A. This step will allow you to identify titles that have been removed by the vendor.
  2. Starting with your first row of data (Row 2 for this example) enter the following formula in cell A2:=VLOOKUP(b2,'20230605'!A:A,1,FALSE)(the bolded text will reflect the name of the new sheet you created)
  3. Copy that formula into the new column A for every row with data.
  4. Sort the entire spreadsheet by Column A so that the non-matched (#N/A) titles should go to the bottom
  5. Cut and paste the row for the non-matched titles at the bottom of the Removed tab and change Column A to reflect the date (ie, 6/5/2023)
  6. Highlight those added rows yellow.
  7. Verify the rows with non-matched titles were deleted from the Pool sheet and delete Column A.

Check for Adds

  1. On the dated sheet add a new Column A for matching
  2. Starting with your first row of data (Row 2 for this example) enter the following formula in cell A2=VLOOKUP(B2,Pool!A:A,1,FALSE)
  3. Now copy that formula into every row with data
  4. Next Sort the entire spreadsheet by Column A so that the non-matched (#N/A) titles should go to the bottom
  5. Now delete all the rows that do match leaving only the non-matched titles (#N/A)
  6. Now look at the remaining rows and you can highlight the whole sheet and do Data>Sort then click on the "My data has headers" box and set the Column to sort on Publisher or just look at the Publisher Column if there aren't many titles
  7. If you see any titles with Taylor & Francis, Routledge, Springer, Palgrave, Cambridge, Elsevier or CRC Press cut those rows and add them to the tab called CHECK so they can be checked for dupes.

Add titles to Alma electronic collection.

  1. Download the EBSCO load template from Box folder (Load Templates): Portfolio_Loader_Template_EBSCO.xlsx.
  2. Copy and paste special (as text) the following fields (or enter text in quotations—not the quotation marks themselves—as shown):


Template Column

Value from Working File

A - ISBN

eISBN

B - ISBN

Print ISBN

C - TITLE

Title

D - AVAILABILITY

"ACTIVE"

E - PARSER_PARAMETERS

Value from column D (use Paste Special>>Values, not traditional paste)


  1. Save As "EBSCOdda_adds_YYYYMMDD.xlsx" where YYYYMMDD equals today's date.
  2. In Alma, search for the electronic collection named "EBSCO Ebooks (DDA)" or Electronic Collection ID 61477629670006011.
  3. Click the more/ellipsis and Edit Service.
  4. Click the Portfolios tab.
  5. Click Load Portfolios.
  6. On the resulting wizard screen, complete the following:
    1. Loading Policy Type = Incremental
    2. File = the Excel files you just saved.
    3. Record Format = MARC21 Bibliographic
    4. Choose Operation = Add New
    5. Select Validation Policy = Validate online
  7. Select Next.
  8. The system will process for a short time, depending on how many rows are in the spreadsheet.
  9. On the resulting screen, you'll see information about what actions will be taken. Verify these numbers match what you expect.
  10. Download the Excel file to view information and warnings, we'll come back to these. If the number of errors isn't too large then continue to the next step.
  11. Click Load.
  12. The system will return you to the Portfolios tab while a job runs in Alma's background.
  13. When the job completes, you'll receive an email with the status. Verify the number of records processed matches what you expect and that there are no records with exceptions.
  14. Copy the rows from the new weekly sheet in the EBSCO_dda23 workbook to the bottom of the Pool sheet.

Review the Review file

See the Review File instructions.

Check the removed titles for the week

You will do this as you did with Ebook Central, but you will be looking the titles up in GOBI to check for purchase and find the Access Model. If an EBSCO title is marked as "DDA" still in GOBI, check access and if access is working leave it highlighted yellow and check it again the following week. If access doesn't work it can be removed.

JSTOR DDA

Open and prepare the Working File

  1. You can find the JSTOR_DDA_23.xlsx file in Box . Click the ellipsis icon alongside the filename and select Open with…>Microsoft Excel, or you can choose Excel Online. Generally we use the desktop app and lock the file while working on it.  
  2. Once you've opened the file you'll need to insert a new sheet after the Pool sheet and change the name of the new sheet to be the date (i.e., 20230605)

Acquire the current list of DDA titles from JSTOR

  1. Log into the JSTOR admin site
  2. Make sure you are in the Holdings section and the Books tab as seen below.
  3. Scroll down to the Demand-Driven Acquisition (DDA) section and click on the "Download XLSX" option to get the file.

Update the Working file

  1. Copy and paste the entire list from the XLSX file into the new sheet (i.e., 20230605) you created in the JSTOR_dda23 file.
  2. Arrange the Columns to mirror the Pool sheet (eISBN, Print ISBN, Title, Copyright, Stable URL - in that order)
  3. Add a new Column A so you can reformat the ISBNs
  4. In cell A2 put the formula =SUBSTITUTE(B2,"-","") and then copy that all down the A column for every row with data
  5. Then Copy and paste as values and over the Column B values so you get the ISBN without dashes.
  6. Change these to be Numbers and then Format the cells to have a Number format (no decimals).
  7. Repeat steps 4-6 for Column C (so formula would change to =SUBSTITUTE(C2,"-","")
  8. Select and cut the Title column, then paste it to become column C.
  9. Delete Column A.

Check for removes

  1. On the Pool tab add a new Column A. This step will allow you to identify titles that have been removed by the vendor.
  2. Starting with your first row of data (Row 2 for this example) enter the following formula in cell A2:=VLOOKUP(b2,'20230605'!A:A,1,FALSE)(the bolded text will reflect the name of the new sheet you created)
  3. Copy that formula into the new column A for every row with data.
  4. Sort the entire spreadsheet by Column A so that the non-matched (#N/A) titles should go to the bottom
  5. Cut and paste the row for the non-matched titles at the bottom of the Removed tab and change Column A to reflect the date (ie, 6/5/2023)
  6. Highlight those added rows yellow.
  7. Verify the rows with non-matched titles were deleted from the Pool sheet and delete Column A.

Check for adds

  1. On the dated sheet add a new Column A for matching
  2. Starting with your first row of data (Row 2 for this example) enter the following formula in cell A2=VLOOKUP(B2,Pool!A:A,1,FALSE)
  3. Now copy that formula into every row with data
  4. Next Sort the entire spreadsheet by Column A so that the non-matched (#N/A) titles should go to the bottom
  5. Now delete all the rows that do match leaving only the non-matched titles (#N/A)

Add titles to Alma Electronic Collection

  1. Download the JSTOR load template from Box folder (Load Templates): Portfolio_Loader_Template_JSTOR.xlsx.
  2. Copy and paste special (as text) the following fields (or enter text in quotations—not the quotation marks themselves—as shown):


Template Column

Value from Working File

A - ISBN

eISBN

B - ISBN

Print ISBN

C - TITLE

Title

D - AVAILABILITY

"ACTIVE"

E - URL

Stable URL


  1. Save As "JSTORdda_adds_YYYYMMDD.xlsx" where YYYYMMDD equals today's date.
  2. In Alma, search for the electronic collection named "JSTOR Demand Driven Ebooks (JSTOR eBooks: DDA Frontlist)" or Electronic Collection ID 61590512030006011.
  3. Click the more/ellipsis and Edit Service.
  4. Click the Portfolios tab.
  5. Click Load Portfolios.
  6. On the resulting wizard screen, complete the following:
    1. Loading Policy Type = Incremental
    2. File = the Excel files you just saved.
    3. Record Format = MARC21 Bibliographic
    4. Choose Operation = Add New
    5. Select Validation Policy = Validate online
  7. Select Next.
  8. The system will process for a short time, depending on how many rows are in the spreadsheet.
  9. On the resulting screen, you'll see information about what actions will be taken. Verify these numbers match what you expect.
  10. Download the Excel file to view information and warnings, we'll come back to these. If the number of errors isn't too large then continue to the next step.
  11. Click Load.
  12. The system will return you to the Portfolios tab while a job runs in Alma's background.
  13. When the job completes, you'll receive an email with the status. Verify the number of records processed matches what you expect and that there are no records with exceptions.
  14. Copy the rows from the new weekly sheet in the JSTOR_DDA_23 workbook to the bottom of the Pool sheet.

Confluence Documentation | Web Privacy Policy | Web Accessibility