Delivered Files List and Associated Scripts for Symphony Data Export
Prepared by Memorial University of Newfoundland for the May, 2015 Symphony Export to Alma
The MUN Symphony was originally installed in 1995 and is based on the ISAM database (not Oracle) which explains why some of our scripts differ from the ones created by Oklahoma State. We have > 2.5 million ANSEL encoded bib records. Also, keep in mind that many of the Symphony record types (marc bibs, users, etc.) are based on formats. Formats change over time and may be locally modified by an institution to suit its needs. Example, we created a Postal Code element to replace the Zip Code element in the user address format. The fact that formats are not fixed and may be modified also explains why export scripts differ between Symphony sites. Additional information regarding the following scripts and procedures may be obtained from Wayne Mitchelmore (email: waynem@mun.ca) at Memorial University. Use of the following scripts should be made by individuals trained in the use of Symphony APIs. There is no implied intent of efficiency built into the code below. Use at your own risk.
Below are the list of Symphony export files delivered to Ex Libris:
File type
|
Delivering Y/N
|
File name(s)
|
Number of records delivered (per file)
|
Encoding (Choose, only if different than one listed)
|
Details/Notes
|
Bibliographic
|
Yes
|
mun_bib_01_20150525.mrc
mun_bib_02_20150525.mrc
mun_bib_03_20150525.mrc
mun_bib_04_20150525.mrc
mun_bib_05_20150525.mrc
mun_bib_06_20150525.mrc
mun_bib_07_20150525.mrc
mun_bib_08_20150525.mrc
mun_bib_09_20150525.mrc
mun_bib_10_20150525.mrc
mun_bib_11_20150525.mrc
mun_bib_12_20150525.mrc
mun_bib_13_20150525.mrc
|
199822
199959
199808
199947
199930
199981
199938
199888
199880
199515
199815
199941
51831
|
Ansel
|
catkey in 001
|
Suppressed bibs
|
Yes
|
suppressedrecmemmay25.txt
|
11806
|
Latin-1
|
|
MARC Holdings
|
Yes
|
marcholdingshelathmemMay25.mrc
|
2192
|
Latin-1
|
Catkey in 004 tag, (encoding is Ansel)
|
Holdings embedded in Bib
|
Yes
|
|
|
Latin-1
|
Holdings are embedded in the bibs
|
Item secondary file (enum/chron)
|
No
|
|
|
Latin-1
|
|
Boundwiths
|
Yes
|
boundwidthmemMay25.txt
|
7608
|
Latin-1
|
|
Serials
|
Yes
|
mun_serctl_May25.flat
|
17460
|
Latin-1
|
catkey in SERC_TITLE_KEY
|
Issues
|
Yes
|
mun_prediction_issue.May25.flat
|
551135
|
Latin-1
|
|
Patrons
|
Yes
|
all_mun_patrons_May25_internal_external
|
4999
|
Latin-1
|
|
Loans
|
Yes
|
chargelist.flat.May25
|
22199
|
Latin-1
|
|
Fines and Fees
|
Yes
|
openbills.mun.May25.txt
|
4611
|
Latin-1
|
|
Requests
|
Yes
|
holddump.mun.May25.flat
|
80
|
Latin-1
|
|
Vendors
|
No
|
|
|
Latin-1
|
|
Funds
|
Yes
|
mun_funds_20150528.csv
|
3433
|
Latin-1
|
|
P2E
|
Yes
|
p2ememMay28_sort.txt
|
978050
|
Latin-1
|
|
Orders
|
Yes
|
order.records.3yrs.mun.May28.txt
|
79173
|
Latin-1
|
catkey in catalog_num
|
Invoices
|
Yes
|
mun_invoices.may28.xml
|
13208
|
Latin-1
|
|
Courses
|
No
|
|
|
Latin-1
|
|
Local authorities
|
Yes
|
localauthorities.mun.May25.mrc
|
1505
|
Latin-1
|
encoding in Ansel
|
.
Bibliographic Record Export
Before starting this process we identified records which were shadowed at either the call number or item level and changed the home locations accordingly to SHADOWCALLNUM or SHADOWITEM. Catkeys of records shadowed at the bib level were output to the suppressed bibs file.
You first need to review your MARC bib formats and determine if there are any special purpose formats in Symphony which have no relationship to MARC and should not be migrated. In our case, we have the EQUIP format used to catalogue pieces of equipment. You may also use the COMMUNITY format. Either delete all the records associated with non-bibliographic formats and/or exclude them from the selcatalog command below using “-f~EQIUP,COMMUNITY” or equiovalent.
# Steps to follow
# get all catkeys from symphony
1. selcatalog > allcatkeys
You will probably want to exclude certain record sets from the export, in our case, serials solutions records. Typically, these will be excluded using values in either itemcat1 or itemcat2.
s
# Exclude all SERSOL records, located in itemcat2
# must use "-e~SERSOL"
cat allcatkeys | selitem -iC "-g~SERSOL" -oC > all_catkeys_not_SERSOL.ckeys
# remove dupliocate catkeys
cat all_catkeys_not_SERSOL.ckeys | sort -u > all_catkeys_not_SERSOL.ckeys.sorted
The selitem command above will exclude bib records with no items (error 111). We need to find these bibs with no copies and add them to the master set of catkeys.
#Handle error 111
cat all_catkeys | selcallnum -iC "-c=0" -oC > catkeys_nocopies
sort -u catkeys_nocopies > catkeys_nocopies_sorted
cat all_catkeys_not_SERSOL.ckeys.sorted catkeys_nocopies_sorted > final_all_catkeys
Create The File of Catkeys for Suppressed Bibs
# This is for the suppressed bibs file of catkeys Switch -61 finds all records in Symphony shadowed at the bib record level.
cat all_catkeys_not_SERSOL.ckeys.sorted | selcatalog -61 > suppressedrecsmemMarch22.txt
# This is for bibs which are not shadowed. This is the final set of catkeys you will be working with and excludes bibs shadowed at the bib level.
cat all_catkeys_not_SERSOL.ckeys.sorted | selcatalog -60 > masterNotShadowed.master
#split file into 200k records per file (13 files created) Ex Libris bib record export should be limited to 200,000 records per file)
split -l200000 final_all_catkeys AllCatkey
# We wanted to preserve and migrate Symphony bib level cataloguing administrative data. The elements we migrated are obtained from the selcatalog command and are as follows:
q = date catalogued store in tag 916
p = date created stored in tag 917
s = created by login name stored in tag 918
r = date modified stored in tag 919
w = last modified by login name stored in tag 920
# now we must get extra data for the 13 files. Basically, process each of the set of 200,000 catkeys and output the data.
cat AllCatkeyaa | selcatalog -iC -oCqpsrw > All_Data_01_20150525.txt
Sample records from - All_Data_01_20150525.txt
100000|0|19950908|BATCH|20150412|HPRETTY3|
100001|0|19950908|BATCH|20150412|HPRETTY3|
# Now take each 200,000 catkey file and perform a catalogdump with the specific requirements below:
cat AllCatkeyaa | catalogdump -oF -h -kc009,c902,f903 -t999 > mun_bib_01_20150525.flat
-of output is in flat format because we will be editing these files
-h output item level holdings in tag 999 (-t999)
-kc009 outputs the catkey in the 009 tag
c902 outputs the catkey in the 902 tag
f903 output the flexkey in the 903 tag
Sample record from mun_bib_01_20150525.flat:
*** DOCUMENT BOUNDARY ***
FORM=MARC
.000. |aam a0n
.001. |a74096605
.008. |a741113s1958 enk b 00000 eng
.009. |a100000
.010. |a 58003927
.035. |a(Sirsi) 74096605
.035. |a(OCoLC)1074626
.043. |ae-uk---
.041. 1 |aeng|afre
.040. |aCaNfsM|beng|cCaNfsM
.050. 0 |aDA625|b.T63 1958
.100. 1 |aTocqueville, Alexis de,|d1805-1859.
.245. 10|aJourneys to England and Ireland.|cTranslated by George Lawrence and K. P. Mayer. Edited by J. P. Mayer.
.260. |aLondon,|bFaber and Faber|c[1958]
.300. |a243 pages ;|c23 cm
.336. |atext|btxt|2rdacontent
.337. |aunmediated|bn|2rdamedia
.338. |avolume|bnc|2rdacarrier
.500. |a"Based on volume v. 2 of ... [the author's] Oeuvres compláetes."
.504. |aBibliographical footnotes.
.651. 0|aGreat Britain|xDescription and travel.
.902. |a100000
.903. |a74096605
.947. |a100000
.950. |a100000
.912. |aMARCIVE 2015
.090. 1 |aDA/625/T63|bMAIN
.596. |a1
.035. |a(OCoLC)1074626
.999. |aDA 625 T63|wLC|c1|i31162004090610|d11/5/2015|e11/5/2015|kCHECKEDOUT|lBOOKSTACKS|mQEII|n58|p$50.00|rM|sY|tBOOK|u12/9/1995
# The above two sets of files just were processed by the library programmer The cataloguing administrative elements were merged into the bib and additional editing was performed on the records as described below.
We have two sets of data: bib records and additional data which needs to be merged into the bib records. Both sets of records are sorted in catkey order. The catkey appears in tag 009 in the bib record and is the first element in the pipe-delimited all data file.
All of our programs were written in the Delphi programming language and are of no use to someone not familiar with the language. Write a program in your preferred language which goes line by line through the bib record until it reaches the 009 tag. Take the catkey in the 009 and match it to the catkey element in the “all data” file. Write out the elements from all data to the appropriate tages in the bib record as illustrated below:
STEP ONE
Record from "All Data":
824175|0|19950908|BATCH|20150415|HPRETTY3|
Bib record:
Before
*** DOCUMENT BOUNDARY ***
FORM=MARC
.000. |aam a0c
.001. |a76213480
.008. |a881122s1987 nyua b 00000 eng u
.009. |a824175
.010. |a 88670063
.020. |a0884770222
.035. |a(Sirsi) 76213480
.035. |a(OCoLC)17153102s
.040. |aDLC|beng|cDLC
.050. 00|aHF1040.7|b.H38 1987
.082. 00|a306.4
.100. 1 |aHaug, Wolfgang Fritz.
.245. 10|aCommodity aesthetics, ideology & culture /|cWolfgang Fritz Haug.
.246. 3 |aCommodity aesthetics, ideology, and culture.
.260. |aNew York :|bInternational General,|cc1987.
.300. |a188 pages :|billustrations ;|c21 cm
.336. |atext|btxt|2rdacontent
.337. |aunmediated|bn|2rdamedia
.338. |avolume|bnc|2rdacarrier
.500. |aA collection of revised articles previously published in German and English.
.504. |aBibliography: pages [173]-188.
.650. 0|aCommercial products|xPsychological aspects.
.650. 0|aAesthetics.
.650. 0|aFashion and art.
.650. 0|aSensuality.
.650. 0|aPopular culture.
.902. |a824175
.903. |a76213480
.947. |a824175
.950. |a824175
.912. |aMARCIVE 2015
.090. 1 |aHF/1040/.7/H38/1987|bMAIN
.098. 2 |aACQ|bCOU|cUSD|d11.95|g941108|iMAIN|l3469|m950517|rLS|s951110
.596. |a1
.035. |a(OCoLC)17153102
.999. |aHF 1040.7 H38 1987|wLC|c1|i31162008384548|d22/5/2013|e16/4/2013|lBOOKSTACKS|mQEII|n2|rY|sY|tBOOK|u10/4/1996
After
*** DOCUMENT BOUNDARY ***
FORM=MARC
.000. |aam a0c
.001. |a76213480
.008. |a881122s1987 nyua b 00000 eng u
.009. |a824175
.010. |a 88670063
.020. |a0884770222
.035. |a(OCoLC)17153102
.035. |a(OCoLC)17153102
.035. |a(Sirsi) 76213480
.040. |aDLC|beng|cDLC
.050. 00|aHF1040.7|b.H38 1987
.082. 00|a306.4
.090. 1 |aHF/1040/.7/H38/1987|bMAIN
.098. 2 |aACQ|bCOU|cUSD|d11.95|g941108|iMAIN|l3469|m950517|rLS|s951110
.100. 1 |aHaug, Wolfgang Fritz.
.245. 10|aCommodity aesthetics, ideology & culture /|cWolfgang Fritz Haug.
.246. 3 |aCommodity aesthetics, ideology, and culture.
.260. |aNew York :|bInternational General,|cc1987.
.300. |a188 pages :|billustrations ;|c21 cm
.336. |atext|btxt|2rdacontent
.337. |aunmediated|bn|2rdamedia
.338. |avolume|bnc|2rdacarrier
.500. |aA collection of revised articles previously published in German and English.
.504. |aBibliography: pages [173]-188.
.650. 0|aAesthetics.
.650. 0|aCommercial products|xPsychological aspects.
.650. 0|aFashion and art.
.650. 0|aPopular culture.
.650. 0|aSensuality.
.902. |a824175
.903. |a76213480
.912. |aMARCIVE 2015
.916. |a0
.917. |a19950908
.918. |aBATCH
.919. |a20150418
.920. |aHPRETTY3
.999. |aHF 1040.7 H38 1987|wLC|c1|i31162008384548|d22/5/2013|e16/4/2013|lBOOKSTACKS|mQEII|n2|rY|sY|tBOOK|u10/4/1996
STEP TWO
In this step we are copying data and reordering the tags:
Firstly this code MUST be run on an already edited file created by the above process
The .916. to .920. were inserted by the above process.
Before
*** DOCUMENT BOUNDARY ***
FORM=MARC
.000. |aam a0c
.001. |a76213480
.008. |a881122s1987 nyua b 00000 eng u
.009. |a824175
.010. |a 88670063
.020. |a0884770222
.035. |a(OCoLC)17153102
.035. |a(OCoLC)17153102
.035. |a(Sirsi) 76213480
.040. |aDLC|beng|cDLC
.050. 00|aHF1040.7|b.H38 1987
.082. 00|a306.4
.090. 1 |aHF/1040/.7/H38/1987|bMAIN
.098. 2 |aACQ|bCOU|cUSD|d11.95|g941108|iMAIN|l3469|m950517|rLS|s951110
.100. 1 |aHaug, Wolfgang Fritz.
.245. 10|aCommodity aesthetics, ideology & culture /|cWolfgang Fritz Haug.
.246. 3 |aCommodity aesthetics, ideology, and culture.
.260. |aNew York :|bInternational General,|cc1987.
.300. |a188 pages :|billustrations ;|c21 cm
.336. |atext|btxt|2rdacontent
.337. |aunmediated|bn|2rdamedia
.338. |avolume|bnc|2rdacarrier
.500. |aA collection of revised articles previously published in German and English.
.504. |aBibliography: pages [173]-188.
.650. 0|aAesthetics.
.650. 0|aCommercial products|xPsychological aspects.
.650. 0|aFashion and art.
.650. 0|aPopular culture.
.650. 0|aSensuality.
.902. |a824175
.903. |a76213480
.912. |aMARCIVE 2015
.916. |a0
.917. |a19950908
.918. |aBATCH
.919. |a20150418
.920. |aHPRETTY3
.999. |aHF 1040.7 H38 1987|wLC|c1|i31162008384548|d22/5/2013|e16/4/2013|lBOOKSTACKS|mQEII|n2|rY|sY|tBOOK|u10/4/1996
--------------------------------
.001. Gets copied to .901.
.090. Gets copied to .913. or .914. depending on if the 10 letter in the line is equal to s or not
.098. Gets copied to .915.
.693. Gets copied to .963.
.009. Gets copied to .001. and then is removed
if these are found inside a .999. record then extract them and give them their own marc tag
|x information gets a record of its own .905.
|z information gets a record of its own .906.
|l information gets a record of its own .907.
|k information gets a record of its own .908.
|t information gets a record of its own .909.
|o information gets a record of its own .910.
---------------- Rearrange ----------------
Now that all the new record types have been added sort then in numeric order
000 to 999
After
*** DOCUMENT BOUNDARY ***
FORM=MARC
.000. |aam a0c
.001. |a824175 This came from .009. and then 009 was removed.
.008. |a881122s1987 nyua b 00000 eng u
.010. |a 88670063
.020. |a0884770222
.035. |a(Sirsi) 76213480
.035. |a(OCoLC)17153102
A duplicate .035. was removed
.040. |aDLC|beng|cDLC
.050. 00|aHF1040.7|b.H38 1987
.082. 00|a306.4
.090 and 098 are removed
.100. 1 |aHaug, Wolfgang Fritz.
.245. 10|aCommodity aesthetics, ideology & culture /|cWolfgang Fritz Haug.
.246. 3 |aCommodity aesthetics, ideology, and culture.
.260. |aNew York :|bInternational General,|cc1987.
.300. |a188 pages :|billustrations ;|c21 cm
.336. |atext|btxt|2rdacontent
.337. |aunmediated|bn|2rdamedia
.338. |avolume|bnc|2rdacarrier
.500. |aA collection of revised articles previously published in German and English.
.504. |aBibliography: pages [173]-188.
.650. 0|aCommercial products|xPsychological aspects.
.650. 0|aAesthetics.
.650. 0|aFashion and art.
.650. 0|aSensuality.
.650. 0|aPopular culture.
.901. |a76213480
.902. |a824175
.903. |a76213480
.907. |aBOOKSTACKS
.909. |aBOOK
.912. |aMARCIVE 2015
.914. |aHF/1040/.7/H38/1987|bMAIN
.915. 2 |aACQ|bCOU|cUSD|d11.95|g941108|iMAIN|l3469|m950517|rLS|s951110
.916. |a0
.917. |a19950908
.918. |aBATCH
.919. |a20150418
.920. |aHPRETTY3
.999. |aHF1040.7 .H38 1987|wLC|c1|i31162008384548|d22/5/2013|e16/4/2013|lBOOKSTACKS|mQEII|n2|rY|sY|tBOOK|u10/4/1996|yBOOK
STEP 3
Call number processing and string conversions.
Call number processing in subfield a of the item holdings tag 999:
In order to comply with the call number structure expected by Alma and Primo for call number browsing we modified the spacing and added a period as shown in the example below:
Before:
P 231 H38 1995
After:
P231 .H38 1995
As per the Symphony Migration instructions we also had to also create a subfield v in the call number whenever one of the following strings occurred in the call number. Here is an example:
VOLUME
VOL
VOL.
V.
NO.
NUMBER
P.
PT.
PART
ADDENDUM
ADD.
SUPPLEMENT
SUPP
SUPP.
SUPPL.
SUPPL
BAND
BD.
BOOK
BK.
HEFT
HFT.
DISC.
BOOKLET
LEVEL
SER.
WERK.
ABT.
T.
INDEX
CELLO
VIOLIN
VIOLA
STRING BASS
BASS
BASS CLEF
VIOLINCELLO
HORN
PIANO
LOW
HIGH
Before:
.999. |aHM 251 S67172 1993 V.1|wLC|c1|i31162008293384|d12/10/1997|e4/7/1997|lBOOKSTACKS|mQEII|n5|p$35.00|rY|sY|tBOOK|u27/12/1995
After:
.999. |aHM251 .S67172 1993|vV.1|wLC|c1|i31162008293384|d12/10/1997|e4/7/1997|lBOOKSTACKS|mQEII|n5|p$35.00|rY|sY|tBOOK|u27/12/1995|yBOOK
Before:
.999. |aTD 1 T4 NO.251|wLC|c1|i31162006360656|d30/5/1996|lBOOKSTACKS|mQEII|rY|sY|tBOOK|u30/5/1996
After
.999. |aTD 1 .T4|vNO.251|wLC|c1|i31162006360656|d30/5/1996|lBOOKSTACKS|mQEII|rY|sY|tBOOK|u30/5/1996
FINAL STEP
#Flatskip the file produced by programmer to convert the records from flat to binary marc format:
cat Completed_mun_bib_01_20150525.flat | flatskip -aMARC -if -om > mun_bib_01_20150525.mrc
Suppressed bibs file of catkeys for Ex Libris
# This is part of the Bib script which
cat all_catkeys_not_SERSOL.ckeys.sorted | selcatalog -61 > suppressedrecsmemMarch22.txt
|
MARC Holdings File
# MARC Serial holdings were only available for the health sciences library. Holdings records were output in marc format and the catkey was output in the 004 tag:
selcatalog | catalogdump -lHEALTH -kc004 -om > marcholdingshealthmemMay25.mrc
|
Boundwiths File
Need to create a file of item ids (barcodes) and associated catkeys (bib records).
Step 1: create a file comprised of the parent callnum key with the child call num key.
|
selbound -oPN > boundfile1.txt
51978|2|51979|4|
51978|2|51980|3|
51978|2|51981|4|
…
...
Step 2: read in the parent call num key and output the input string (child call num key) along with the item id (barcode).
cat boundfile1.txt | selitem -iN -oSB > boundfile2.txt
51979|4|31162016064884 |
51980|3|31162016064884 |
51981|4|31162016064884 |
…
…
Step 3: read in the child callnumkey and output the item id (bar code) along with the catkey of the associated bib records.
cat boundfile2.txt | selcallnum -iK -oSC > boundwidthmemMar20.txt
31162016064884 |51979|
31162016064884 |51980|
31162016064884 |51981|
…
…
In the above example we have at least 3 bib records bound together under the same item id (bar code).
Serials
First, you generate a file containing serial control keys and associated catkeys. You then feed this file into the serctledit.pl program written by Oklahoma State University to generate the serials control flat file. The perl script puts the catkey in the “SERC.TITLE.KEY” element. You name the output file inside of the perl script.
selserctl -oKC > serctl.keys
perl serctledit.pl serctl.keys
# check Don's file for Uppercase subfield a
|
Issues
The issues file is generated using the following script:
selserctl | selprediction -iT | dumpflatissue > mun_prediction_issue.flat
|
Patrons
We decided to migrate only Symphony patrons which had charges, lost items, bills, charges and holds. The output from the various commands were merged into a single file of user keys sorted to remove duplicates.
seluserstatus "-tBLOCKED,BARRED,DELINQUENT" -oU > userstatus.userkeys
seluser "-7>0" -oK >lostitems.userkeys
seluser "-b>0" -oK >bills.userkeys
seluser "-c>0" -oK > charges.userkeys
#seluser "-h>0" -oK > holds.userkeys
selhold -aY -jACTIVE -oU > file6.userkeys
cat userstatus.userkeys lostitems.userkeys bills.userkeys charges.userkeys file6.userkeys > all.userkeys
cat all.userkeys | sort -u > Unique_all.userkeys
The next step is to create a set of internally managed and externally managed users. We use Banner for managing members of the university community (students, staff and retirees). The remainder (guests, temporary affiliates, etc,) are managed internally in Alma.
cat Unique_all.userkeys | seluser -iK "-pLIBSTAFF,ALUMNI,GUEST,HSLGUEST,MIGUEST,NONMUNGRAD,TESTUSER,EJOURNALS,HOLDINGS,I
CL,ILL,ILL-HOSP,ILL-QEII,ILL-REF,LIBRARYUSE" -oB > userinternal_mun_June16
cat userinternal_mun_June16 | seluser -iB | dumpflatuser > userextract_mun_internal_June16.txt
cat Unique_all.userkeys | seluser -iK "-p~LIBSTAFF,ALUMNI,GUEST,HSLGUEST,MIGUEST,NONMUNGRAD,TESTUSER,EJOURNALS,HOLDINGS,
ICL,ILL,ILL-HOSP,ILL-QEII,ILL-REF,LIBRARYUSE" -oB > userexternal_mun_June16
cat userinternal_mun_June16 | seluser -iB | dumpflatuser > userextract_mun_insternal_June16.txt
cat userexternal_mun_June16 | seluser -iB | dumpflatuser > userextract_mun_external_June16.txt
# The above files need to be edited in a text editor or by a program to insert the ".INTERNAL_EXTERNAL.” element with the value “ INTERNAL" for internal users and “EXTERNAL" for external users. Also, we added a note element with the value “Symphony_export” in the event we needed to identify these users in the future. Combine the above files into a single file.
|
The next step is to process the address component of the patron record as per the mapping specifications supplied in the Alma to Symphony migration form. A few things you need to know: Symphony supports 3 repetitions of address whereas, Alma supports two. The user address formats in Symphony may have been modified by your institution and therefore may not resemble what is expected by Alma. First, if you have 3 address formats decide which two you want to export to Alma. Also, given that user address formats in Symphony are based on pull-down values we have found that there is no standard entry of patron addresses.
.In our Symphony user address format the following elements are available and can appear in any order in a user record:
.STREET.
.CITY.
.PROVINCE.
.COUNTRY
.CITY/STATE.
.LINE.
.LINE1.
.LINE2.
.LINE3.
.LINE4.
The program needs to take the above elements are rewrite them as:
.USER_ADDR1_LINE.
.USER_ADDR1_LINE1.
.USER_ADDR1_LINE2.
.USER_ADDR1_LINE3.
.USER_ADDR1_LINE4.
and
.USER_ADDR2_LINE.
.USER_ADDR2_LINE1.
.USER_ADDR2_LINE2.
.USER_ADDR2_LINE3.
.USER_ADDR2_LINE4.
Note, that there is a maximum of 5 address lines in Alma. If your Symphony address contains more than 5 lines you can place the additional information in a note field. This is all dictated by the mapping document.
Before:
.STREET. 103 Larkhall Street
.CITY. St. John's
After:
.USER_ADDR1_LINE. 103 Larkhall Street
.USER_ADDR1_LINE1. St. John's
Loans
selcharge -oIUcdeyrsmnk | selitem -iK -oSB | seluser -iK -oBS > chargelist.flat.June16
#This creates a pipe-delimited file. Insert the following header in this file
user_key|date_time_charged|date_time_due|date_time_renewed|library|number_of_renewals|date_recalled|number_of_recall_notices|number_of_overdue_notices|date_claims_returned|itemID|
|
Fines and Fees
selbill -pN | dumpflatbill > openbills.mun.June16.txt
|
Requests
# you only want to export active requests
selhold -aY -jACTIVE -oIUypb | selitem -iK -oSB | seluser -iK -oBS > holddump.mun.June16.flat
|
Vendors
| |
Write a program which cleans up various elements in the vendor record including:
Address:
-
Symphony can have 3 vendor addresses whereas you can only have two addresses in Alma. If the Symphony vendor record has a 3rd address put the contents of this address in the DETAILS.NOTE field.
-
For ADDR1 and ADDR2 blocks:
.STREET. changed to .VEND_ADDR1.LINE. or VEND_ADDR2.LINE. increment LINE for each occurance of street .
Add VEND_ADDR1. Or VEND_ADDR2. prefix to remaining address elements
.TERMS. changed to .DETAILS_NOTE.
All occurances of .NOTE. and .COMMENT. are concatenated into a single .NOTE. or .COMMENT. element
Before
*** DOCUMENT BOUNDARY ***
FORM=LDVENDOR
.VEND_ID. |aA-LOG-F
.VEND_LIBRARY. |aHEALTH
.VEND_NAME. |aLOGIN BROTHERS CANADA-FIRM ORDERS
.VEND_CUSTOMER. |a200633
.VEND_CURRENCY. |aCAN
.VEND_GROUP1. |aCANADA
.VEND_GROUP2. |aJOBBER
.VEND_GROUP3. |aINVOICE
.VEND_ORDER_ACTIVE. |aY
.VEND_PAYING_ACTIVE. |aY
.VEND_ACCOUNTADDR. |a3
.VEND_SERVICEADDR. |a1
.VEND_ORDERADDR. |a1
.VEND_ADDR1_BEGIN.
.ATTN. |aORDER DEPT.
.STREET. |a324 SAULTEAUX CRESCENT
.CITY/STATE. |aWINNIPEG, MB
.ZIP. |aR3J 3T2
.COUNTRY. |aCANADA
.PHONE. |a800-665-1148
.PHONE. |a800/680-2889 ext. 5555 (Media Hotline-assistance with CD-ROM, computer file
products)
.FAX. |a800-665-0103
.EMAIL. |aURL: http://lb.ca/ OUR PIN NUMBER IS 874101.
.EMAIL. |aSales Rep: Irene Elek - Tel (800)665-1148; Voice Mail 344; (Fax)
800-665-0103
.EMAIL. |aIrene Elek: irene@lb.ca
.VEND_ADDR1_END.
.VEND_ADDR2_BEGIN.
.ATTN. |aRETURNS DEPT.
.STREET. |a291 TRADERS BLVD. EAST
.CITY/STATE. |aMISSISSAUGA, ON
.ZIP. |aL4Z 2E5
.PHONE. |a800/665-1148(SERVICE CENTRE)
.FAX. |a800/665-0103
.VEND_ADDR2_END.
.VEND_ADDR3_BEGIN.
.ATTN. |aAccounts Receivable
.STREET. |aP.O. Box 9518
.STREET. |aPostal Station A
.CITY/STATE. |aToronto, ON
.ZIP. |aM5W 2K3
.PHONE. |a800-665-1148, ext. 209 Sharon Olynick (Use this address if sending
remittance cheques)
.FAX. |a800-665-0103
.EMAIL. |ajaf@lb.ca (Sending payment - VISA)
.VEND_ADDR3_END.
.VEND_XINFO_BEGIN.
.TERMS. |a
.NOTE. |aPIN NUMBER: 874101
.COMMENT. |aGST REGISTRATION #R128679826
.COMMENT. |aNO PERMISSION NEEDED TO RETURN BOOKS THAT ARE IN RESALABLE CONDITION(SEE
LOGIN FILE FOR PROCEDURES)
.COMMENT. |aNO LONGER HANDLES SAUNDERS OR MOSBY or CHURCHILL LIVINGSTONE
TITLES--ONLY SELLING WHAT'S LEFT IN STOCK(08/11/96)
.VEND_XINFO_END.
After
*** DOCUMENT BOUNDARY ***
FORM=LDVENDOR
.VEND_ID. |aA-LOG-F
.VEND_LIBRARY. |aHEALTH
.VEND_NAME. |aLOGIN BROTHERS CANADA-FIRM ORDERS
.VEND_CUSTOMER. |a200633
.VEND_CURRENCY. |aCAN
.VEND_GROUP1. |aCANADA
.VEND_GROUP2. |aJOBBER
.VEND_GROUP3. |aINVOICE
.VEND_ORDER_ACTIVE. |aY
.VEND_PAYING_ACTIVE. |aY
.VEND_ACCOUNTADDR. |a3
.VEND_SERVICEADDR. |a1
.VEND_ORDERADDR. |a1
.VEND_ADDR1_BEGIN.
.VEND_ADDR1.ATTN. |aORDER DEPT
.VEND_ADDR1.LINE. |a324 SAULTEAUX CRESCENT
.VEND_ADDR1.CITY/STATE. |aWINNIPEG, MB
.VEND_ADDR1.ZIP. |aR3J 3T2
.VEND_ADDR1.COUNTRY. |aCANADA
.VEND_ADDR1.PHONE. |a800-665-1148
.VEND_ADDR1.PHONE. |a800/680-2889 ext. 5555 (Media Hotline-assistance with CD-ROM, computer file products)
.VEND_ADDR1.FAX. |a800-665-0103
.VEND_ADDR1.EMAIL. |aURL: http://lb.ca/ OUR PIN NUMBER IS 874101 Sales Rep: Irene Elek - Tel (800)665-1148; Voice Mail 344; (Fax) 800-665-0103 aIrene Elek: irene@lb.ca
.VEND_ADDR1_END.
.VEND_ADDR2_BEGIN.
.VEND_ADDR2.ATTN. |aRETURNS DEPT
.VEND_ADDR2.LINE1. |a291 TRADERS BLVD. EAST
.VEND_ADDR2.CITY/STATE. |aMISSISSAUGA, ON
.VEND_ADDR2.ZIP. |aL4Z 2E5
.VEND_ADDR2.PHONE. |a800/665-1148(SERVICE CENTRE)
.VEND_ADDR2.FAX. |a800/665-0103
.VEND_ADDR2_END.
.VEND_XINFO_BEGIN.
.DETAILS_NOTE. |aAccounts Receivable, P.O. Box 9518, Postal Station A, Toronto, ON, M5W 2K3, PHONE 800-665-1148, ext. 209 Sharon Olynick (Use this address if sending remittance cheques), FAX. |a800-665-0103, jaf@lb.ca (Sending payment - VISA)
.NOTE. |aPIN NUMBER: 874101
.COMMENT. |aGST REGISTRATION #R128679826 NO PERMISSION NEEDED TO RETURN BOOKS THAT ARE IN RESALABLE CONDITION(SEE LOGIN FILE FOR PROCEDURES) NO LONGER HANDLES SAUNDERS OR MOSBY or CHURCHILL LIVINGSTONE TITLES--ONLY SELLING WHAT'S LEFT IN STOCK(08/11/96)
.VEND_XINFO_END.
Funds
|
selfund -oFJr | selfundcyc -iF -f">2012" -oSa > fundinfo.flat.mun.feb27
Sample data from the above file:
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|$24303.86|
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|$6137.00|
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|$1935.00|
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|$1935.00|
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|-$1.00|
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|$23740.00|
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|$21876.00|
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|$0.00|
QEII|72000-01|MAIN BOOK COMPUTER SCIENCE|$31533.00|
….
….
The data was then transformed into an excel spreadsheet, where we modified the fund id to make it unique across libraries and fiscal cycles by adding a library code and fiscal cycle to the end of each fund ID. We also added the following columns to the spreadsheet
Ledger Coder
Ledger Name
Massage the spreadsheet until it looks like the following:
P2E
| |
In our Symphony system all electronic items have a current location of “INTERNET”. Within this “INTERNET” set databases have an itemcat1 value of “EINDEX”. We also are excluding Serials Solutions records from this set.
Basically, create 2 sets of catkeys, one for databases and the other for the remaining electronic items.
Using a text editor or programme, you need to append “,db” to each database catkey, and “,portfolio” to the remaining electronic items. When you are done, combine both files into a single file.
cat visiblelist.catkey | selitem -g"~SERSOL" -mINTERNET -eEINDEX -oC > mp2eElectronic.ckeys
cat visiblelist.catkey | selitem -g"~SERSOL" -mINTERNET -e"~EINDEX" -oC > mp2eNotElectronic.ckeys
cat visiblelist.catkey | selitem -g"~SERSOL" -lINTERNET -eEINDEX -oC > lp2eElectronic.ckeys
cat visiblelist.catkey | selitem -g"~SERSOL" -lINTERNET -e"~EINDEX" -oC >lp2eNotElectronic.ckeys
cat p2eElectronic.ckeys | sort -u > p2eElectronic.ckeys.sorted
cat p2eNotElectronic.ckeys | sort -u > p2eNotElectronic.ckeys.sorted
# below are sample records from the p2e file
254094,db
478944,db
731027,db
1144502,db
1157910,db
1167550,db
1174819,db
1178642,db
1196772,db
1249650,db
$ tail p2eMar20.txt
4585597,portfolio
4585598,portfolio
4585599,portfolio
4585600,portfolio
4586713,portfolio
4587303,portfolio
4587304,portfolio
4587305,portfolio
4587306,portfolio
Orders
|
|
We are actually generating orderline records which are aggregated into purchase orders in Alma.
The goal here is to insert the catkey of the bib record associated with the orderline and the orderline key into each orderline record. The catkey is used to link the orderline records to the bib records and the orderline key is used to link orderline records to corresponding invoiceline records.
# we limited the export of orders to the last three years
selorder -f">2012<2017" -oKG > orderkeys_3yrs
Here is a sample of records output by the above script:
247293|MARINE|PO-18|2013|
247295|MARINE|PO-75|2013|
247296|MARINE|PO-39|2013|
247297|MARINE|PO-263|2013|
247299|MARINE|PO-314|2013|
247301|MARINE|PO-296|2013|
247302|MARINE|PO-134|2013|
# next generate the orderline records
cat orderkeys_3yrs | dumpflatorder > order.records.3yrs.mun.May25
Here is a sample record:
*** DOCUMENT BOUNDARY ***
.ORDR_ID. |aPO-4932
.ORDR_LIBR. |aQEII
.FISCAL_CYCLE. |a2013
.VEND_ID. |aPER-EBSCO-USD
.ORDR_TYPE. |aSUBSCRIPT
.ORDR_DATE_READY. |aNEVER
.ORDR_DATE_MAILED. |aNEVER
.ORDR_DATE_CLAIM. |aNEVER
.ORDR_DATE_CANCEL. |aNEVER
.ORDR_XINFO_BEGIN.
.ORDR_XINFO_END.
.LINE_ITEM_BEGIN.
.LINE_ITEM_ID. |a1101091-1001
.LINE_UNIT_PRICE. |a0.00
.LINE_VEND_CURR. |aUSD
.LINE_EXCHANGE. |a.9984
.LINE_COPIES. |a1
.LINE_DISCOUNT. |aTABLE
.LINE_EXTEND_PRICE. |a0.00
.LINE_MULTI_PARTS. |aN
.LINE_TITLEID. |a1082-9636
.LINE_RENEWAL_DATE. |aNEVER
.LINE_DATE_MAILED. |a20060504
.LINE_XINFO_BEGIN.
.DESC. |aPrint + Online (free)
.INSTRUCT. |aCANCELLED print after 2012 (as per Collections) Will get online through e-Duke pkg on PO-65038
.NOTE. |a72100
.LINE_XINFO_END.
.FUND_SEGMENT_BEGIN.
.LINE_FUND_ID. |a72100-67
.LINE_FUND_COPIES. |a1
.FUND_DATE_PAID. |aNEVER
.FUND_AMT_PAID. |a0.00
.FUND_SEGMENT_END.
.DIST_SEGMENT_BEGIN.
.HOLDING_CODE. |aMUPE
.LINE_DIST_COPIES. |a1
.DIST_DATE_RECEIVED. |a20120401
.DIST_DATE_DELIVERED. |a20120401
.DIST_SEGMENT_END.
.LINE_ITEM_END.
#next generate the file of order keys, catkeys and orderline keys
cat orderkeys_3yrs | selorderlin -iO -oSCQ > ordersorderlines.May25
Here is a sample of records output by the above script (library, purchase order number, fiscal cycle, which together comprise the order key followed by the catkey and the orderline key).
MARINE|PO-18|2013|594808|693655|
MARINE|PO-75|2013|163818|693657|
MARINE|PO-39|2013|167405|693658|
MARINE|PO-263|2013|1571250|693659|
MARINE|PO-314|2013|1185221|693661|
MARINE|PO-296|2013|414849|693663|
MARINE|PO-134|2013|3682718|693664|###
MARINE|PO-204|2013|1201568|693665|
# next write a program which will add the catkey and orderline key to the orderline record. First, check to see that both files you are working on match up on the ORDR_ID, ORDR_LIBR and FISCAL_CYCLE. If you find a discrepancy this will typically occur in the orderline file in which case remove the offending record. With the files matched up proceed to insert the catkey and orderline key in the orderline record. Also, if you modified your fund ids in the funds file you will need to perform the same transformation of the fund id in the orderline record. Finally, some notes fields in the file like COMMENT and DESC and NOTE may contain line breaks. These lines need to be concatenated into a single line.
Orderlines can also be linked to bibs and items using the LINE-ITEM.ID which for received physical items may contain a bar code. This typically becomes a fuzzy match.
Before
*** DOCUMENT BOUNDARY ***
.ORDR_ID. |aPO-4932
.ORDR_LIBR. |aQEII
.FISCAL_CYCLE. |a2013
.VEND_ID. |aPER-EBSCO-USD
.ORDR_TYPE. |aSUBSCRIPT
.ORDR_DATE_READY. |aNEVER
.ORDR_DATE_MAILED. |aNEVER
.ORDR_DATE_CLAIM. |aNEVER
.ORDR_DATE_CANCEL. |aNEVER
.ORDR_XINFO_BEGIN.
.ORDR_XINFO_END.
.LINE_ITEM_BEGIN.
.LINE_ITEM_ID. |a1101091-1001
.LINE_UNIT_PRICE. |a0.00
.LINE_VEND_CURR. |aUSD
.LINE_EXCHANGE. |a.9984
.LINE_COPIES. |a1
.LINE_DISCOUNT. |aTABLE
.LINE_EXTEND_PRICE. |a0.00
.LINE_MULTI_PARTS. |aN
.LINE_TITLEID. |a1082-9636
.LINE_RENEWAL_DATE. |aNEVER
.LINE_DATE_MAILED. |a20060504
.LINE_XINFO_BEGIN.
.DESC. |aPrint + Online (free)
.INSTRUCT. |aCANCELLED print after 2012 (as per Collections) Will get online through e-Duke pkg on PO-65038
.NOTE. |a72100
.LINE_XINFO_END.
.FUND_SEGMENT_BEGIN.
.LINE_FUND_ID. |a72100-67
.LINE_FUND_COPIES. |a1
.FUND_DATE_PAID. |aNEVER
.FUND_AMT_PAID. |a0.00
.FUND_SEGMENT_END.
.DIST_SEGMENT_BEGIN.
.HOLDING_CODE. |aMUPE
.LINE_DIST_COPIES. |a1
.DIST_DATE_RECEIVED. |a20120401
.DIST_DATE_DELIVERED. |a20120401
.DIST_SEGMENT_END.
.LINE_ITEM_END.
After
*** DOCUMENT BOUNDARY ***
.ORDR_ID. |aPO-4932
.ORDR_LIBR. |aQEII
.FISCAL_CYCLE. |a2013
.CATALOG_NUM. |a1101091
.ORDERLINEKEY. |a694861
.VEND_ID. |aPER-EBSCO-USD
.ORDR_TYPE. |aSUBSCRIPT
.ORDR_DATE_READY. |aNEVER
.ORDR_DATE_MAILED. |aNEVER
.ORDR_DATE_CLAIM. |aNEVER
.ORDR_DATE_CANCEL. |aNEVER
.ORDR_XINFO_BEGIN.
.ORDR_XINFO_END.
.LINE_ITEM_BEGIN.
.LINE_ITEM_ID. |a1101091-1001
.LINE_UNIT_PRICE. |a0.00
.LINE_VEND_CURR. |aUSD
.LINE_EXCHANGE. |a.9984
.LINE_COPIES. |a1
.LINE_DISCOUNT. |aTABLE
.LINE_EXTEND_PRICE. |a0.00
.LINE_MULTI_PARTS. |aN
.LINE_TITLEID. |a1082-9636
.LINE_RENEWAL_DATE. |aNEVER
.LINE_DATE_MAILED. |a20060504
.LINE_XINFO_BEGIN.
.DESC. |aPrint + Online (free)
.INSTRUCT. |aCANCELLED print after 2012 (as per Collections) Will get online through e-Duke pkg on PO-65038
.NOTE. |a72100
.LINE_XINFO_END.
.FUND_SEGMENT_BEGIN.
.LINE_FUND_ID. |a72100-67-01-2013
.LINE_FUND_COPIES. |a1
.FUND_DATE_PAID. |aNEVER
.FUND_AMT_PAID. |a0.00
.FUND_SEGMENT_END.
.DIST_SEGMENT_BEGIN.
.HOLDING_CODE. |aMUPE
.LINE_DIST_COPIES. |a1
.DIST_DATE_RECEIVED. |a20120401
.DIST_DATE_DELIVERED. |a20120401
.DIST_SEGMENT_END.
.LINE_ITEM_END.
Invoices
You are working will two files: the invoice file which is an xml file comprised of invoicelines and a pipe-delimited file containing the library, purchase order number and orderline key elements:
|
selinvoice -r">2012<2017" > invoice.keys
cat invoice.keys | prtinvoice --xmlfull > mun_invoices.may25.xml
cat invoice.keys | selinvoice -iL -oLY | selinvline -iL -oSQ > invoiceorderline
s.May25
In the above script you pipe invoice keys to selinvoice to obtain the library and invoice id and then use selinvline to obtain the orderline key which matches the invoiceline. It is important to note that we are working with invoices which are comprised of one or more invoicelines. The invoices file and the tab-delimited file have records in the same order. Below we see the library, invoice id and orderlinekey. Note that invoice 727410 has 4 occurances which means that it is comprised of 4 invoicelines with each invoiceline attached to a different orderline. And knowing from above that orderliness are attached to bibs we now have the complete picture of how invoicelines are linked to orderliness which are linked to bibs.
QEII|1276350/0412|697252|
QEII|815559|700974|
QEII|727410|695580|
QEII|727410|695571|
QEII|727410|695572|
QEII|727410|695574|
QEII|2805180|694006|
QEII|DE137565|696250|
| |
Now it’s time to create a MYSQL database which begins with:
Library | invoice id | orderlinekey
You then parse the orderline file created above and obtain additional data elements and put them into the table:
.FISCAL_CYCLE. |a2015
.VEND_ID. |aACQ-NAUTICALMIND-CAN
.LINE_VEND_CURR. |aCAN
.LINE_EXCHANGE. |a1.0
.LINE_FUND_ID. |a72502-20-01-2015
The invoices xml file is then parsed and when a "Library and ObjectID" are identified the mySQL table is searched on these two fields
to retrieve the orderliness lines which correspond to the in the invoice. For each invoiceline we add the following elements from the table:
123456
You also add the element from the table following the element near the beginning of the invoice since all invoicelines within a invoice have the same currency. Finally, you need to convert the element in the invoice to conform with any changes you have made in the funds file.
This approach works for the most part. When it doesn’t it is usually due to bad Symphony data or invoicelines which are linked to credits or extra charges like shipping.
Before
QEII
36779-12/13
PER-LIBCON
90988
2013
1100.00
1100.00
1091.38
1091.38
2012-04-02
2012-04-02
2012-03-28
Pd on visa (fax Apr. 2/12)
72106-01-2013
2013
72016
0.00
1091.38
1
1
1
1
KWHITFIE
KWHITFIE
0001
1100.00
1100.00
1091.38
1091.38
1.0079
2012-04-02
Apr.18/12-Apr.17/13
PO-36779
2013
1
1
1100.00
2012-04-02
2012-04-02
KWHITFIE
KWHITFIE
After
QEII
36779-12/13
PER-LIBCON
90988
2013
USD
1100.00
1100.00
1091.38
1091.38
2012-04-02
2012-04-02
2012-03-28
Pd on visa (fax Apr. 2/12)
72106-01-2013
2013
72016
0.00
1091.38
1
1
1
1
KWHITFIE
KWHITFIE
0001
696976
1.0079
72106-01-2013
2013
1100.00
1100.00
1091.38
1091.38
1.0079
2012-04-02
Apr.18/12-Apr.17/13
PO-36779
2013
1
1
1100.00
2012-04-02
2012-04-02
KWHITFIE
KWHITFIE
Local Authorities
|
cat provauthorityxx.ids | selauthority -iF -oK | authdump -ki > localauthorities.flat
cat localauthorities.flat | flatskip -if -om -aTITLE > localauthorities.mun.May25.mrc
|
|
|
|
|
Share with your friends: | 
