Extracting $ from PAID fields

helmetpastoralSoftware and s/w Development

Dec 13, 2013 (3 years and 7 months ago)

60 views

Millennium
Management Information


Extracting $ from PAID fields



Created:
Oct

2007
; Revised: Feb 2010


I NNOVATI VE OPERATI ONS GROUP

http://staffweb.lib.washington.edu/
committees/iog/

Extracting $ from PAID fields


The PAID field in the order record shows how much was paid for an item,
the payment
date

and other information, such as the invoice number. Items, such as serials, standing
orders or multi
-
volume sets, will have multiple paym
ent lines for each year or volume.




Although the various parts of the payment line (date,

invoice number,

amount etc) appear
in separate columns and each payment appears on a separate line, they are all part of one
field in the order record. Because Inn
ovative is not currently capable of exporting
only
part of a field (
such as th
e payment amount)

determining how much was spent for serials
on a particular fund during a certain time period is difficult.


Mark Kibbey, Head, Integrated Library Systems, has w
ritten a script
which will reduce
the PAID field to just the payment amount.




Installing

ActivePerl

1.

Install ActivePerl on your computer.
Copy the ActivePerl file ending in .msi in ITS
shared docs located at:
\
\
files
\
shareddocs
\
LibrarySystems
\
IIIDocument
ation
\
PERL
.
Paste
the file

into your downloads folder (
C:
\
My Downloads
).


Alternately, it can be downloaded to your computer from:

http://www.activestate.com/activeperl/

Choose the download for Windows
, MSI.


2.

In your downloads folder,
double
-
click on the ActivePerl icon.

When asked if you
want to run the software, select
Y
es
. The Installation Wizard dialog box will open.


3.

Follow the default selections

in the Installation Wizard by selecting
Next

and the
n
select
Install

to begin the installation process. Uncheck
Display the Release Notes
and
select
Finish
.



Installing

the conversion script

4.

On your hard drive

set up the following file structure

(if it does not already exist)
:
C:
\
\
Perl
\
scripts

Millennium
Management Information


Extracting $ from PAID fields



Created:
Oct

2007
; Revised: Feb 2010


I NNOVATI VE OPERATI ONS GROUP

http://staffweb.lib.washington.edu/
committees/iog/


5.

The
convers
ion script requires an “input” file, an “output” file and an “error” file. The
script will look for the PAID fields in the input file and return the converted PAID
fields to the output file, along with any errors to the error file.


Using Notepad or Wordp
ad,
create two empty text files, name them selectorout.txt
and selectorerr.txt and save them to C:
\
\
Perl
\
scripts.

(From the Start menu, select
Programs/Accessories/Notepad).


6.

Copy and
paste the Perl script

below
into a notepad or Wordpad file. Save the
not
epad file as selector.pl to your scripts folder.


#file to process selector lsits and extract funds.

#mkibbey 9/25/07 redone for 2007 output format



#monitors for duplicate orders. To run this first sort input.

#5/24/03 added cate note from order to fie
lds..

$selout = "C:/Perl/scripts/selectorout.txt";

$selin = "C:/Perl/scripts/selectorin.out";

$selerr = "C:/Perl/scripts/selectorerr.txt";



unlink($selout) if $selout; #remove old output

unlink($selerr) if $selerr;


open (SOUT, ">>$selout")

or die "Cannot open $selout for append
:$!";

#open (SERR, ">>$selerr") or die "Cannot open $selerr for append
:$!";

$linenum = 0;


$prevrecord= " ";

open (IN_LIST, "$selin") or die "Cannot open $selin for read :$!";


while($newline=<IN_LIST>) {





($record, $fund, $location, $link, $title, $author,
$pubinfo,$issn,$paid) = split /
\
t/, $newline, 9;


next if ($record eq $prevrecord); #skip if duplicate order
-

MUST BE SORTED.


$linenum++; #ok its a new record up counter an
d
process


$prevrecord = $record; #save record nummber for check against
next line.

#print "the paid field is $paid
\
n";


#instead we propcess the payments
-

up to 32 of them perhaps


(@payment) = split /
\
|/, $paid;


$to
talpaid= '='; #start of excell formula




foreach $entry(@payment) {


#each payment section has 7 tabbed fields and we want number 4


# print "paid entry is $entry
\
n";


($pd1, $pd2,$pd3,$amoun
t,$restofpaid )= split /
\
t/,
$entry, 5 ;


# print "parts of paid are $pd1 $pd2 $pd3 $amount
$restofpaid
\
n";

Millennium
Management Information


Extracting $ from PAID fields



Created:
Oct

2007
; Revised: Feb 2010


I NNOVATI VE OPERATI ONS GROUP

http://staffweb.lib.washington.edu/
committees/iog/


$amount=~s/,//g;


if ($amount=~/
-
.*/) {


$totalpaid=$totalpaid.$amount;


print "negative entry $amount
\
n";



}else {




$totalpaid=$totalpaid.'+'.$amount;


}


} #end foreach entry in payment




$outline =join "
\
t",
$record,$fund,$locatio
n,$link,$title,$author,$pubinfo,$issn,$totalpa
id;


print SOUT "$outline
\
n"


} #end of newlines


close



Export
ing

your

list of serials

(#105074)

There is
currently
no option in Millennium to limit
the PAID fields to a particular range
of

dates; use text
-
based. You may create your list in Millennium, but you must export it
through text
-
based.


7.

Create a list of order records. See the handout
Create Lists
.


8.

Open a text
-
based session and from the main menu choose
M > MANAGEMENT
information
, t
hen
L > Create LIST of records
. Choose your list by entering the
list number.


9.

Select
U > Output USER
-
selected format
.


10.

Select
C > CREATE a new file for output
.


11.

You will be prompted to list the fields you want to export.
At the
Output item #1 >

prompt, e
n
ter the
fields in the following order
:


Rec
ord (26), fund (12), location (
2),
link (l),
title (!245), au
thor
(a), pubinfo (p), issn (m)
, paid

(/0)


Note:

to switch to/from order record fields to/from bibliographic record fields, type ?

and then the record
type you want to switch to (i.e. b or o). If you enter the wrong
field, hit ESC and then B to backup one condition.


Millennium
Management Information


Extracting $ from PAID fields



Created:
Oct

2007
; Revised: Feb 2010


I NNOVATI VE OPERATI ONS GROUP

http://staffweb.lib.washington.edu/
committees/iog/




12.

Once you have selected

the PAID field, the system will prompt you to limit payments
to those made within a p
articular time period. En
ter the time period yo
u wish to
include in the report and choose y if they are correct.




13.

When you have finished listing the fields you want to export, a
t the Output item
prompt, type E
NTER
.


14.

The field delimiter

should be

set to TAB;
the text qualifier t
o NONE;
the repeated
field delimiter set as pipe (|).
Not set correctly?



C
hange the fi
eld delimiter by selecting 1 (for the field delimiter), then selecting 1
(for control character) and entering 09.



Change the text qualifier by selecting 2 (for the text
qualifier), then selecting 3 for
NONE.



Change the repeated field delimiter by selecting 3 (for the repeated field
delimiter), then selecting 2 (for ASCII character) and entering | (Shift +
\
).


15.

Select
CREATE a new file for output
.



16.

At the
File Name:

promp
t,
enter your name to name your file

(e.g.
johndoe
)
and hit
ENTER.


Hit

? to switch
to/from
order
field choices
to/from

bibliographic
field choices.

Then hit b or o to choose your record type.
If you enter the wrong field, hit the
ESCape key and Backup one condition..

Millennium
Management Information


Extracting $ from PAID fields



Created:
Oct

2007
; Revised: Feb 2010


I NNOVATI VE OPERATI ONS GROUP

http://staffweb.lib.washington.edu/
committees/iog/

17.

File creation in progress .... File creation completed! Output the
file now? (y/n)

Choose y.


18.

Choose
ftp.lib.washington.edu


19.

Enter
anonymous

for the usename and
anything

for the
password
.


20.

Scroll through the list to make sure your file has
PUT

beside it.



21.

At the prompt, hit T for Transfer files.

The name of your file should be listed.




22.

Rename your file /incoming/selectorin.out and hit ENTER.




23.

Hit C for continue. The file should now
be sitting in
\
\
files
\
ftp
\
incoming
.

Confirm that
the file has been transferred before proceeding to the next step.


24.

Delete your output file from the text
-
based system. Hit Q to Quit,
hit O to Output a
file,
then at the prompt hit D for delete and select th
e output file.


25.

From
the Windows Start menu, select Run. In the Run dialog box, type
\
\
web
files
\
ftp
\
incoming. Find
your

.out
” file

and copy
it

to C:
\
\
Perl
\
scripts.



Running the script & Viewing results

26.

Open
C:
\
\
Perl
\
scripts.
Rename your file
selectorin.o
ut
.
E.g. if your file name was
johndoe.out, rename the file selectorin.out.


27.

Double
-
click on selector.pl

to run the script.


28.

The script will output results to selectorout.txt and errors to selectorerr.txt.

Right
-
click on selectorout.txt and choose Open wit
h Microsoft Excel.


29.

In Microsoft Excel, choose File, Save As and
save your converted file
.


30.

Before converting another review file, be sure to delete the selectorin.out file and
empty the selectorout.txt and selectorerr.txt files (Open the .txt files, selec
t all by
typing
Ctrl+A
and then hit
Delete
).