Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

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

by roboticus (Canon)
on Oct 02, 2012 at 23:47 UTC ( #996938=note: print w/ replies, xml ) Need Help??


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

squarez:

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

...roboticus

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


Comment on Re: Using Perl and WriteExcel to split data from one column to many others
Download Code
Re^2: Using Perl and WriteExcel to split data from one column to many others
by squarez (Initiate) on Oct 03, 2012 at 01:15 UTC

    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.

Re^2: Using Perl and WriteExcel to split data from one column to many others
by squarez (Initiate) on Oct 03, 2012 at 01:25 UTC

    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...

        Thank you for the help, went with your solution as it proved very quick :)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://996938]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2014-08-21 10:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (134 votes), past polls