Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

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

by squarez (Initiate)
on Oct 02, 2012 at 23:12 UTC ( #996936=perlquestion: print w/replies, xml ) Need Help??
squarez has asked for the wisdom of the Perl Monks concerning the following question:

Solution Found! Thank you everyone who helped, here is my final part of the script that formats XLS:

... use strict; use warnings; use DBI; use POSIX; use File::Basename; use Net::SFTP::Foreign; use Text::CSV::Simple; use Spreadsheet::WriteExcel; ... # Generate XLS from array: my $strExcelFilename = "filename.xls"; my $workbook = Spreadsheet::WriteExcel->new($strExcelFilename); my $worksheet = $workbook->addworksheet("worksheetname"); my $intStartRow = 1; my $i = $intStartRow; my $intQueryArray = 0; my $o = $intQueryArray; my $intStartCol = 0; my $u = $intStartCol; $sth = execute_query("QUERY THAT RETURNS DATA FOR MULTIPLE COLUMNS IN +ONE COLUMN"); my %header = ( -bold => 1, -italic => 1, -text_wrap => 1, -border => 1, ); my $format = $workbook->add_format(%header); $worksheet->write('A1', "something", $format); $worksheet->write('B1', "something", $format); $worksheet->write('C1', "something", $format); $worksheet->write('D1', "something", $format); $worksheet->write('E1', "something", $format); $worksheet->write('F1', "something", $format); $worksheet->write('G1', "something", $format); # Format and split strings from query into their corresponding columns my @res; while ($res[$o] = $sth->fetchrow_array()) { my $set = $res[$o]; $set =~ s/[\(\)]//g; my @values = split(',', $set); foreach my $value (@values) { $worksheet->write($i, $u, $value); $u++ } $u = 0; $i++; $o++; }

Original Post

Hello all, I am pretty new to Perl but have coded in other languages and I have a problem. I have a data set coming back from a query that has all of the tables data in the first column. Each row in the first column has data like this:

(x,y,z,etc) (x,y,z,etc) (x,y,z,etc)

I need to split that data across to corresponding columns. Using "," as the delimiter between data, and removing the "()" wrapped around the results, producing something like this:

COL -> A B C D E F G ROWS-> 1 x y z . . . . 2 x y z . . . . 3 x y z . . . . 4 x y z . . . .

My code first puts the returnset from the query into an object $sth, sets the column headers, and formats them. Then I use:

... while (my @res = $sth->fetchrow_array()) { $worksheet->write($i, 0, $res[0]); $i++; } ...

This populates my excel file's first column with the data. I need help on how to place each (x,y,z,etc) into their own columns. There are also times when there is no data and a null or blank needs to be inserted. Can I do this when I am originally populating through my array while loop? Or should I populate like have already done then add in new code to split the data across the rows? I have done some searching online and found something similar to what I need:

... $/ = ''; my %list; while ( my $block = <DATA> ) { my ($tag, @list) = split /\n/, $block; s/^-- // for @list; $list{$tag} = \@list; } my $row = 0; foreach my $tag ( @{ $list{'main'} } ) { if ( exists $list{$tag} ) { $worksheet->write($row, 0, $tag); $worksheet->write_row($row++, 1, $list{$tag}); } } ...

But even after reading the perl regular expression documentation, I am still very confused as to how to read through this code and apply it to my own problem. Any help would be greatly appreciated ! -squarez

Replies are listed 'Best First'.
Re: Using Perl and WriteExcel to split data from one column to many others
by roboticus (Chancellor) on Oct 02, 2012 at 23:47 UTC


    You do it basically the same way you're already doing. Just use a column counter that's set to 0 at the start of each row:

    while (my @res = $sth->fetchrow_array()) { my $c=0; $worksheet->write($i, $c++, $_) for @res; $i++; }


    When your only tool is a hammer, all problems look like your thumb.

      I had looked at it and yes I do need to increment the columns when I start to split the columns, but the issue is that the query returns a set that is only in one column, and I need to take that column, into a data array, then pull each part of the data array out, split those values amongst the columns, and trim the ( ). I see now that split cleans , for me. The issue now is taking the split and passing the split parts to different columns. So you have this :

      while (my @res = $sth->fetchrow_array()) { my $c=0; $worksheet->write($i, $c++, $_) for @res; $i++; }

      That results in this:

      COL -> A B C D E F ROW -> 1 (x,y,..) 2 (a,b,..) 3 (q,w,..) 4 (d,f,..) etc...

      With my query results this only returns one column of data because all the columns for each row are consolidated in the first column from the result set. My perl script needs to rip that apart and allocate the contents of that first column to the other columns.

      Also thank you for the help. I am currently thinking of:

      while ($res[$o] = $sth->fetchrow_array()) { <code for splitting current array segment, and then printing the c +orresponding splits into the next columns sequentially.> }

      I am thinking possibly another loop with an array to populate each rows values, then writing within that sub loop to each row column.

        I get the funny feeling your 'first column' is just a string like this "(a, b, c, d)" (but I might be wrong, as I am new at this) If that is the case, removing the parentheses and then splitting on the comma will fix your issue:

        my $set = $res[0]; #I use a lot of 'extra' variables to tell me what everything is $set =~ s/[\(\)]//g; #remove all parentheses my @values = split(',', $set); #now every value should have its own spot in the array

        I hope this solves your problem.

        Please ignore me if this is a stupid answer...

Re: Using Perl and WriteExcel to split data from one column to many others
by kcott (Chancellor) on Oct 03, 2012 at 00:08 UTC

    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++; }

    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

      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 :)

        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"; }

        Here's the output:

        $ $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 = [ '' ]; ------------------------------------------------------------

        -- Ken

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://996936]
Approved by kcott
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (10)
As of 2018-03-17 16:50 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (224 votes). Check out past polls.