From 58050b9349e0c7c378ad669747aeb900ccdae260 Mon Sep 17 00:00:00 2001 From: Mason James Date: Thu, 4 Jul 2013 14:21:08 +1200 Subject: [PATCH] Bug 10538 - Improve importation of .CSV framework files this patch is basically a rewrite of the _import_table_csv() sub, using Text::CSV to test this patch... 1/ dump a framework as a .csv file, to your disk 2/ open that .csv file in LibreOffice (or OpenOffice) and save that file as a new.csv note: LO/OO has just created a 'broken' new.csv file 3/ import new.csv file into Koha, (the import should FAIL) 4/ apply patch and repeat step 3, (the import should SUCCEED) Signed-off-by: Joy Nelson --- C4/ImportExportFramework.pm | 159 +++++++++++++++++++++++-------------------- 1 files changed, 84 insertions(+), 75 deletions(-) diff --git a/C4/ImportExportFramework.pm b/C4/ImportExportFramework.pm index f85101d..9522e83 100644 --- a/C4/ImportExportFramework.pm +++ b/C4/ImportExportFramework.pm @@ -23,11 +23,11 @@ use XML::LibXML; use XML::LibXML::XPathContext; use Digest::MD5 qw(); use POSIX qw(strftime); +use Text::CSV; use C4::Context; use C4::Debug; - use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); BEGIN { @@ -1127,96 +1127,105 @@ sub _processRows_Table return 1; }#_processRows_Table - - - # Import worksheet from the csv file to the mysql table -sub _import_table_csv -{ - my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_; +sub _import_table_csv { + + my ( $dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields ) + = @_; - my $row = ''; - my $partialRow = ''; - my $numFields = @$fields; + my $numFields = @$fields; my $fieldsNameRead = 0; my @arrData; - my ($fieldsStr, $dataStr, $updateStr); + my ( $fieldsStr, $dataStr, $updateStr ); my $db_scheme = C4::Context->config("db_scheme"); - my @fieldsPK = @$PKArray; + my @fieldsPK = @$PKArray; shift @fieldsPK; - my $ok = 0; - my $numRow = 0; + my $ok = 0; my $pos = 0; - while (<$dom>) { - $row = $_; - # Check whether the line has an unfinished field, i.e., a field with CR/LF in its data - if ($row =~ /,"[^"]*[\r\n]+$/ || $row =~ /^[^"]+[\r\n]+$/) { - $row =~ s/[\r\n]+$//; - $partialRow .= $row; - next; + + my $csv = Text::CSV->new( { binary => 1, eol => $/ } ); + + while ( my $row = $csv->getline($dom) ) { + + @arrData = @$row; + + # trim empty columns from row + my @a2 = reverse(@arrData); + my $idx = 0; + foreach my $i (@a2) { + last if $i; + $idx++; } - if ($partialRow) { - $row = $partialRow . $row; - $partialRow = ''; + + my $col_count = scalar @arrData - $idx; + @arrData = @arrData[ 0 .. $col_count - 1 ]; + + # skip glitchy rows + next unless scalar @arrData == scalar @$fields; + + if ( $arrData[0] eq '#-#' ) { + + # Change of table with separators #-# + return 1; + } + elsif ( $fieldsNameRead && $arrData[0] eq 'tagfield' ) { + + # Change of table because we begin with field name with former field names read + seek( $dom, $pos, 0 ); + + return 1; } - # Line OK, process it - if ($row =~ /(?:".*?",?)+/) { - @arrData = split('","', $row); - $arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/); - $arrData[$#arrData] =~ s/[\r\n]+$//; - chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/); - if (@arrData) { - if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') { - # Change of table with separators #-# - return 1; - } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') { - # Change of table because we begin with field name with former field names read - seek($dom, $pos, 0); - return 1; + if ( scalar(@$fields) == scalar(@arrData) ) { + if ( !$fieldsNameRead ) { + + # New table, we read the field names + $fieldsNameRead = 1; + for ( my $i = 0 ; $i < @arrData ; $i++ ) { + if ( $arrData[$i] ne $fields->[$i] ) { + $fieldsNameRead = 0; + last; + } } - if (scalar(@$fields) == scalar(@arrData)) { - if (!$fieldsNameRead) { - # New table, we read the field names - $fieldsNameRead = 1; - for (my $i=0; $i < @arrData; $i++) { - if ($arrData[$i] ne $fields->[$i]) { - $fieldsNameRead = 0; - last; - } - } - if ($fieldsNameRead) { - $fieldsStr = join(',', @$fields); - $dataStr = ''; - map { $dataStr .= '?,';} @$fields; - chop($dataStr) if ($dataStr); - $updateStr = ''; - map { $updateStr .= $_ . '=?,';} @$fields; - chop($updateStr) if ($updateStr); - } - } else { - # Read data - my $j = 0; - my %dataFields = (); - for (@arrData) { - if ($fields->[$j] eq 'frameworkcode' && $_ ne $frameworkcode) { - $dataFields{$fields->[$j]} = $frameworkcode; - $arrData[$j] = $frameworkcode; - } else { - $dataFields{$fields->[$j]} = $_; - } - $j++ - } - $ok = _processRow_DB($dbh, $db_scheme, $table, $fieldsStr, $dataStr, $updateStr, \@arrData, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete); + if ($fieldsNameRead) { + $fieldsStr = join( ',', @$fields ); + $dataStr = ''; + map { $dataStr .= '?,'; } @$fields; + chop($dataStr) if ($dataStr); + $updateStr = ''; + map { $updateStr .= $_ . '=?,'; } @$fields; + chop($updateStr) if ($updateStr); + } + } + else { + + # Read data + my $j = 0; + my %dataFields = (); + for (@arrData) { + if ( $fields->[$j] eq 'frameworkcode' + && $_ ne $frameworkcode ) + { + $dataFields{ $fields->[$j] } = $frameworkcode; + $arrData[$j] = $frameworkcode; + } + else { + $dataFields{ $fields->[$j] } = $_; } + $j++; } - $pos = tell($dom); + + $ok = _processRow_DB( + $dbh, $db_scheme, $table, + $fieldsStr, $dataStr, $updateStr, + \@arrData, \%dataFields, $PKArray, + \@fieldsPK, $fields2Delete + ); } - @arrData = (); } - $numRow++; + $pos = tell($dom); } return $ok; -}#_import_table_csv +} #_import_table_csv # Import worksheet from the ods content.xml file to the mysql table -- 1.7.2.5