Array of Hashes -> XLS / CSV 1

[1DMF]

Hi,

I need to convert an array of hashes into XLS or basically CSV format.

Is there an easy way to do this with a pre-written module?

I was thinking i could do a loop over the array, using the 'keys' operator and a flag to only produce a row with the keys (column headings) once, then the data.

Should I simply just handroll it as described or can I save some time with a pre-written module?

Many thanks

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[PaulTEG]

Check out CPAN, look for Spreadsheet::WriteExcel

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[1DMF]

Thanks for the info Paul, but I cannot see anywhere in that module a simple way of passing it an array of hashes and it produce a simple CSV file like a record set.

I've decided to handroll it.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

for those interested , here's what I did for my purpose...

# set Vars
    my $i = 0;
    my $head;
    my $xls;
    my $time = time;

    # Loop Array of Hashes

    for(@logs){
        $i++;
        my $row;

        # Loop Hash and build Header + Row info 
        while (my ($key, $value) = each(%$_)){

            if($i == 1){                
                $head .= "\"$key\",";
            }   
                    
            $row .= "\"$value\",";
        }
    
        # drop extra commas + add newline char
        $row = substr($row,0,length($row)-1);
        $row .= "\n";

        # build XLS data
        $xls .= $row;                
    } 

    # drop extra comma and + XLS data to Header
    $head = substr($xls,0,length($xls)-1);
    $head .= "\n$xls";

    # Build file string 
    my $file = "c:/YOUR_FILE_PATH/$time.csv";

    #create file     
    open(CATAL, ">$file"); 
    flock(CATAL, 2);
    binmode(CATAL);
    truncate(CATAL, length($head));
    seek(CATAL, 0, 0);
    print CATAL "$head";
    close(CATAL);

bit crude , bit basic and I don't handle superlous extra double quotes (as I know the data doesn't have any).

But it's a basis for others should they need to do a similar task.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

dang when is this site going to get an edit facility!!!

Any one spot the stupid copy/paste error...

    # drop extra comma and + XLS data to Header
    [b]$head = substr($xls,0,length($xls)-1);[/b]
    $head .= "\n$xls";

should be....

    # drop extra comma and + XLS data to Header
    $head = substr([b]$head[/b],0,length([b]$head[/b])-1);
    $head .= "\n$xls";

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

ok due to the fact I have just found that you cannot predict the order of the hash key when using a foreach keys, I have had to change the code.

This is only required in place of the above code if the column order in the output XLS is required to be specific.

# set Vars
my $head;
my $xls;
my $time = time;
my $vars = "field1,field2,field3,field4,field5,field6,field7,field8,field9";
my @head = split(/\,/,$vars);

      for(@logs){
        my $row;
        foreach my $col(@head){
            my $value = $_->{$col};

            if($col eq "Used"){
                my @tm = split(/\./,$value);
                $value = $tm[0];
            }                    

            $row .= "\"$value\",";
        }
    
        # drop extra commas + add newline char
        $row = substr($row,0,length($row)-1);
        $row .= "\n";
        $xls .= $row;                
      } 


    # create column headings
    for(@head){
       $head .= "\"$_\",";
    }
   
    # drop extra comma and + XLS data to Header
    $head = substr($head,0,length($head)-1);
    $head .= "\n$xls";

    # Build file string 
    my $file = "c:/YOUR_FILE_PATH/$time.csv";

    #create XLS file     
    open(CATAL, ">$file"); 
    flock(CATAL, 2);
    binmode(CATAL);
    truncate(CATAL, length($head));
    seek(CATAL, 0, 0);
    print CATAL "$head";
    close(CATAL);

That way although the Array of Hashes (@logs) has the hash keys in a random order, you use the holding var $var to set order, split into an array and loop.

Thanks goes to Prex1 for his insight and keys/column holding array suggestion!

NOTE:

if($col eq "Used"){
   my @tm = split(/\./,$value);
   $value = $tm[0];
}

This is only in the code because the data in the field 'Used', is a date/time field and so i'm basically removing the '.000' time precision from the data.


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!