I've thrown this query at the mailing list before but I'm still stuck I'm afraid.
I have an Excel file with a worksheet that looks like:
A B C D ----------------------------------------------------- 1¦ STUD_ID GEOG MATH 2¦ 1 44 99 3¦ 2 55 70 4¦ 3 66 55
Row 1 is the header row from which the subjects must be extracted to do an update of the result for each student. This would be an example update statement for student 1 :
UPDATE TABLE RESULTS SET RESULT=44 WHERE STUDENT_ID=1 AND SUBJECT=GEOG
All my work has been done using Excel files but I'm prepared to move to CSV format to get this thing working if anybody could better assist me in that format.
I'm going to include my code so far which is based on the Spreadsheet::ParseExcel module. It's embarrassingly bad because I don't know how to isolate out the Subject fields from the header so I'm constructing and then de constructing a hash of all the elaments needed for an update:
############### COLUMNS ####################### (Captures the Subjects)
for(my $iR = $oWkS->{MinRow} ; defined $oWkS->{MaxRow} && $iR <= 0 ; $iR++)
{for(my $iC = $oWkS->{MinCol} + 1 ; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++)
{$oWkC = $oWkS->{Cells}[$iR][$iC];
push(@columns,$oWkC->Value);
}
}
############# ROWS ############## (Captures the Student_IDs)
for(my $iC = $oWkS->{MinCol} ; defined $oWkS->{MaxCol} && $iC <= 0 ; $iC++)
{for(my $iR = $oWkS->{MinRow} + 1 ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++)
{$oWkC = $oWkS->{Cells}[$iR][$iC];
push(@rows,$oWkC->Value);
}
}
# ############# DATA ############## (Captures the Results)
for(my $iR = $oWkS->{MinRow} + 1 ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++)
{for(my $iC = $oWkS->{MinCol} + 1 ; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++)
{$oWkC = $oWkS->{Cells}[$iR][$iC];
push(@data,$oWkC->Value);
}
}
# ########## TRAVERSE OUR ARRAYS ######### (Combines the elements from above into one hash)
foreach $row (@rows) {
foreach $column (@columns) {
push(@keys,$row.",".$column);
}
}
}
my %combined = map { $keys[$_], $data[$_] } 0 .. $#data;while(($key,$data) = each(%combined)) {
$student = substr $key, 0, 8;
$course = substr $key, 9, 30;
print "\n\n", "STUDENT: ", $student , " COURSE: ", $course , " MARK: ", $data ," QUAL: ", $qual ,"
YEAR: ", $year ," EX_MONTH: ", $ex_month ," ST_PERIOD: ", $st_period , "\n\n\n";
$sth->execute($data,$qual,$column,$year,$student,$ex_month,$st_period) or die $DBI::errstr\n";
}
The final print statement does show the correct values getting assigned by student by subject but $sth (the update query) is not updating the results field (though no error is returned)
Many thanks,
Mark
