Re: Excel to MARC

Here's a program I wrote for our tech services department to make  
brief bibs from vendor spreadsheets.

Tech services get a spreadsheet from the vendor with information about  
newly ordered material. Each vendor obviously has a different data  
layout.

They copy the order spreadsheet into a new spreadsheet based on a  
template. They've created a template for each of their vendors. The  
first row has the desired MARC tags for the data in the column. The  
second row has default values. Third - nth row is vendor data.

Then they export the data to a tab delimited file and run this  
program. It makes MARC records that are loaded into III Millenium.

Feedback is welcome :)

Rich Ackerman
Nova Southeastern University
[email protected]


# perl script to create MARC records from a tab delimited textfile
# rich ackerman, april 25, 2007
#			
# Updates:	June 28, 2008 - generalize to new input data structure	
#		
##########################################################################
#
# This program makes MARC records from an input file, ready for
# uploading to NSU's Millenium system. If there are any problems
# with the data an error message is printed.
#
##########################################################################
#
# Input:	The input is a tab delimited file. There are three types
#			of input lines.
#
# 			1. The first line has MARC tag definitions for each column.
#			Every column must have a MARC tag definition.
#	
#			There are three different kinds of MARC tag definitions.
#
#			1a. Full Tag Definition - always six characters long.
#
#				A full tag defines a complete MARC tag with indicators and a  
subfield.
#				If you want to specify indicators do it in the first instance of  
the tag.
#
# 				Position	Value
#
#				1-3			Field Tag
#				4			First Indicator - space means "no indicator"
#				5			Second Indicator - space means "no indicator"
#				6			Subfield Code
#
# 				Examples: "1001 a", "260  b", "935  a", "24514a"
#
#			1b. Subfield Definition - always five characters long
#
#				A subfield definition defines a tag with no indicators OR adds a
#				subfield to an existing tag. The program will create the tag with
#				blank indicators if it is not already defined.
#
#				If the tag was defined in an earlier column, the subfield will be  
added.
#
#				Position	Value
#
#				1-3			Field Tag
#				4			Pipe (|)
#				5			Subfield code
#				
#				Examples: 960|a, 961|b
#
#			1c. Skip Column Definition - always three characters long
#
#				A tag 000 means skip the column. This will be used to void  
columns of
#				data that are not wanted in the MARC record.
#
# 			2. The second line specifies an optional default value for each  
MARC tag
#			in the first line.
#
# 			3. Lines 3-n contain data. The default value from the second line  
is used
#			if there is no value given.
#
##########################################################################
#
# Output: 	Two files containing MARC data. One is a compiled binary  
for loading into Millenium.
# 			The second is a text image for human inspection.
#
##########################################################################
#
# Calling sequence: p>perl bb.pl FILENAME [FILENAME2 ...]
#
##########################################################################

use MARC::Record;
use MARC::Field;
use strict;

my $i;			# count number of lines of input
my @marc;		# array to store MARC tags (line 1)
my @defaults;	# array to store default values (line 2)
my @data;		# array to store each input line (line 3 - n)

my $SKIPCOLUMN = '000';	# flag that says to ignore the whole column

# loop through multiple input files (even though we only expect one  
I'm lazy)

foreach (@ARGV) {

	# print "\nProcessing $_\n";

	$i = 0; # simple counter to count number of record processed
	
	my $inputfile  = $_;
	my ($name, $ext) = split (/\./, $inputfile);
	my $outputfile = $name . '.mrc';
	my $textdump = $name . '.mrc.txt';
	
	# open the input stream	
	open (INPUT, $inputfile) or die "Cannot open input file $inputfile:  
$!";
		
	# open the output streams
	open (OUTPUT, "> $outputfile") or die $!;
	open (TEXTDUMP, "> $textdump") or die $!;
	
	# read each line of the current input file and make a MARC record for  
it
	while ( <INPUT> ) {
		
		chomp($_); # get rid of line ending
		@data = split (/\t/);
		
		# First line? Save MARC tag definitions
		if ( $i == 0 ) {
			@marc = @data;
			print "Saving MARC definitions @marc\n";

		# Second line? Save default values
		} elsif ($i == 1) {
			@defaults = @data;
			print "Saving defaults @defaults\n";
		
		# Data line? Process it
		} else {
			
			# Feedback is a good thing
			print "Processing $data[0]\n";

			# create new record for this input line	
			my $record = MARC::Record->new();

			# Go through each column of data. Each column has a MARC tag
			# in @marc and a default value in @defaults. If the MARC tag
			# is the skip value then we just skip the column - information from
			# the vendor that we just don't need to bother with.
			 									
			for ( my $j = 0; $j < @marc; $j++ ) {
									
				next if $marc[$j] eq $SKIPCOLUMN;													
				
				# get the value we are going to set
				my $value = $data[$j] ? $data[$j] : $defaults[$j];
				
				# Now figure out what tag we are updating				
				my ($tag, $ind1, $ind2, $subfield) = '';								
				if ( length($marc[$j]) == 6 ) {

					# This is a full one, like 24514a or 156 3b
					$tag = substr($marc[$j], 0, 3);
					$ind1 = substr($marc[$j], 3, 1);
					$ind2 = substr($marc[$j], 4, 1);
					$subfield = substr($marc[$j], 5, 1);

				} elsif (length($marc[$j]) == 5 ) {
					
					# Just a subfield, like 960|a
					$tag = substr($marc[$j], 0, 3);
					$subfield = substr($marc[$j], 4, 1);

				} else {
					
					# This should never happen...
					my $errlen = length ( $marc[$j] ) ;
					print "Hey, this $marc[$j] is the wrong length: $errlen!\n";
					die;
				}
				
				my $field = $record->field($tag);
				
				# If the field exists and there is data to be stored in it,
				# then add another subfield.
				if ($field && $tag != 961) {									
					$field->add_subfields( $subfield => $value);				 					
					# print "Adding $value to $subfield\n";
				} else {				

				# Need to create a new field 				
					$field = MARC::Field->new (
						$tag, $ind1, $ind2,
						$subfield => $value);
					$record->append_fields($field);
				}
			}
					
			# Finally put the record in the output file
			print OUTPUT $record->as_usmarc();
			print TEXTDUMP $record->as_formatted();
			print TEXTDUMP "\n\n";
		}
		++$i; # bump line counter
	}
	close(OUTPUT);
	close(TEXTDUMP);
	
	$i = $i - 2; # don't count the line with the default settings on it  
or the MARC tags
	print "\n\nPut $i records into $outputfile and $textdump \n\n";
}






On Nov 13, 2008, at 12:40 AM, Saiful Amin wrote:

> On Wed, Nov 12, 2008 at 11:03 PM, Yan Liao  
> <[email protected]> wrote:
>
>> Does any one know how to trans data from Excel to MARC? There is a  
>> rush
>> local project and I don't have time to review my PERL class notes.
>>
>
> The best method would be to convert the Excel file into a delimited  
> text
> file (using 'Save As ..') and use MARC::Record to write the MARC  
> data by
> hand. There is a marvelous tutorial available for this module [1]  
> with lot
> of real examples. Once you get comfortable with this module you  
> probably
> won't look anywhere else for MARC related jobs in future.
>
> Having said that, I've just started working on a personal project of
> creating a web-based utility to enable conversion of Excel data into  
> MARC21.
> You can have look at the pre-alpha version in [2]. It is far from  
> complete,
> e.g., the download option in iso2709 or marcxml is yet to be  
> written. Any
> feedback would be welcome.
>
> [1]
> http://search.cpan.org/~mikery/MARC-Record-2.0.0/lib/MARC/Doc/Tutorial.pod
> [2] http://122.166.0.252/cgi-bin/xl2marc.pl
>
> -- 
> Saiful Amin
>
> Edutech India Pvt Ltd
> Bangalore, India.
> www.edutech.com