Hi luupski,

Another way to do this is with the join function, which lets you avoid appending the final comma in the first place.

Read the data from your file, split it on whitespace, join each datum with commas, and surround the entire record with parentheses:

foreach my $line (<$infh>) {
    if ($line =~ /$re_data/) {
        # Split on whitespace, surround with parens, join with commas
        push @data, "(" . join(",", split(/\s+/, $line)) . ")";
    }
}
[download]

and finally, join the records with a comma followed by a newline. The last record won't get a comma (nor newline):

# Simply 'join' all data; last record contains neither comma nor newli
+ne.
my $data = join(",\n", @data);
[download]

Here's an entire working example:

#!/usr/bin/perl

use strict;
use warnings;
use feature qw( say );
use IO::File;


##################
## User-defined ##
##################
my $input   = 'in.txt';       # Input file
my $output  = 'out.txt';      # Output file
my $re_data = qr/^[0-9]+/;    # Detect lines containing records

##################
## Main program ##
##################
# Read the input
my $infh = IO::File->new($input) or die "Can't read '$input' ($!)";
my @data = ( );

foreach my $line (<$infh>) {
    if ($line =~ /$re_data/) {
        # Split on whitespace, surround with parens, join with commas
        push @data, "(" . join(",", split(/\s+/, $line)) . ")";
    }
}
close($infh);

# Simply 'join' all data; last record contains neither comma nor newli
+ne.
my $data = join(",\n", @data);

# Create the output
my $text = qq{
    SELECT CUSTOMERID, ORDERID, CUSTOMERNAME, CUSTOMERLOCATION
    FROM DB.CUSTOMER_DATA
    WHERE (CUSTOMERID, ORDERID, CUSTOMERNAME, CUSTOMERLOCATION) IN (
      $data
    )
};
$text =~ s/\n\s+/\n/g;    # Remove indentation (for "looks")

# Write the output
my $outfh = IO::File->new;
open($outfh, '>', $output) or die "Can't write '$output' ($!)";
print $outfh $text;
close($outfh);
say "Wrote '$output'";
[download]

The output for which is:

SELECT CUSTOMERID, ORDERID, CUSTOMERNAME, CUSTOMERLOCATION
FROM DB.CUSTOMER_DATA
WHERE (CUSTOMERID, ORDERID, CUSTOMERNAME, CUSTOMERLOCATION) IN (
(0001,20000001,john,CA),
(0002,30000002,neill,WI),
(0003,40000003,joe,GA),
(0004,50000004,will,IL),
(0005,60000005,mike,IN),
(0006,70000006,bill,AK)
)
[download]

---

---

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.