Spliting line with varying entries 1

[iowaprogrammer]

I am trying to write a program to extract user info from a database and write them into a file. The
content of the field in the database I am calling can have one or more entries and are separated by a '|'. See sample data below...

user@domain|user2@domain|user3@domain
friend@domain
funny@domain|funny1@domain
lucy@domain|lueue@domain

So I am trying to use split with the '|' as the separator and then write the entry to a file like the following output below..

user@domain
user2@domain
user3@domain
friend@domain
funny@domain
funny1@domain
lucy@domain
lueue@domain

But what I get is only the first entry of each record like...

user@domain
friend@domain
funny@domain
lucy@domain


This is the code I have used so far

#!/usr/bin/perl -w

use Win32::ODBC;

my $DSN="Usermanager";
#my $SqlStatement = "SELECT Useremail FROM allusers where Useremail != ''";
my $SqlStatement = "SELECT Userothermailboxes FROM allusers where Userothermailboxes != ''";
my $user;

unless(open(OUTUSERS,'>c:\queryuserothermailboxes.txt'))
{
die("Can not open OUFILE\n");
exit;
}

if (!($db = new Win32::ODBC($DSN)))
{
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}

if ($db->Sql($SqlStatement)){
print "SQL failed.\n";
print "Error: " . $db->Error() . "\n";
$db->Close();
exit;
}

if (! $db->Sql($SqlStatement))
{ @FieldNames = $db->FieldNames();
$Cols = $#FieldNames + 1;

for ($i = 0; $i < $Cols; $i++)
{ print $FieldNames[$i], "\t";
}
print "\n";
}

while( $db->FetchRow()){
undef %Data;
($Data) = $db->Data();

$_=$Data;
print "$_\n";

@user=split(/\|/,$_);
# print OUTUSERS "$user\n";
foreach(@user){
{
print "$_\n";
}
}
}

$db->Close();

close OUTUSERS;


Thanks to all in advance

 

[feherke]

Hi

Seems that you are using Windows, but you output only [tt]\n[/tt] as line terminator. Are you sure the result is actually wrong and not just a stupid file viewer/editor is messing up the lines ? Try to output it as :

[b]foreach[/b] [teal]([/teal][navy]@user[/navy][teal])[/teal] [teal]{[/teal]
  [b]print[/b] [green][i]"$_[highlight]\r[/highlight]\n"[/i][/green][teal];[/teal]
[teal]}[/teal]

Note, the inner pair of braces ( {} ) seems useless there.

Anyway, personally I would drop that [tt]foreach[/tt] loop :

[b]while[/b] [teal]([/teal][navy]$db[/navy][teal]->[/teal][COLOR=darkgoldenrod]FetchRow[/color][teal]())[/teal] [teal]{[/teal]
  [b]undef[/b] [navy]%Data[/navy][teal];[/teal]
  [teal]([/teal][navy]$Data[/navy][teal])[/teal] [teal]=[/teal] [navy]$db[/navy][teal]->[/teal][COLOR=darkgoldenrod]Data[/color][teal]();[/teal]

  [navy]$_[/navy][teal]=[/teal][navy]$Data[/navy][teal];[/teal]
  [b]print[/b] [green][i]"$_\n"[/i][/green][teal];[/teal]

  [highlight][navy]$_[/navy][teal]=~[/teal][b]s[/b][fuchsia]/\|/\r\n/[/fuchsia][b]g[/b][teal];[/teal][/highlight]

  [b]print[/b] [green][i]"$_\r\n"[/i][/green][teal];[/teal]
[teal]}[/teal]

Feherke.

http://free.rootshell.be/~feherke/

 

[Kirsle]

Actually that \r thing probably isn't necessary...

In Perl, because of its C roots, \n is automatically translated into your system's native line break format. In Unix, \n is literally \n (hex 0A); on Windows, \n is actually \r\n (hex 0D0A).

So, on Windows, when you use \r\n it actually means 0D0D0A, or CrCrLf, and not CrLf like you'd think. If you literally mean to have \r\n (and want it to be \r\n on all platforms), you should use the hex versions.

$_ =~ s/\|/\x0d\x0a/;

Kirsle.net | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[iowaprogrammer]

feherke

I tried the code you gave and it worked like a charm.

while ($db->FetchRow()) {
  undef %Data;
  ($Data) = $db->Data();

  $_=$Data;
  print "$_\n";

  $_=~s/\|/\r\n/g;

  print "$_\r\n";
}

I am writing it on a windows machine and I believe it was the editor. I tried komodo and the code worked fine then. But I liked your code better as it had less lines involved then.

Thank you for your input

 

[Kirsle]

For the heck of it, I did an experiment with line breaks.

On Windows 7 command prompt,

C:\Users\Kirsle>perl
open (TEST, ">C:/Users/Kirsle/test.txt");
print TEST "hello\r\nworld\r\n";
close (TEST)
__END__

C:\Users\Kirsle>copy test.txt Z:\
        1 file(s) copied.

This was in a virtual machine and Z:\ was mapped to a VirtualBox shared folder network path, where I sent it to a folder on my Linux machine. Note that FTP wasn't used so there was no mangling of the end-of-lines due to transferring in ASCII mode; this was a literal copy/paste to the Linux filesystem.

In Linux I did `hexdump -C` to see the hex of the text file.

[kirsle@firefly VBoxShared]$ hexdump -C test.txt 
00000000  68 65 6c 6c 6f [red]0d 0d 0a[/red]  77 6f 72 6c 64 [red]0d 0d 0a[/red]  |hello[red]...[/red]world[red]...[/red]|
00000010

So, this shows that on a Windows machine when you use "\r\n" in Perl, it really does mean, "Carriage Return + Carriage Return + Line Feed", which is probably not what ya want.

Doing the same test all in Linux and not Windows:

[kirsle@firefly VBoxShared]$ perl
open (TEST, ">test2.txt");
print TEST "hello\r\nworld\r\n";
close (TEST);
__END__
[kirsle@firefly VBoxShared]$ hexdump -C test2.txt 
00000000  68 65 6c 6c 6f [blue]0d 0a[/blue] 77  6f 72 6c 64 [blue]0d 0a[/blue]        |hello[blue]..[/blue]world[blue]..[/blue]|
0000000e

Kirsle.net | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[feherke]

Hi

Thank you, Kirsle.

I actually had a pale amnesia about reading something like that. But while in [tt]perl[/tt] even the [tt]$\[/tt] is set to [tt]undef[/tt] by default, I concluded that was probably a different language.

( Of course, now after acting stupid I found it in the documentation. man perlport | ISSUES | Newlines. )

Feherke.

http://free.rootshell.be/~feherke/

 

[Annihilannic]

Seeing you acting stupid is a rare pleasure indeed, feherke.

Annihilannic.