Recipe: Data Extraction from Talis

Extracting anonymised data from Talis Alto

Originators/Authors

University of Manchester

Andy Land and Steve Campbell

Purpose

To extract loan transactions from Talis Alto.  For each transaction (the borrowing of a physical item but not its renewal or return) data showing the item id, basic bibliographic details, borrow id and transaction date are retrieved.

 

Background

 

The loan transactions were needed to provide data for use in the SALT book recommender service.

Ingredients

  • Talis Alto Library Management System
  • Perl

Assumptions

 

Select access to the Sybase database holding the loan data.

 

Warnings

 

Has the potential to impact on performance of the Talis system. The routine is best run on an alternative instance of the database if available and/or scheduled at a time when performance is less crucial.

Method

 

  • Run perl script
  • Set start date and end date (eg an academic year), or use a range of loan_id numbers.

 

Individual steps

 

  • Run the perl script shown in Appendix B
  • Put the start and finish LOAN_ID numbers into the relevant sql line and into the output file name.

Output data

 

Information will be placed in a csv file called salt_data_xxxxxxx-yyyyyyy.out

 

 

Appendix A: Sample output

‘Feb 29 2000′,’4000000′,’155567′,’39106′,’0416181902′,’Carsten, F. L., Francis Ludwig’,’. – The rise of fascism’,’1970′,’ ‘

 

Appendix B: Scripts

 

 

salt_data_4000000-4250000.pl

 

#! /usr/local/bin/perl

$BLCMP_HOME=$ENV{“BLCMP_HOME”};

$TALIS_HOME=$ENV{“TALIS_HOME”};

$MIS_HOME=”$TALIS_HOME/mis”;

$LOCAL_MIS_HOME=$ENV{“LOCAL_MIS_HOME”};

 

require “sybperl.pl”;

require “$TALIS_HOME/perl_tools/std_utils.pl”;

require “$TALIS_HOME/perl_tools/mis_utils.pl”;

 

$Database = “prod_talis”;

&Std_open_db();

open (LOG, “> salt_data_4000000-4250000.out”);

 

($result) = &sql($d,”

select getdate()

“);

 

(@result) = &sql($d,”

SELECT substring(L.CREATE_DATE,1,11), L.LOAN_ID, L.BORROWER_ID, W.WORK_ID,

W.CONTROL_NUMBER, W.AUTHOR_DISPLAY, W.TITLE_DISPLAY, W.PUB_DATE, W.EDITION_MAIN

from WORKS W, LOAN L, ITEM I, BORROWER B

WHERE L.LOAN_ID between 4000000 and 4250000 and L.STATE=0 and L.ITEM_ID=I.ITEM_ID

AND I.WORK_ID=W.WORK_ID

and L.BORROWER_ID=B.BORROWER_ID AND B.TYPE_ID not in (7,17)

“);

 

foreach $result (@result)

{

($t1,$t2,$t3,$t4,$t5,$t6,$t7,$t8,$t9)=split(‘~’,$result);

print LOG “‘$t1′,’$t2′,’$t3′,’$t4′,’$t5′,’$t6′,’$t7′,’$t8’,’$t9’n”;

}

 

 

One thought on “Recipe: Data Extraction from Talis

  1. Pingback: Final blog post | SALT – Surfacing the Academic Long Tail

Leave a Reply

Your email address will not be published. Required fields are marked *