/
EBS Batch (draft)

EBS Batch (draft)

Oct 24, 2018

Evidence-based acquisitions (??) require periodic updates. Cambridge, for example, is done monthly.

Title Activations

File Set Up

You will receive a New EBA Title List file as needed..

  1. Open the file locally, probably in your Downloads folder

  2. Delete the top rows (the rows leading to the column headings).

  3. Rename the sheet to today’s date.

  4. Copy the HB ISBN column and insert so that it is column A.

  5. Sort by that column. For any rows with no data in HB ISBN, copy and paste the data from PB ISBN.

**When you are done the file should look something like the  example**

Download Title List (Intota)

Next you need to download a list of titles from the relevant database in Intota.

  1. If you are logged in you can use the link above

  2. If not you will probably need to login and then try it or search for the relevant under Databases, should be the first result

  3. On the Database page click on the "View All" link next to the Titles Section and then click the Download button on the right and hit Confirm (see the example to the right)

  4. Then just open that in Excel (you need to be in Excel to open it) and cut and paste the whole thing into the Act tab of your spreadsheet 

  5. remove the first 14 rows (they all start with **)

  6. Set the Format of the ISSN/ISBN column to Number (no decimals) and then copy that and paste it in front of the Title field so it's like the example under to the right

Match Expend to Act 

Now you will match the titles in the Expend tab to the titles in the Act tab by doing the following:

  1. Add a new Column A to the Expend tab

  2. Starting with your first row of data (Row 2 for this example) enter the following formula in cell A2

    1. =VLOOKUP(B2,Act!$A$2:$A$39016,1,FALSE)

      1. the bolded number may change depending on how many rows are in your Act tab

      2. You are matching the data in cell B2 against the entire Column A (minus header) of the Act tab and returning the data in Column A as long as the match is exact

  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, then highlight them

    1. See the example under to the right

  5. Now you can can delete Column A

Match Act to Expend

Next you will match the titles in the Act tab to the titles in your Expend tab so you can change the status if needed and pull in the user model data. It's almost the exact same process as the Expend to Act matching above.

  1. Add a new Column A

  2. Starting with your first row of data (Row 2 for this example) enter the following formula in cell A2

    1. =VLOOKUP(B2,Expend!$A$2:$F$27,1,FALSE)

      1. You are matching the data in cell B2 against the entire Column A (minus header) of the Expend tab and returning the data in Column A as long as the match is exact

      2. You have selected more columns (A-F in this example) so that you can pull data from those once your matches are found

      3. the bolded number may change depending on how many rows are in your Expend tab

  3. Then 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

    1. Check the number of matched rows against the number of rows in your Expend tab (minus the highlighted ones)

  5. Now delete the non matched rows

    1. In the Status column (Column F), change all rows to "Purchased"

  6. Now update your formula to return the Access Model column (should 6th column)

  7. Now copy the data and paste values only into the Public Note (should be Column N)

  8. Then highlight that column and do Find and Replace as follows:

    1. Unlimited (DRM Free) = UU

    2. Non-Linear = NLL

    3. 1-user = SU

    4. 3-user = 3U

  9. Now delete Column A (see example of what it should look like now under  to the right

Loading Activation File

Now you are going to load your activation file back into Intota

  1. First make sure you have the title list download open 

    1. file name will be something like: AABBV_Ebook-Central-Perpetual-and-DDA_all (9)

  2. Now go to your Act tab and copy the entire worksheet

  3. Paste into the title download file (you may need to delete all existing data before pasting)

  4. Remove Column A

  5. Save As: AABBV_Ebook-Central-Perpetual-and-DDA_all

    1. make sure you save it as a tab-delimited (txt) file

  6. Now go back to Intota to the database page and click the Upload Title List button on the main page or the Upload button in the expanded Title Details section and attach your file

Check Non Matched Titles

Now go back to your Expend tab and look up the highlighted titles in Intota and make sure they are activated under the Ebook Central database with the status of Purchased.

Check Title Activation Load

Wait a few minutes and then check the Intota home page under Upload Status and see if your file loaded successfully. See the  for example of what it should look like it.

Cost Loading

Once you have verified the title upload you are ready to start the cost loading portion.

Cost Matching

You can follow the instructions on the Cost Loading in Intota page until you get to the part about pulling data over. 

You should use these instructions for pulling your data over since they are specific to the EBC_Expend files.

  1. Change the column returned to Total Cost (4th column) and change the format to Number with two decimal places

    1. Copy and paste values only into the following columns

      1. S 

      2. U

      3. AT

  2. Change the column returned to Purchase Type (5th column)

    1. Now sort by column A

    2. Copy and paste values only In Column Q (Payment Type) 

    3. Find and Replace in Column Q as follows:

      1. Autopurchase = Demand Driven Purchase

      2. Ebook (firm order) = One Time Purchase

      3. Upgrade stays the same

  3. Change the column returned to Access Model (6th column)

    1. Copy and paste values only into Column AR (Note)

    2. Find and Replace as follows for that column

      1. 1-user = SU

      2. 3-user = 3U

      3. Unlimited (DRM Free) = UU

      4. Non-Linear = NLL

  4. Now fill in the following columns

    1. Fiscal Year (N) = current year

    2. Transaction Currency Code (T) = USD

    3. Include in Total for Year (V) = yes

  5. Delete Column A 

  6. Should look like  example

Invoice Matching

Once you do the basic matching using the above instructions you will need to take your invoices for the week and fill in some additional information

Sort by the Title Name (H) field to make it a little easier

  1. Go through each invoice and do the following for each title

    1. add fund code (AR)

    2. add order number if it's on the invoice (X)

    3. note any titles not listed 

  2. Set aside any invoices that have no matches to do manually

  3. Sort by Fund Name (AR). Verify any rows with no fund code against any titles not matched from invoices and then delete the row if no match is found

  4. Save it!

Saving and Uploading File

  1. Now copy the whole Cost worksheet and paste it in your CostTemplateWithDBAndTitles spreadsheet

  2. Delete Column A and Save As a CSV file using the following naming convention: EBC_costYYYYMMDD-YYYMMDD

  3. Go back to the Cost Data Upload section of Intota and hit Upload File button

  4. Select your file

  5. Make sure you check the Use itemized totals as effective costs box!

Then check back later to make sure the file loaded.