too many fields, sql output in rows, not columns. Perl SOLUTION enclosed
When too many fields are selected in an SQL query, the output is displayed
as rows and not in a nice table format.
The PERL program bellow reformats SQL output from columns to rows.
If the output is already in columns, no change is made. ( transparent )
The output table will have the fields TAB delimited. Nulls will be printed
as space or zero. ( you choose )
Save this file as "col2rows.pl" ( or what ever you wish ) .
make it excutable : chmod +x col2rows.pl
to run do :
dbaccess <my_database_name> <my_sql>.sql | col2rows.pl
The output of the sql is piped through col2rows.pl
Here the output is saved to a file.
dbaccess <my_database_name> <my_sql>.sql | col2rows.pl >
outputfile.txt
I hope someone finds this helpful. ( If you use this script, I would be
pleased if you would email me that you are using it. )
Have Fun.
Isaac.
============================================================================
==========
#!/usr/local/bin/perl ### this Must be the first line of your executable
file.
# col2rows.pl
# By Isaac Loven isaac_loven@hotmail.com June 1999
# When too many fields from an Informix Database are selected in an SQL
query,
# the output is displayed as rows and not in a nice table format.
# This program reformats SQL output from columns to rows.
# If the output is already in columns, no change is made.
# You may use this program in any way you wish, but please keep my name in
it. ( I have a big ego )
# if you make any modifications, just add you name to the credits.
# The first line is the location of Your perl interpreter.
# You may have to change this to the location of your perl
# ie #!/usr/local/perl
# The output table will have the fields TAB delimited.
# Nulls will be printed as space, zero, or blank. ( you choose )
# choose fill value:
$fill=0; ## if value is blank print 0. . I prefer a 0 for my
applications,
$fill=" "; ## if value is null print a spaces
while($in=<>) # read one line in std buffer
{ $line++;
if ( $line == 3 )
{
if ( $in =~ /\\w/ )
{ $passthrough = "Y";
print "\\n";
print "\\n";
}
else
{ $passthrough="N";
print "\\n";
}
}
if ( $passthrough eq "Y" ) { print $in ; }
if ( $passthrough eq "N" )
{
( $name , $value) = split ('\\s+', $in); # split std buffer
if ( $name ne "" )
{
$value=$fill if ( $value eq "" );
###### Now if the data id a fraction ie 3.123456778 Cut off
###### the least significant digits after the 3rd decimal place:
###### You can adjust this.
$value =~s/(\\d+\\.\\d\\d\\d)\\d+/$1/;
$val_str .= $value ."\\t";
$header .= $name . "\\t";
}
if ( $name eq "" && $blank==1 ) { print "$header\\n\\n"; }
if ( $name eq "" ) { print "$val_str\\n"; $blank++;
l_str=""; }
}
}
Isaac,
Put this script along with a README.1st file into a shell archive
and submit it to the IIUG Software Repository. (email to:
software@iiug.org). This way folk who are not monitoring CDI this week
can also find your script.
Art S. Kagel
Isaac Loven wrote:
>
> When too many fields are selected in an SQL query, the output is displayed
> as rows and not in a nice table format.
>
> The PERL program bellow reformats SQL output from columns to rows.
>
> If the output is already in columns, no change is made. ( transparent )
>
> The output table will have the fields TAB delimited. Nulls will be printed
> as space or zero. ( you choose )
>
> Save this file as "col2rows.pl" ( or what ever you wish ) .
>
> make it excutable : chmod +x col2rows.pl
> to run do :
>
> dbaccess <my_database_name> <my_sql>.sql | col2rows.pl
> The output of the sql is piped through col2rows.pl
>
> Here the output is saved to a file.
> dbaccess <my_database_name> <my_sql>.sql | col2rows.pl >
> outputfile.txt
>
> I hope someone finds this helpful. ( If you use this script, I would be
> pleased if you would email me that you are using it. )
>
> Have Fun.
> Isaac.
>
> ============================================================================
> ==========
>
> #!/usr/local/bin/perl ### this Must be the first line of your executable
> file.
>
> # col2rows.pl
>
> # By Isaac Loven isaac_loven@hotmail.com June 1999
>
> # When too many fields from an Informix Database are selected in an SQL
> query,
> # the output is displayed as rows and not in a nice table format.
>
> # This program reformats SQL output from columns to rows.
>
> # If the output is already in columns, no change is made.
>
> # You may use this program in any way you wish, but please keep my name in
> it. ( I have a big ego )
> # if you make any modifications, just add you name to the credits.
>
> # The first line is the location of Your perl interpreter.
> # You may have to change this to the location of your perl
> # ie #!/usr/local/perl
>
> # The output table will have the fields TAB delimited.
> # Nulls will be printed as space, zero, or blank. ( you choose )
>
> # choose fill value:
> $fill=0; ## if value is blank print 0. . I prefer a 0 for my
> applications,
> $fill=" "; ## if value is null print a spaces
>
> while($in=<>) # read one line in std buffer
> { $line++;
> if ( $line == 3 )
> {
> if ( $in =~ /\\w/ )
> { $passthrough = "Y";
> print "\\n";
> print "\\n";
> }
> else
> { $passthrough="N";
> print "\\n";
> }
> }
>
> if ( $passthrough eq "Y" ) { print $in ; }
> if ( $passthrough eq "N" )
> {
> ( $name , $value) = split ('\\s+', $in); # split std buffer
> if ( $name ne "" )
> {
> $value=$fill if ( $value eq "" );
> ###### Now if the data id a fraction ie 3.123456778 Cut off
> ###### the least significant digits after the 3rd decimal place:
> ###### You can adjust this.
> $value =~s/(\\d+\\.\\d\\d\\d)\\d+/$1/;
> $val_str .= $value ."\\t";
> $header .= $name . "\\t";
> }
> if ( $name eq "" && $blank==1 ) { print "$header\\n\\n"; }
> if ( $name eq "" ) { print "$val_str\\n"; $blank++;
> l_str=""; }
> }
> }