RE: Reading from SQL, Writing to CSV ...

[Jones Robert Contr 81 CS/SCK]

From: Joe Schell [mailto:[EMAIL PROTECTED]]
Jones Robert Contr 81 CS/SCK wrote:
> 
>         I'm running the following snippet of code below.  Every time I
read
> a field in that has a series of alpha with commas in it there are
problems.
> After each of these multiple comma fields is read, the next field is
> skipped.  For example, if a field contains 'john, henry, III' then my next
> field is omitted.
>

--You know the next field is omitted because you printed it out in the
--while-fetch loop?

Actually, below the while/fetch loop is another loop which puts all of the
elements into a CSV file.  It's pretty simple.  Go through each element of
the matrix created (ota[x][y]) and put a comma between them.  If there is a
comma (,) in any element, then encapsulate the element in quotes (").  Then
I bring Excel up and look at the contents.

The problem is if I have an element with a double comma in it, and only on
those fields with a double comma in it, the next field is omitted right
after it.  So if I have field #9 with the contents of "mike, joey sr," then
if field #10 is his race nothing appears.  

I'll have to do some more testing to make sure field #10 is actually in my
SQL reading list (@import) and a few other debugging routines.  This is
certainly turning into a big mystery at this point.  
 
>         Are there any characters which when read into a list will screw it
> up ??
>

--Not that I know of.  How are you interfacing with the
--database(interface, perl version, driver version.)

The Interface is with DBI through DBD-Oracle to an Oracle 8 database.  This
is running on my Windows 2000 machine.  The Perl version is 5.005_03 build
522.  The DBI is at 1.13.1 and the DBD-Oracle is at 1.03.  

I have used this same program with other databases and tables with no
problems whatsoever at all.  I may have to create a few test tables and
insert some double-commas in them and variations to see how it reacts.
Maybe there's something else in the field causing it to react this way.  The
field it is reading from is a VarChar2(35) field, and the field being
omitted after it is a VarChar2(1) field.  
 
> while (@import=$sth->fetchrow_array)
>  {
>   $count++;
>   $tmpcount = 0;
>   foreach $holder (@import)
>    {
>     $tmpcount++;
>     $ota[$count][$tmpcount] = $holder;
>    }
>  }
> 
> open(FHCSV, "> ota.csv") || die "Ouch !!!\n $!\n";
> for ($xx=1; $xx <= $count; $xx++)
>  {
>   for ($yy=1; $yy <= $tfields-1; $yy++)
>    {
>     if ($ota[$xx][$yy] =~ /\,/)
>      { print FHCSV "\"$ota[$xx][$yy],\""; }
>     else
>      { print FHCSV "$ota[$xx][$yy],"; }
>    }
>   if ($ota[$xx][$tfields] =~ /\,/)
>    { print FHCSV "\"$ota[$xx][$tfields]\"\n"; }
>   else
>    { print FHCSV "$ota[$xx][$yy]\n"; }
>  }
> close(FHCSV);
_______________________________________________
Perl-Win32-Users mailing list
[EMAIL PROTECTED]
http://listserv.ActiveState.com/mailman/listinfo/perl-win32-users