5 update_stock_props.pl - updates stock props
9 update_stock_props -H [database host] -D [database name] update_stock_prop_file.xlsx
16 update_stock_prop_file.xlsx: a file with three columns:
21 The script will remove the _new and _old extension, compare if they are the same, and then start replacing the values in the old column with the values in the new column for each accession_name.
25 Lukas Mueller <lam87@cornell.edu.
32 use Bio
::Chado
::Schema
;
34 use SGN
::Model
::Cvterm
;
35 use CXGN
::DB
::InsertDBH
;
36 use Spreadsheet
::ParseExcel
;
37 use Spreadsheet
::ParseXLSX
;
49 my $dbh = CXGN
::DB
::InsertDBH
->new( { dbhost
=>"$dbhost",
51 dbargs
=> {AutoCommit
=> 1,
58 my $schema= Bio
::Chado
::Schema
->connect( sub { $dbh->get_actual_dbh() });
60 my $formula_cvterm = $schema->resultset("Cv::Cvterm")->create_with({
62 cv
=> "cvterm_property",
65 my $type_id = $formula_cvterm->cvterm_id();
68 # Match a dot, extension .xls / .xlsx
69 my ($extension) = $file =~ /(\.[^.]+)$/;
72 if ($extension eq '.xlsx') {
73 $parser = Spreadsheet
::ParseXLSX
->new();
76 $parser = Spreadsheet
::ParseExcel
->new();
79 #try to open the excel file and report any errors
80 my $excel_obj = $parser->parse($file);
83 die "Input file error: ".$parser->error()."\n";
86 my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
87 my ( $row_min, $row_max ) = $worksheet->row_range();
88 my ( $col_min, $col_max ) = $worksheet->col_range();
90 if (($col_max - $col_min) < 1 || ($row_max - $row_min) < 1 ) { #must have header and at least one row of phenotypes
91 die "Input file error: spreadsheet is missing header\n";
96 my ($accession_name, $cvterm_old, $cvterm_new);
98 if ($worksheet->get_cell(0,0)) {
99 $accession_name = $worksheet->get_cell(0,0)->value();
101 if ($worksheet->get_cell(0,1)) {
102 $cvterm_old = $worksheet->get_cell(0,1)->value();
103 $cvterm_old =~ s/(.*)\_old/$1/g;
104 if (! $cvterm_old) { die "cvterm needs to be cvterm with _old extension in old column"; }
107 if ($worksheet->get_cell(0,2)) {
108 $cvterm_new = $worksheet->get_cell(0,2)->value();
109 $cvterm_new =~ s/(.*)\_new/$1/g;
110 if (! $cvterm_new) { die "cvterm needs to be cvterm with _new extension in old column"; }
113 if ($cvterm_new ne $cvterm_old) {
114 die "cvterm_new must be the same as cvterm_old without the extension, currently $cvterm_new vs $cvterm_old";
117 print STDERR
"Working with stock property $cvterm_new\n";
118 my $cvterm_id = SGN
::Model
::Cvterm
->get_cvterm_row($schema, $cvterm_new, 'stock_property')->cvterm_id();
122 my ($cvterm_new_value, $cvterm_old_value);
124 for (my $n=1; $n<$row_max; $n++) {
126 if ($worksheet->get_cell($n,0)) {
127 $accession_name = $worksheet->get_cell($n,0)->value();
129 if ($worksheet->get_cell($n,1)) {
130 $cvterm_old_value = $worksheet->get_cell($n,1)->value();
132 if ($worksheet->get_cell($n,2)) {
133 $cvterm_new_value = $worksheet->get_cell($n,2)->value();
136 my $accession = $schema->resultset("Stock::Stock")->find( { uniquename
=> $accession_name } );
137 if (!$accession) { die "Accession $accession does not exist. Please fix and try again."; }
139 my $current_entry = $schema->resultset("Stock::Stockprop")->find( { value
=> $cvterm_old_value, stock_id
=> $accession->stock_id(), type_id
=> $cvterm_id });
141 if (! $current_entry) {
142 die "The accession $accession_name does not have a value of $cvterm_old_value of type $cvterm_new. Please fix and try again";
146 $current_entry->update(
148 value
=> $cvterm_new_value
154 print STDERR
"Done.\n";