dirtdog has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks

I'm having a very hard time trying to get some results from a sql statement using WriteExcel module into a Hash.

The results from my sql statement yield 2 columns: Client is column 1 and Month followed by total orders for that month as column 2

for example,

Client|Counts by Month

IBM | February 1 March 5 July 4

Oracle| January 3 March 4 April 6 May 5

RedHat | March 2 June 3 August 1

The first result would be in @row so @row[0] would be equal to IBM and @row1 would be equal to 'February 1 March 5 July 4'.

Somehow I need to split the values of @row1 into a hash so the Key->value pair would be February->1, March->5, July->4, but that is where I'm getting stuck.

Any help would be greatly appreciated!

  • Comment on How to stuff space separated column into Hash

Replies are listed 'Best First'.
Re: How to stuff space separated column into Hash
by choroba (Cardinal) on Aug 29, 2016 at 18:29 UTC
    By splitting the string, you get a list with a month, number, month, number, and so on. You can create a hash from this list directly, odd elements become keys and even elements become values: 
    #!/usr/bin/perl
use warnings;
use strict;

my @row = ( 'IBM', 'February 1 March 5 July 4' );
my %by_month = split ' ', $row[1];

use Data::Dumper;
print Dumper(\%by_month);

    [download]

    ($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,

    [download]
[reply]
[d/l]
[select]
Re: How to stuff space separated column into Hash -- oneliner
by Discipulus (Canon) on Aug 29, 2016 at 20:44 UTC
    You already had good and wise answers but if you need to surprise or just want to learn more, Perl can handle this situation in few keystroke (see perlrun for details); given the following inputtable.txt file: 
    Client|Counts by Month
IBM | February 1 March 5 July 4
Oracle| January 3 March 4 April 6 May 5
RedHat | March 2 June 3 August 1

    [download]

    you can tame it at your will with: 

    # warning windows double quotes!
perl -MData::Dump -F"\s*\|\s*|\s" -lane "next if $.==1;$r{$F[0]}={@F[1
+..$#F]};END{dd %r}" inputtable.txt

(
  "IBM",
  { February => 1, July => 4, March => 5 },
  "Oracle",
  { April => 6, January => 3, March => 4, May => 5 },
  "RedHat",
  { August => 1, June => 3, March => 2 },
)

    [download]

    or if you prefere the core module Data::Dumper you'll end with a longer version: 

    perl -MData::Dumper -F"\s*\|\s*|\s" -lane "next if $.==1;$r{$F[0]}={@F
+[1..$#F]};END{print Dumper \%r}" i
nputtable.txt
$VAR1 = {
          'IBM' => {
                     'February' => '1',
                     'July' => '4',
                     'March' => '5'
                   },
          'Oracle' => {
                        'March' => '4',
                        'May' => '5',
                        'January' => '3',
                        'April' => '6'
                      },
          'RedHat' => {
                        'June' => '3',
                        'March' => '2',
                        'August' => '1'
                      }
        };

    [download]

    L*

    There are no rules, there are no thumbs..
    Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.
[reply]
[d/l]
[select]
Re: How to stuff space separated column into Hash
by GotToBTru (Prior) on Aug 29, 2016 at 18:44 UTC

    Some time spent in Tutorials might be beneficial. You would find this, for instance.

    But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)
[reply]
Re: How to stuff space separated column into Hash
by Marshall (Canon) on Aug 29, 2016 at 20:00 UTC
    choroba nailed this one++. I thought that I'd show you that this can work both ways, array=>hash and hash=>array.

    #!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;

my $string = 'February 1 March 5 July 4';
my @result_of_split =  split ' ', $string;
print Dumper \@result_of_split;

=prints: (@result_of_split)
$VAR1 = [
          'February',
          '1',
          'March',
          '5',
          'July',
          '4'
        ];
=cut        

my %hash = @result_of_split; #assign array to hash
print Dumper \%hash;

=prints: (%hash)
$VAR1 = {
          'February' => '1',
          'July' => '4',
          'March' => '5'
        };
=cut        

my @array = %hash;    #assign hash back to an array
print Dumper \@array;

=prints: (@array) back again
$VAR1 = [
          'February',
          '1',
          'March',
          '5',
          'July',
          '4'
        ];
=cut

    [download]
    When assigning an array to a hash, you have to have an even number of elements, 2,4,6,8, etc. If warnings is enabled (and should be), you will get a warning "odd number of ...." if @array contains 3,5, etc number of elements.
[reply]
[d/l]

      thanks marshall

      Ideally, I'd like to use a Hash of Hashes and I have the following code 

      while ( my @row = $sth->fetchrow_array() ) {
                print ">@row<\n";

        ($key, $value) = split ' ', $row[1];
        $HoH{$row[0]}{$key} = $value;
        
}

while ( ($client, $months) = each %HoH ) {
    print "$client: ";
    while ( ($month, $count) = each %$months ) {
        print "$month=$count ";
    }
    print "\n";
}

      [download]

      the problem is the split to get the $key and $value only gets the first key->value pair. Is there a way to get all of the key-value pairs into the HoH?

      Thanks!

[reply]
[d/l]

        I think I'm all set 

        while ( my @row = $sth->fetchrow_array() ) {
                print ">@row<\n";

        my %by_month = split ' ', $row[1];
        $HoH{$row[0]} = \%by_month;

        [download]

        This accomplishes my objective. Thanks again.

[reply]
[d/l]

Back to Seekers of Perl Wisdom