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;
[download]

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
[download]

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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.