Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Seekers of Perl Wisdom

( #479=superdoc: print w/replies, xml ) Need Help??

If you have a question on how to do something in Perl, or you need a Perl solution to an actual real-life problem, or you're unsure why something you've tried just isn't working... then this section is the place to ask. Post a new question!

However, you might consider asking in the chatterbox first (if you're a registered user). The response time tends to be quicker, and if it turns out that the problem/solutions are too much for the cb to handle, the kind monks will be sure to direct you here.

User Questions
How can one merge multiple excel files sheet-wise?
1 direct reply — Read more / Contribute
by supriyoch_2008
on Mar 29, 2020 at 09:45

    Hi Perlmonks,

    I am interested in merging multiple (3) excel files sheet-wise i.e. cereal.xls, pulse.xls and fruit.xls,each having two sheets i.e. sheet1 and sheet2, into a single outputfile like result.xls. The sheet1 of result file must contain the contents of sheet1 of 3 input files in sequential rows. Similarly, the sheet2 of result file must contain the contents of sheet2 of 3 input files. While searching the web I have come across a program, given below, to merge excel files but the result file obtained from this program contains the values of sheet1 of 3 input files as sheet1, sheet2 and sheet3; rather the contents of sheet1, sheet2 and sheet3 should be in sheet1 of result.xls. I am using a Windows 7 64-bit pc and comfortable with .xls and not .xlsx. I have tried to get the desired output but failed. I request Perlmonks to suggest me how to sort out this problem.

    #!/usr/bin/perl use warnings; use strict; use Spreadsheet::ParseExcel; use Spreadsheet::WriteExcel; my $parser = Spreadsheet::ParseExcel->new(); #Creating Object to Read +Worksheet my @files = qw/ cereal.xls pulse.xls fruit.xls /; my $write_workbook = new Spreadsheet::WriteExcel('Result.xls'); my $n = 1; foreach my $file (@files) { my $parse_workbook = $parser->parse($file); my $write_worksheet = $write_workbook->add_worksheet("Sheet$n"); if ( !defined $parse_workbook) { die $parser->error(), ".\n"; } for my $parse_worksheet ( $parse_workbook->worksheets() ) { my ( $row_min, $row_max ) = $parse_worksheet->row_range(); my ( $col_min, $col_max ) = $parse_worksheet->col_range(); for my $row ( $row_min .. $row_max ) { for my $col ( $col_min .. $col_max ) { my $cell = $parse_worksheet->get_cell( $row, $col ); next unless $cell; my $cell_value = $cell->value(); my $cell_format = $cell->get_format(); $write_worksheet->write($row, $col, $cell_value); } } } $n++; } print "\n\n ####################################### Result produced\n #######################################\n"; exit;
    The contents of cereal.xls are as follows: Sheet1 No. Name Sugar (g/100g) 1 Wheat 0.3 2 Maize 7.7 3 Rice 0.1 Sheet2 No. Name Protein (g/100g) 1 Wheat 10 2 Maize 7 3 Rice 2.6
    The contents of pulse.xls are as follows: Sheet1 No. Name Sugar (g/100g) 1 Pea 6 2 Chickpea 11 3 Pigeonpea 1.8 Sheet2 No. Name Protein (g/100g) 1 Pea 5 2 Chickpea 8.4 3 Pigeonpea 7.6
    The contents of fruit.xls are as follows: Sheet1 No. Name Sugar (g/100g) 1 Apple 10 2 Pear 10 3 Pineapple 10 Sheet2 No. Name Protein (g/100g) 1 Apple 0.3 2 Pear 0.4 3 Pineapple 0.5
    The desired result.xls file should look like: Sheet1 No. Name Sugar (g/100g) 1 Wheat 0.3 2 Maize 7.7 3 Rice 0.1 1 Pea 6 2 Chickpea 11 3 Pigeonpea 1.8 1 Apple 10 2 Pear 10 3 Pineapple 10
    Sheet2 No. Name Protein (g/100g) 1 Wheat 10 2 Maize 7 3 Rice 2.6 1 Pea 5 2 Chickpea 8.4 3 Pigeonpea 7.6 1 Apple 0.3 2 Pear 0.4 3 Pineapple 0.5
CGI MySQL insert/update special characters
1 direct reply — Read more / Contribute
by Takamoto
on Mar 28, 2020 at 11:13

    Hello monks

    This task should be trivial...but when I insert/update a record in my MySQL database with a value containing special characters, the special characters are not saved. No special message. Why?

    #shortened script #getting pars with CGI my $q = CGI->new(); my $LicenseKey = $q->param('key'); my $UserName = $q->param('user'); my $UserPW = $q->param('pw'); my $dbhServer = DBI->connect("DBI:mysql:$db:$host", $user, $pass); my $sth = $dbhServer->prepare("INSERT INTO Accounts VALUES (?, ?, ?, ? +, ?)"); $sth->execute(undef, $LicenseKey, $UserName, $UserPW, $date) || print +("ERROR EXECUTION: " . $dbhServer->errstr); #same with updating my $sth = $dbhServer->prepare('update Accounts set UserName=?, Passwor +d=? WHERE LicenseKey=?'); $sth->execute($UserName, $UserPW, $LicenseKey)|| print ("ERROR EXECUTI +ON: " . $dbhServer->errstr); $dbhServer->disconnect;
use a variable in ldapsearch filter
2 direct replies — Read more / Contribute
by Cornichon
on Mar 27, 2020 at 19:50

    Hello everyone,

    I'm writing a script that receives as a parameter the name of a user and performs a search in LDAP but I can't find a way to use a Perl variable in the LDAP search filter.

    The code used for the search is this one:

    $value = $ldap->search( base => 'dc=xxdom,dc=xxdc', scope => 'sub', filter => '(sn="xx")', );

    Let's say the variable $name contains the name I need to do the search.

    I tried this: filter => '(sn = $name)'.

    It's not working, does anyone have a solution for me?

Perl command line switches
4 direct replies — Read more / Contribute
by Irakro1997
on Mar 26, 2020 at 22:27
    Hi I recently started using Perl scripting again after a very long time so I inherited a scile file that I am playing with to get to work while trying to understand it I just recalled a useful command but I canít remember the name nor the exact usage so thought I would ask ...if you have inherited a Perl script that you quickly want to understand which arguments to pass on command line to generate out put files there is a command that you can use on the Perl script to get help on how to run that file or which arguments to pass to the script while running .... if that makes sense would someone here know these until commands ? Thanks
Oracle::DBD to 12c server
3 direct replies — Read more / Contribute
by jerryhone
on Mar 26, 2020 at 11:46
    We have an old application running on Solaris using Oracle:DBD that happily connects to an Oracle 11g server! $DBD::Oracle::VERSION = '1.14'; When trying to connect to a new 12c server we're encountering ORA-28040: No matching authentication protocol (DBD ERROR: OCISessionBegin) Google implies that we need to change sqlnet.allowed_logon_version on the server, but our DBA tells us that's not possible on our new dataserver setup! So, question is...will a newer version of Oracle::DBD solve our issues, and if so, what version do we need? If not, any other ideas?
Multidimensional arrays
3 direct replies — Read more / Contribute
by baxy77bax
on Mar 26, 2020 at 11:13
    Hi

    Let say I have a multidimensional array and every time the number of dimensions is different. I don't have apriori information about the number of dimensions thus i need to recursively access each and figure out if there is more depth or not. But I also need to copy this array into another and modify it (let say increment). Curently I am trying to do it like this:

    sub new { my ($class) = @_; my $self->{_mtx_} = []; bless $self, $class; return $self; } sub copycomp_matrix { my ($self, $arg) = @_; $self->_recurse($arg,$self->{_mtx_}); print Dumper($self->{_mtx_}); } sub _recurse { my ($self, $in, $out) = @_; for (my $i = 0;$i<@{$in};$i++){ if (ref $in->[$i] eq 'ARRAY'){ $self->_recurse($in->[$i], $out->[$i]); }else{ if (!$out->[$i]){ $out->[$i] = $in->[$i] ; }else{ $out->[$i] = $in->[$i] +1 ; } } } } ######################################################### use strict; use lib "./"; use Above::Obj; my $z = Above::Obj->new(); my $a = [[1,2],[2,3]]; $z->copycomp_matrix($a);
    but I keep getting  $VAR1 = []; What am i missing ?? Is there a better way to do this ? thnx
Get Excel column name for given decimal number
3 direct replies — Read more / Contribute
by Akshit
on Mar 26, 2020 at 09:00

    Hello Monks..!!

    Hope all doing good :)

    I am trying to Get Excel column name for given decimal number.

    my code able to obtion from A..ZZ. Once it reaches column AAA its not working. I have to update logic but I am running out of ideas.

    Also I felt my code is very long. So any ideas to improve my code is appreciated.

    Here is my code:

    .
    $colname = GetExcelColumnName(703); print "colname is : $colname \n"; sub GetExcelColumnName { $columnNumber = $_[0]; $columnnamestr = ""; print "column number is : $columnNumber \n"; $columnNumber = $columnNumber; $div = $columnNumber/26; my $divstart = 0; if($columnNumber%26 == 0) { $div = $div - 1; $divstart = 1; } $divcnt = 0; if($div >= 1) { $divcnt = $columnNumber/26; } if(($divcnt == 0)) { $columnname = 65-1+$columnNumber; $return_str = chr($columnname); } else { if($columnNumber == 26) { $columnname = 65+$div; } else{ $columnname = 65-1+$div; } $return_str = chr($columnname); $mod = $columnNumber%26; if($columnname <= 90) { if(($return_str != 'Z') && ($mod == 0)) { $columnname = 65; } elsif(($return_str == 'Z') && ($mod == 0)) { $columnname = 90; } else{ $columnname = 65-1+$mod; } $return_str = $return_str.chr($columnname); } else { $return_str = "update logic \n"; } } return $return_str; }
$VERSION in module files
4 direct replies — Read more / Contribute
by bliako
on Mar 26, 2020 at 06:09

    Hello Monks,

    I wonder what is the good practice with the $VERSION string in module files.

    I assumed that each module file should have an our $VERSION = '1.2.3'; in it. Please correct me if this is not the correct format.

    I also assumed that my Makefile maker ExtUtils::MakeMaker reads version from just one of those files using the key VERSION_FROM in its config.

    I also assumed that the Version in the pod section of each module file must be set manually and cannot be read automatically from the $VERSION of that file.

    My question is how do I go about and changing all those $VERSION strings and pod Version strings automatically when I create a new version of this module. I can create a script to change $VERSION and, with some risk, pod Versions but I wonder if there is a better way.

    bw, bliako

Query of multi dimentional array
4 direct replies — Read more / Contribute
by shabird
on Mar 26, 2020 at 05:51

    Hello Monks, hope you all are fine

    I am struggling with multi dimensional array this time so basically the question goes like this, I have a file which has a data like this

    GeneID Tp1 Tp2 Tp3 ALA1 10 12 11 THR8 57 99 12 HUA4 100 177 199 ABA5 2 5 10

    So i have read the file and assigned it to multi dimensional array with the help of this code.

    my @content = (<FH>); close(FH); my $no_of_seq = scalar(@content); my @myArray; foreach my $row (@content) { my @columns = split(/\s+/,$row); push(@myArray,\@columns); # print "@columns"; } for($row = 0; $row < $no_of_seq; $row++){ for($col = 0; $col < $no_of_seq; $col++){ print($myArray[$row][$col], "\t"); } print("\n"); }

    so this code prints every element in the same order but now i want to calculate the sum of numbers in each row and print that sum next to gene name i.e sum of values for ALA1 is 33 and sum of values for THR8 is 168 and so on. Now i have tried something like this.

    for($row = 0; $row < $no_of_seq; $row++){ for($col = 0; $col < $no_of_seq; $col++){ $sum = $myArray[$row][$col] + $myArray[$row]; print($sum, "\t"); }

    But this is printing something like this

    140526414289424 140526414289424 140526414289424 1405264142894 +24 140526414289424 140526414212840 140526414212850 140526414212852 1405264142128 +51 140526414212840 140526414213296 140526414213353 140526414213395 1405264142133 +08 140526414213296 140526414288368 140526414288468 140526414288545 1405264142885 +67 140526414288368 140526414313944 140526414313946 140526414313949 1405264143139 +54 140526414313944

    How can i achieve my desired result? thank you in advance

Error while Installing NetSNMP::OID
1 direct reply — Read more / Contribute
by vikasgurlinka
on Mar 25, 2020 at 22:24
    OS : RHEL 7.7 (AWS)

    when I try to Install Perl module NetSNMP::OID have the below error

    Could not find a typemap for C type 'netsnmp_oid *'

    [root@ip-10-1-1-75 NetSNMP-OID-5.0404]# perl Makefile.PL Generating a +Unix-style Makefile Writing Makefile for NetSNMP::OID Writing MYMETA. +yml and MYMETA.json [root@ip-10-1-1-75 NetSNMP-OID-5.0404]# make Skip blib/lib/NetSNMP/OID.pm (unchanged) Running Mkbootstrap for OID () chmod 644 "OID.bs" "/usr/bin/perl" -MExtUtils::Command::MM -e 'cp_nonempty' -- OID.bs bli +b/arch/auto/NetSNMP/OID/OID.bs 644 "/usr/bin/perl" "/usr/share/perl5/vendor_perl/ExtUtils/xsubpp" -protot +ypes -typemap '/usr/share/perl5/ExtUtils/typemap' OID.xs > OID.xsc Could not find a typemap for C type 'netsnmp_oid *'. The following C types are mapped by the current typemap: 'AV *', 'Bool +ean', 'CV *', 'FILE *', 'FileHandle', 'HV *', 'I16', 'I32', 'I8', 'IV +', 'InOutStream', 'InputStream', 'NV', 'OutputStream', 'PerlIO *', 'R +esult', 'STRLEN', 'SV *', 'SVREF', 'SysRet', 'SysRetLong', 'Time_t *' +, 'U16', 'U32', 'U8', 'UV', 'bool', 'bool_t', 'caddr_t', 'char', 'cha +r *', 'char **', 'const char *', 'double', 'float', 'int', 'long', 's +hort', 'size_t', 'ssize_t', 'time_t', 'unsigned', 'unsigned char', 'u +nsigned char *', 'unsigned int', 'unsigned long', 'unsigned long *', +'unsigned short', 'void *', 'wchar_t', 'wchar_t *' in OID.xs, line 19 +2 Could not find a typemap for C type 'netsnmp_oid *'. The following C types are mapped by the current typemap: 'AV *', 'Bool +ean', 'CV *', 'FILE *', 'FileHandle', 'HV *', 'I16', 'I32', 'I8', 'IV +', 'InOutStream', 'InputStream', 'NV', 'OutputStream', 'PerlIO *', 'R +esult', 'STRLEN', 'SV *', 'SVREF', 'SysRet', 'SysRetLong', 'Time_t *' +, 'U16', 'U32', 'U8', 'UV', 'bool', 'bool_t', 'caddr_t', 'char', 'cha +r *', 'char **', 'const char *', 'double', 'float', 'int', 'long', 's +hort', 'size_t', 'ssize_t', 'time_t', 'unsigned', 'unsigned char', 'u +nsigned char *', 'unsigned int', 'unsigned long', 'unsigned long *', +'unsigned short', 'void *', 'wchar_t', 'wchar_t *' in OID.xs, line 20 +9 make: *** [OID.c] Error 1 [root@ip-10-1-1-75 NetSNMP-OID-5.0404]#

Add your question
Title:
Your question:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others exploiting the Monastery: (5)
    As of 2020-03-29 17:13 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?
      To "Disagree to disagree" means to:









      Results (171 votes). Check out past polls.

      Notices?