in reply to Using Perl and WriteExcel to split data from one column to many others

G'day squarez,

Welcome to the monastery.

I can't actually find a module called WriteExcel on CPAN. There's a lot with WriteExcel in the name: I'm going to guess you're using Spreadsheet::WriteExcel.

This module has a method called write_row() whose documentation has:

"... This is useful for converting the results of a database query into an Excel worksheet. ..."

You should be able to modify your code to something like (untested): 

while (my @res = $sth->fetchrow_array()) {
    @res = map { defined ? $_ : '' } @res;
    $worksheet->write_row($i, 0, [@res]);
    $i++;
}

[download]

I also noted Excel::Writer::XLSX has a method with the same name which appears to work the same (from a coding perspective).

Even if you're using a different module, you may still find the technique applicable. If not, let us know what you are using.

-- Ken

Replies are listed 'Best First'.
Re^2: Using Perl and WriteExcel to split data from one column to many others
by squarez (Initiate) on Oct 03, 2012 at 01:20 UTC

    I apologize, I am using the Spreadsheet::WriteExcel module. As I mentioned to roboticus above, the problem is splitting the column data, not fetching and writing from an array. I need to take each entry from the data array, split up that string by "," delineation, then pass those values to the sequential columns. Think of that first column I am returning as a compressed view of a table all in one column.

    Thank you for the help :)

[reply]

      I think the confusion stems from (x,y,z,etc) which looks like an array; when, in fact, it's a string, i.e. '(x,y,z,etc)'.

      Assuming I've got that right, this piece of code shows how to split the data. I'm still not entirely sure what you mean by "There are also times when there is no data": I've added 3 additional tests with no data between commas, between parentheses and between single-quotes. 

      #!/usr/bin/env perl

use strict;
use warnings;

use Data::Dumper;

my $res_re = qr{ \A [(] ( .* ) [)] \z }x;

my @all_res = (
    ['(x,y,z,etc)', 'maybe other stuff'],
    ['(x,y,z,etc,,abc,,,def)', 'maybe other stuff'],
    ['()', 'maybe other stuff'],
    ['', 'maybe other stuff'],
);

for (@all_res) {
    my @res = @$_;
    print Dumper \@res;
    $res[0] = '()' unless $res[0];

    my $res_wanted = [ split /,/ => ($res[0] =~ $res_re)[0] ];
    $res_wanted = [ '' ] unless @$res_wanted;
    print Dumper $res_wanted;
    print '-' x 60, "\n";
}

      [download]

      Here's the output:

      $ pm_split_res_for_excel.pl
$VAR1 = [
          '(x,y,z,etc)',
          'maybe other stuff'
        ];
$VAR1 = [
          'x',
          'y',
          'z',
          'etc'
        ];
------------------------------------------------------------
$VAR1 = [
          '(x,y,z,etc,,abc,,,def)',
          'maybe other stuff'
        ];
$VAR1 = [
          'x',
          'y',
          'z',
          'etc',
          '',
          'abc',
          '',
          '',
          'def'
        ];
------------------------------------------------------------
$VAR1 = [
          '()',
          'maybe other stuff'
        ];
$VAR1 = [
          ''
        ];
------------------------------------------------------------
$VAR1 = [
          '',
          'maybe other stuff'
        ];
$VAR1 = [
          ''
        ];
------------------------------------------------------------

      [download]

      -- Ken

[reply]
[d/l]
[select]
        Thank you for all the help Ken, with your help I was able to find a solution. :)
[reply]

In Section Seekers of Perl Wisdom