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

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

Hi Monks, I'm a little stuck and was hoping one of you could help get me out of the mud :-). I had a script that worked until we added over 999 values to it recently. I now have about 3,000 values separated by a comma on 1 line. Unfortunately, I can't change the way the data is provided. Ultimately, I need to get a max of 999 of them into an array or some other structure so I can then perform a query from the db with those values. i.e, select * from table_name where values in ( ). If I can just get chunks of 999 stuffed into an array I could then do a "for loop" until all values have been queried and added as a new worksheet to my excel spreadsheet. It would need to be dynamic too since the values could increase or decrease over time.

Below is a snippet of the code that used to work (until we broke the 999 threshold that Oracle allows in an "IN" clause by adding over 3k comma separated values in the input record.)

open INPUT_FILE, @row[$cell_column] or die "Can't open @row[$cell_colu +mn]: $!\n"; while ( $line = <INPUT_FILE> ) { chomp $line; if ($line =~ /^#/ || $line =~ /^\s*$/) { next; } $line =~ s/^[^=]*\=//; @users = split /,/, $line; } close INPUT_FILE; $placeholders = join ( ',', ('?') x @users ); my $SQLLock=qq{ select user,name from teams where user in ( $placeholders ) }; my $sthL = $dbh->prepare($SQLLock); $sthL->execute(@users); while ( my @row = $sthL->fetchrow_array() ) { print ">@row<\n"; for($cell_column=0; $cell_column<=$#row; $cell_column+ ++){ $worksheet->write_string($i, $cell_column, @r +ow[$cell_column],$format1); } $worksheet->set_row($i, undef, undef, 0, 0, 1); $i++; } $sthL->finish;

Here is my attempt to get the split function broken up by lines of 3. ( For the example I chose a small number because it's more managable. Just need to prove out the logic, but clearly the "limit" clause of the split isn't keeping 3 values per line. This does not work.

#!/usr/bin/env perl use DBI; use POSIX 'strftime'; use strict; use Data::Dumper; my $line; my @teams; while ( $line = <DATA> ) { chomp $line; if ($line =~ /^#/ || $line =~ /^\s*$/) { next; } $line =~ s/^[^=]*\=//; @teams = map { [ split /,/, $_, 3 ] } $line; } print Dumper \@teams; __DATA__ Teams=PATRIOTS,BILLS,DOLPHINS,JETS,COWBOYS,GIANTS,EAGLES,REDSKINS,BENG +ALS,OILERS,STEELERS,BROWNS,SEAHAWKS,RAMS,49ERS,RAIDERS

Would greatly appreciate any help or assistance that could be provided. Thanks!

In reply to How to Split on specific occurrence of a comma by dirtdog

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or or How to display code and escape characters are good places to start.
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (3)
As of 2021-10-26 05:41 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (90 votes). Check out past polls.