From 80f9037ea8739783fc1ad65cadc900f0bb105be2 Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Mon, 23 Jun 2003 20:20:32 +0000 Subject: [PATCH] added Microsoft Excel file import git-svn-id: file:///home/dpavlin/private/svn/webpac/trunk@54 13eb9ef6-21d5-0310-b721-a9d68796d827 --- all2xml.conf | 59 ++++++++-------- all2xml.pl | 154 +++++++++++++++++++++++++++++------------ import_xml/excel.xml | 159 +++++++++++++++++++++++++++++++++++++++++++ parse_format.pm | 65 +++++++++++++++++- swish_isis.conf | 3 +- 5 files changed, 365 insertions(+), 75 deletions(-) create mode 100644 import_xml/excel.xml diff --git a/all2xml.conf b/all2xml.conf index 3499d3d..fe82c8b 100644 --- a/all2xml.conf +++ b/all2xml.conf @@ -1,37 +1,36 @@ -# global configuration file - -# global options -[global] - # define database to use for indexes - dbi_dbd=Pg - dbi_dsn=dbname=webpac - dbi_user=dpavlin - dbi_passwd=xx - # describe your databases here -[ps-libri] - # directory for isis databases - isis_db=/data/isis_data/ps/LIBRI/LIBRI - # this will use xml file in import_xml/ dir - type=isis - # this is arbirary xml added to each record (e.g. Library identifier - # or something like that - xml=Knjiga -#[ge-libri] -# isis_db=/data/isis_data/ge/LIBRI/LIBRI +### ISIS ### +# +#[ps-libri] +# # directory for isis databases +# isis_db=/data/isis_data/ps/LIBRI/LIBRI +# # this will use xml file in import_xml/ dir # type=isis +# # this is arbirary xml added to each record (e.g. Library identifier +# # or something like that # xml=Knjiga +# +##[ge-libri] +## isis_db=/data/isis_data/ge/LIBRI/LIBRI +## type=isis +## xml=Knjiga +# +# +#[ps-peri] +# isis_db=/data/isis_data/ps/PERI/PERI +# type=isis +# xml=Èasopis +# +#[kk-arti] +# isis_db=/data/isis_data/kk/ARTI/ARTI +# type=isis +# xml=Èlanak +# +### M$ Excel ### - -[ps-peri] - isis_db=/data/isis_data/ps/PERI/PERI - type=isis +[ffzg-journals] + type=excel + excel_file=/data/isis_data/excel/tekuci_FF.xls xml=Èasopis -[kk-arti] - isis_db=/data/isis_data/kk/ARTI/ARTI - type=isis - xml=Èlanak - - diff --git a/all2xml.pl b/all2xml.pl index 8fc8177..32381b3 100755 --- a/all2xml.pl +++ b/all2xml.pl @@ -45,16 +45,24 @@ sub x { return $utf2cp->convert($_[0]); } -# decode isis import codepage -my $isis2cp; +# decode isis/excel or other import codepage +my $import2cp; # outgoing xml must be in UTF-8 my $cp2utf = Text::Iconv->new($codepage,'UTF-8'); -sub isis2xml { +# mapping between data type and tag which specify +# format in XML file +my %type2tag = ( + 'isis' => 'isis', + 'excel' => 'column' +); + +sub data2xml { use xmlify; + my $type = shift @_; my $row = shift @_; my $add_xml = shift @_; @@ -87,12 +95,13 @@ sub isis2xml { my ($swish,$display); - foreach my $x (@{$config->{indexer}->{$field}->{isis}}) { + my $tag = $type2tag{$type} || die "can't find which tag to use for type $type"; + foreach my $x (@{$config->{indexer}->{$field}->{$tag}}) { my $format = x($x->{content}); my $delimiter = x($x->{delimiter}) || ' '; - my $isis_i = 0; # isis repeatable offset + my $repeat_off = 0; # repeatable offset my ($s,$d,$i) = (1,1,0); # swish, display default $s = 0 if (lc($x->{type}) eq "display"); @@ -109,9 +118,8 @@ sub isis2xml { # init vars so that we go into while... ($swish,$display) = (1,1); - while ($swish || $display) { - ($swish,$display) = parse_format($format,$row,$isis_i++,$isis2cp); - + if ($swish || $display) { + ($swish,$display) = parse_format($type, $format,$row,$repeat_off++,$import2cp); # filter="name" ; filter this field through # filter/[name].pm my $filter = $x->{filter}; @@ -211,60 +219,123 @@ sub isis2xml { ########################################################################## +# read configuration for this script my $cfg = new Config::IniFiles( -file => $config_file ); +# read global.conf configuration +my $cfg_global = new Config::IniFiles( -file => 'global.conf' ); + # open index $index = new index_DBI( - $cfg->val('global', 'dbi_dbd'), - $cfg->val('global', 'dbi_dsn'), - $cfg->val('global', 'dbi_user'), - $cfg->val('global', 'dbi_passwd') || '', + $cfg_global->val('global', 'dbi_dbd'), + $cfg_global->val('global', 'dbi_dsn'), + $cfg_global->val('global', 'dbi_user'), + $cfg_global->val('global', 'dbi_passwd') || '', ); -# delete [global] section to leave just databases sections -$cfg->DeleteSection('global'); - foreach my $database ($cfg->Sections) { - my $isis_db = $cfg -> val($database, 'isis_db') || die "$database doesn't have 'isis_db' defined!"; - my $type = $cfg -> val($database, 'type') || die "$database doesn't have 'type' defined"; + my $type = lc($cfg -> val($database, 'type')) || die "$database doesn't have 'type' defined"; my $add_xml = $cfg -> val($database, 'xml'); # optional - $config=XMLin("./import_xml/$type.xml", forcearray => [ 'isis' ], forcecontent => 1); +print STDERR "reading ./import_xml/$type.xml\n"; - $isis2cp = Text::Iconv->new($config->{isis_codepage},$codepage); + $config=XMLin("./import_xml/$type.xml", forcearray => [ $type2tag{$type} ], forcecontent => 1); - my $db = OpenIsis::open( $isis_db ); - if (0) { -# # FIX -# if (! $db ) { - print STDERR "WARNING: can't open '$isis_db'\n"; - next ; + # output current progress indicator + my $last_p = 0; + sub progress { + #return if (! $opts{q}); # FIXME + my $current = shift; + my $total = shift; + my $p = int($current * 100 / $total); + if ($p != $last_p) { + printf STDERR ("%5d / %5d [%-51s] %-2d %% \r",$current,$total,"=" x ($p/2).">", $p ); + $last_p = $p; + } } - my $max_rowid = OpenIsis::maxRowid( $db ); + # now read database +print STDERR "using: $type...\n"; - print STDERR "Reading database: $isis_db [$max_rowid rows]\n"; + if ($type eq "isis") { + my $isis_db = $cfg -> val($database, 'isis_db') || die "$database doesn't have 'isis_db' defined!"; - my $path = $database; # was $isis_db + $import2cp = Text::Iconv->new($config->{isis_codepage},$codepage); + my $db = OpenIsis::open( $isis_db ); - my $last_p = 0; + my $max_rowid = OpenIsis::maxRowid( $db ); + + print STDERR "Reading database: $isis_db [$max_rowid rows]\n"; + + my $path = $database; + + for (my $row_id = 1; $row_id <= $max_rowid; $row_id++ ) { + my $row = OpenIsis::read( $db, $row_id ); + if ($row && $row->{mfn}) { + + progress($row->{mfn}, $max_rowid); + + my $swishpath = $path."#".int($row->{mfn}); + + if (my $xml = data2xml($type,$row,$add_xml)) { + $xml = $cp2utf->convert($xml); + use bytes; # as opposed to chars + print "Path-Name: $swishpath\n"; + print "Content-Length: ".(length($xml)+1)."\n"; + print "Document-Type: XML\n\n$xml\n"; + } + } + } + print STDERR "\n"; + + } elsif ($type eq "excel") { + use Spreadsheet::ParseExcel; + use Spreadsheet::ParseExcel::Utility qw(int2col); + + $import2cp = Text::Iconv->new($config->{excel_codepage},$codepage); + my $excel_file = $cfg -> val($database, 'excel_file') || die "$database doesn't have 'excel_file' defined!"; -# { my $row_id = 4514; -# FIX - for (my $row_id = 1; $row_id <= $max_rowid; $row_id++ ) { - my $row = OpenIsis::read( $db, $row_id ); - if ($row && $row->{mfn}) { - # output current process indicator - my $p = int($row->{mfn} * 100 / $max_rowid); - if ($p != $last_p) { - printf STDERR ("%5d / %5d [%-51s] %-2d %% \r",$row->{mfn},$max_rowid,"=" x ($p/2).">", $p ) if (! $opts{q}); - $last_p = $p; + my $sheet = x($config->{sheet}) || die "no sheet in $type.xml"; + my $start_row = x($config->{start_row}) || die "no start_row in $type.xml"; + + my $oBook = Spreadsheet::ParseExcel::Workbook->Parse($excel_file) || die "can't open Excel file '$excel_file'"; + + my $sheet_nr = 0; + foreach my $oWks (@{$oBook->{Worksheet}}) { + #print STDERR "-- SHEET $sheet_nr:", $oWks->{Name}, "\n"; + last if ($oWks->{Name} eq $sheet); + $sheet_nr++; + } + + my $oWorksheet = $oBook->{Worksheet}[$sheet_nr]; + + print STDERR "using sheet: ",$oWorksheet->{Name},"\n"; + defined ($oWorksheet) || die "can't find sheet '$sheet' in $excel_file"; + my $end_row = x($config->{end_row}) || $oWorksheet->{MaxRow}; + + for(my $iR = $oWorksheet->{MinRow} ; defined $end_row && $iR <= $end_row ; $iR++) { + my $row; + for(my $iC = $oWorksheet->{MinCol} ; defined $oWorksheet->{MaxCol} && $iC <= $oWorksheet->{MaxCol} ; $iC++) { + my $cell = $oWorksheet->{Cells}[$iR][$iC]; + if ($cell) { + $row->{int2col($iC)} = $cell->Value; + } } - my $swishpath = $path."#".int($row->{mfn}); + progress($iR, $end_row); + +# print "row[$iR/$end_row] "; +# foreach (keys %{$row}) { +# print "$_: ",$row->{$_},"\t"; +# } +# print "\n"; + + my $swishpath = $database."#".$iR; + + next if (! $row); - if (my $xml = isis2xml($row,$add_xml)) { + if (my $xml = data2xml($type,$row,$add_xml)) { $xml = $cp2utf->convert($xml); use bytes; # as opposed to chars print "Path-Name: $swishpath\n"; @@ -273,7 +344,6 @@ foreach my $database ($cfg->Sections) { } } } - print STDERR "\n"; } # call this to commit index diff --git a/import_xml/excel.xml b/import_xml/excel.xml new file mode 100644 index 0000000..08617f9 --- /dev/null +++ b/import_xml/excel.xml @@ -0,0 +1,159 @@ + + + + + + + + |A| + + + + |B| + |B| + + + + + |B| + + + + + |A| + |A| + + + + + + |C| + + + + + + + + + + + diff --git a/parse_format.pm b/parse_format.pm index e92958a..5f5705a 100644 --- a/parse_format.pm +++ b/parse_format.pm @@ -1,15 +1,31 @@ #------------------------------------------------------------- # -# parse_format('format',$isis_row); +# parse_format(...) # -use isis_sf; sub parse_format { + my $type = shift || die "parset_format must be called with type!"; my $format = shift || die "parse_format must be called with format!"; my $row = shift || die "parse_format must be called with row!"; my $i = shift || 0; # isis repeatable number my $codepage = shift || die "parse_format must be called with codepage!"; + if ($type eq "isis") { + return parse_isis_format($format,$row,$i,$codepage); + } elsif ($type eq "excel") { + return parse_excel_format($format,$row,$i,$codepage); + } +} + +#------------------------------------------------------------- + +sub parse_isis_format { + use isis_sf; + + my $format = shift; + my $row = shift; + my $i = shift; + my $codepage = shift; my $out; my $out_swish; @@ -66,4 +82,49 @@ sub parse_format { } #------------------------------------------------------------- + +sub parse_excel_format { + my $format = shift; + my $row = shift; + my $i = shift; + my $codepage = shift; + + my $out; + my $out_swish; + + my $prefix = ""; + if ($format =~ s/^([^A-Z\|]{1,3})//) { + $prefix = $1; + } + + my $display; + my $swish; + + while ($format && length($format) > 0) { +#print STDERR "\n#### $format #"; + if ($format =~ s/^\|([A-Z]{1,2})\|//) { +#print STDERR "--$1-> $format -[",length($format),"] "; + if ($row->{$1}) { + my $tmp = $row->{$1}; + $tmp = $codepage->convert($tmp) if ($codepage); + $display .= $prefix . $tmp; + $swish .= $tmp." "; +#print STDERR " == $tmp"; + } + $prefix = ""; + } elsif ($format =~ s/^([^A-Z\|]+)(\|[A-Z]{1,2}\|)/$2/) { + $prefix .= $1 if ($display); + } else { + print STDERR "unparsed format: $format\n"; + $prefix .= $format; + $format = ""; + } +#print STDERR " display: $display swish: $swish [format: $format]"; + } + # add suffix + $display .= $prefix if ($display); + + return ($swish,$display); +} + 1; diff --git a/swish_isis.conf b/swish_isis.conf index 544261a..7f282d2 100644 --- a/swish_isis.conf +++ b/swish_isis.conf @@ -56,7 +56,8 @@ IndexAdmin "dpavlin@rot13.org" # Enable alternate search syntax. # This means a search query can contain ``+'' and ``-'' as syntax parameter. -EnableAltSearchSyntax yes +# this directive is removed in swish 2.4 +#EnableAltSearchSyntax yes # -- 2.20.1