Replace $_ =~ s/\s/\|/g; with local $"='|';.

$" sets the delimiter between array elements in a double quoted string. By default, it is set to a single space which is why you were originally seeing a space between array elements. The space wasn't part of the array elements, nor the array, which is why trying to substitute spaces with pipes didn't work.

$" is a global variable. The keyword local keeps your custom setting of that variable from affecting other parts of your code. Your custom setting will only apply within the while loop.

Best, beth

Thanks beth. That works great too. I now understand the difference in working with scalars and lists. I need to work with the individual elements treating those as scalar data.

Cheers, Neurotoxx

join() to the rescue

while (@row = $sth->fetchrow_array) {
    print OUTFILE '|',join('|',@row),"|\n";
}
[download]

print OUTFILE '|',join('|',@row),"|\n";

You could let join do all the work.

    print OUTFILE join '|', '', @row, "\n";
[download]

I hope this is of interest.

Cheers,

JohnGG

Join is very good. :) Thanks Jethro and Johngg

$sth->execute;
    open (OUTFILE, "> file.txt")
        or die "Cant open dat file : $!";
    while (@row = $sth->fetchrow_array) {
        $_ =~ s/\s/\|/g;
        $_ =~ s/(.*)/|$1|/;
        print OUTFILE "@row\n";
    }
    
close OUTFILE;
[download]

That is correct. I did some more reading on arrays last night and now I understand how the data structure is different from scalar data. I did use your search expression, and it works great. Thank you very much, -Neurotoxx

Thanks. The regex expressions work great. However, I have a couple more issues with the data. For example, I would like to replace only the spaces after the first element and before the last element. I need to keep the spaces that are part of the last name. For example:

0211121253 Mike Dell ID06533
0211121253 Leo De Vinci ID02014
0211121253 Joe De La Marco ID65255
[download]

Should look like this:

0211121253|Mike|Dell|ID06533
0211121253|Leo|De Vinci|ID02014
0211121253|Joe|De La Marco|ID65255
[download]

Also, I would like it to delete any lines that are missing field elements.

0211121253|Mike|Dell|ID06533
Missing data|Leo|De Vinci|ID02014 <--delete line
0211121253|Joe|De La Marco|ID65255
[download]

I have been trying to create a regex that keys on "ID" and replaces the space before it. I've been trying these:

$_ =~\b\sID\d; 
$_ =~^\d+;
[download]

But these expressions replace instead of add the pipe.

I would really appreciate a way to add the pipes and not replace the search value in the regex.

Thank you so much for your help,

vince

#!/usr/bin/perl

use strict;

open( my $out_file, '>', "/tmp/outfile" )
  or die "Error: Cannot open file\n";

while( my $line = <DATA> ) {
    $line =~ s/\s/\|/g;
    print $out_file "|" . $line . "\n";
}

close( $out_file );

__DATA__
0211121253 Mike Dell ID06533
0211121253 Chris Jones ID02014
0211121253 Pa Kettle ID65255
0111119112 Mitch Poo ID05983
[download]

While the other monks have provided the answer to your question, I'm going to provide you with a cautionary example:

...roboticus

Ah, yes...that can be bad. I was able to structure the query to ignore middle initials, but a few slipped in.

__DATA__
0211121253|Mike|Dell|ID06533
0211121253|A|Chris|Jones|ID02014 # this is not good
0211121253|Pa|Kettle|ID65255
0111119112|Mitch|Poo|ID05983
[download]

However, this leads me to the question: How do I remove the initial and/or period, leaving the data structure the same?

pseudo code: 
if row contains an element with one letter
  then remove letter
continue pipe delimiting...
[download]

Thanks, vince

neurotoxx:

That would depend on your data source:

• If it's a database, you could add some delimiters during the query
• If it's a fixed-format flat file, you could take field lengths into account (substr, unpack)
• If there are a fixed number of fields in the row, you could put all the "extras" in the name field
• Perhaps you could take advantage of the fact that one of the fields matches /ID\d+/

That's generally the problem with "squishy" data. Sometimes you have to look it over and get clever....

...roboticus

You can use next to skip the rest of an iteration in a loop:

while( ... ) {
    ...
    next if <something>;
    ...
}
[download]

The <something> is treated as an expression. Expressions can be $variable =~ /<regex>/ or just a single variable, which evaluates to false if undef, 0 or ''.

You say I'm trying to read the file..., whereas your code clearly generates the file (file.txt) using data read, a row at a time, from an Oracle query.

Given this, it strikes me that the simplest solution to your formatting problem (which would, at the same time, solve the problem identified by roboticus) - is, IMO, to modify the query to force Oracle to change the default column separator - to '|' as required - in the report.

That was my original plan, but I wasn't able to figure out how to set that in the SQL query. I'll check the docs. Thanks for you help, I sincerely appreciate it.

-Neurotoxx

The simplest approach is to specify the list of columns concatenated with the '|' character - something like (I can't remember the exact syntax):

SELECT id + '|' + FirstName  + '|' + Surname
FROM some_table
WHERE ....
[download]

Otherwise, IIRC, you need to issue a set command (to set the column separator) prior to running the query - RTFM for further details.

A user level that continues to overstate my experience :-))

jethro's solution seems to be the most natural for me. In your solution, I don't see where $_ gets its value. Does fetchrow_array silently sets it?