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”;

}

 

 

2 Responses to Recipe: Data Extraction from Talis

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

  2. Pingback: Final blog post | Innovations

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s