Export ebooks from ALEPH to SFX
Tags: advanced, sfx, export, e-books, aleph
Last Updated: Nov 20, 2009 13:44
State
Stable
Programming language
PHP
Software requirements
PHP5 with OCI8 compiled
Screen captures
Figure 1: A webpage that groups and presents the different vendor packages in the catalog.
Figure 2: The administrator can review the ISBN:s and see how many are lacking.
Figure 3: The administrator can export the ISBN:s to a file prepared for SFX import via DataLoader
Figure 4: The prepared file
Author(s) homepage
http://www.bibl.hj.se
Download
No code available to download, se Installation instructions for code samples.
Working example
This is a user restriced resource, so I can not give you access to a working example... but it works ...
Using the following Ex Libris open interfaces
The beauty of having access to the Oracle database
Installation instructions
As I wrote earlier this is a workflow that is specific to our environment. You might find it hard to implement it exactly the way we hav done it.
1. The first thing you need to do is to add a field to the MARC record when you import your records from the vendor. We add a vendor based code in the 698 field. The codes are vendor specific and this is the only thing we use the field for.
2. In order to query for the 698 field you need to add it as one of your z13_user_defined fields. We have it in the 01 database as field 4.
3. Many e-book records lack ISBN in 020a so you might want to edit the tab that controls how z13_ISBN_ISSN is defined. We added 020z so that z13_ISBN_ISSN lists 020z if there is no 020a.
4. The script below is in PHP and there are comments inline that explain what is going on. I have left out the parts that limit access to script. If you use it you should put it behind IP-restriction or U/P. Also(!) you should include user and password that connects to the database from another place and not leave it in code.
<Code sample>
<?php
//Set up connection to the database $db in aleph 18 is aleph18
$conn = oci_connect($uid01, $pw01, $db);
//Connect to database
if (!$conn) {
$e = oci_error();
print htmlentities($e['message']);
exit;
}else{
//echo "<br>connected to db<br />";
}
//Define SQL query, count and group by field 698
$sql = "select count(*) as COUNTED, z13_user_defined_4 as VENDOR from z13 group by z13_user_defined_4";
//Prepare statement and run statement
$stmt = oci_parse($conn, $sql);
oci_execute($stmt);
//print header
echo "<b>Records in CAT grouped by 698 (z13_user_defined_4)</b><br><br><ul>";
//Loop through and print results
while (oci_fetch($stmt)) {
$TI = oci_result($stmt, "VENDOR");
//If there is no vendor this is a normal record, call it something
if($TI ==''){
$TI = 'JULIA';
}
$SYS = oci_result($stmt, "COUNTED");
echo "<li>$TI [$SYS] | <a href=$PHPSELF?code=$TI>Show ISBN </a> | <a href=create_sfx_port.php?coder=$TI>
Create SFX DataLoader file</a></li>";
}
echo "</ul>";
//Free SQL statement
oci_free_statement($stmt);
//Close connection
oci_close($conn);
//Receive and define variable if sent to page
$coder = $_GET["code"];
//If variable is sent connect to database and fetch ISBN:s for that vendor
if($coder){
$conn2 = oci_connect($uid01, $pw01, $db);
if (!$conn2) {
$e = oci_error();
print htmlentities($e['message']);
exit;
}else{
//echo "<br>connected to db<br />";
}
//Define SQL
$sql2 = "select z13_ISBN_ISSN as ISBN from z13 where z13_user_defined_4 like '".$coder."'";
$stmt2 = oci_parse($conn2, $sql2);
oci_execute($stmt2);
$x2='0';
$x3='0';
echo "<br><hr><br><h1>$coder</h1>";
while (oci_fetch($stmt2)) {
$ISBN = oci_result($stmt2, "ISBN");
//Remove all non numbers in ISBN but leave Xx alone
$q = ereg_replace("[^0-9Xx]","",$ISBN);
//Remove any whitespace ... shouldn't be any
$qt = trim($q);
//Get rid of empty ISBN:s
if(empty($qt)){
//echo " <br>";
}else{
echo "$qt";
echo " <br>";
$x3++;
}
$x2++;
}
echo "<br />$x3 (out of $x2) records | <a href=create_sfx_port.php?coder=$coder>Create SFX DataLoader file</a>";
//Free SQL statement
oci_free_statement($stmt2);
oci_close($conn2);
}
?>
</Code sample>
If a user clicks on Create SFX import file link they are sent to a page that creates the Loader file for them. In the code above it is called create_sfx_port.php. This is what it looks like.
<Code sample>
<?php
//Define timestamp
$today = date("YmdHi");
//Define incoming vendor code
$coder = $_GET["coder"];
//Tell the browser it is supposed to generate a txt file
header("Content-type: application/octet-stream");
header('Content-Disposition: attachment; filename="'.$coder.'_'.$today.'_sfx.txt"');
//If a code is sent to the page, connect to the database
if($coder){
$conn2 = oci_connect($uid01, $pw01, $db);
if (!$conn2) {
$e = oci_error();
print htmlentities($e['message']);
exit;
}else{
//echo "<br>connected to db<br />";
}
//Define the SQL statement
$sql2 = "select z13_ISBN_ISSN as ISBN from z13 where z13_user_defined_4 like '".$coder."'";
// Connect and run statement
$stmt2 = oci_parse($conn2, $sql2);
oci_execute($stmt2);
//define content variable
$data ="";
//loop through and write isbn and active (tab del.) to data variable
while (oci_fetch($stmt2)) {
$ISBN = oci_result($stmt2, "ISBN");
$q = ereg_replace("[^0-9Xx]","",$ISBN);
$qt = trim($q);
if(empty($qt)){
//do nada
}else{
$data .= "$qt";
$data .= "\t";
$data .= "ACTIVE";
$data .= "\n";
}
}
//print content of data variable
echo $data;
//Free SQL statement
oci_free_statement($stmt2);
oci_close($conn2);
}
?>
</Code sample>
TO DO list
Nothing so far
Known issues
Nothing so far
Comments
Text...
Advanced