You could save column headings a couple of ways. The easiest is to simply define the column headings in an array and then print them out with a join. If the headings vary, you can usually pull the column headings from the db connection. The following example works for ODBC but I believe that DBI has a similar option. I cobbled this snippet from programs that I have in production so I've skipped the setup, initialization and most of the error checking that should accompany the code.

    my ($rtncd, $db) = OpenODBC($cfg{obtsdsn}, $cfg{dbf01});
    my $colnames = '';
    my $outfile = 'testfile.csv';
    my $sqlstmt = '';
    my @rowdata = ();

    open(OUTFILE, ">$outfile") || 
         die "Could not open output file $outfile\n$!\n";

    $colnames = join(',',@{GetColNames($db,$tblname)});
    print OUTFILE $colnames."\n";

    $sqlstmt = qq|SELECT TOP 10 * FROM $tblname|;
    if($db->Sql("$sqlstmt")){
        $rtncd = -1;
        print "error!!Could not query table ".
              "$tblname\n$sqlstmt\n".$db->Error();
    }else{
        while($db->FetchRow()){
                              # retrieve and initialize data
            @rowdata = map{ $_ ||= '' } $db->Data();
                         # doesn't account for comma in data
            print OUTFILE join(',',@rowdata);
            print OUTFILE "\n"; # one per line
        }
    }
    close(OUTFILE);
    $db->Close;
    
    exit;
} #end main()
############################################################;
sub GetColNames{
    my $db = shift;
    my $tblname = shift;
    my $rtncd = 0;
    my $sqlstmt = qq|SELECT TOP 1 * FROM $tblname|;
    my @colnames = ();
    
    if($db->Sql("$sqlstmt")){
        $rtncd = -1;
        print "error!![0105]Could not query table ".
              "$tblname\n$sqlstmt\n".$db->Error();
    }else{
        if($db->FetchRow()){
            push @colnames, $db->{fnames}[$_] 
                                  for (0..$#{$db->{fnames}});
        }
    }
    
    return \@colnames;
} #end GetColNames()
############################################################;

[download]

In reply to Re: save the result of sql query in csv file by periapt
in thread save the result of sql query in csv file by shilpam

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.