SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Export database table to a csv file in perl

    If my table is like the following:-

    Name Age
    John 10
    Luke 20
    Roger 30


    What I would like to do is to output above to a csv file in the following format:-

    John, Luke, Roger
    10, 20, 30

    I have the table in an array which i assume needs to be manipulated..
    Any ideas how i can do this ?

  2. #2
    SitePoint Wizard bronze trophy KevinR's Avatar
    Join Date
    Nov 2004
    Location
    Moon Base Alpha
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as you read the data from the databse, store the names in one array and the numbers in another array. Then use those arrays to output to the cvs file. Post your current code.

  3. #3
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by KevinR View Post
    as you read the data from the databse, store the names in one array and the numbers in another array. Then use those arrays to output to the cvs file. Post your current code.
    my $sql = 'select job_name, run_rime from batch_timings
    where job_name in ("anv.pr.ia.rbs.elp","anv.pr.birt.batch.elp")';



    $getkey = $dbh -> prepare($sql);
    $getkey -> execute;


    open OUT, ">$rfile" or die "Can't open $rfile. $!\n";

    while (@row = $getkey->fetchrow) {
    foreach (@row) {
    print OUT $_ . "\n" ;
    }
    }


    close OUT;

  4. #4
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm... this might work:

    Code Perl:
    my $sql = 'select job_name, run_rime from batch_timings where job_name in ("anv.pr.ia.rbs.elp","anv.pr.birt.batch.elp")';
     
    my $getkey = $dbh->prepare($sql);
    $getkey->execute;
     
    my (@names, @values);
     
    while( my @row = $getkey->fetchrow_array ){
        push @names, $row[0];
        push @values, $row[1];
    }
     
    $getkey->finish;
     
     
    open my $OUT, '>', $file or die $!;
    print {$OUT} (join ",", @names) . "\n";
    print {$OUT} (join ",", @values) . "\n";
    close $OUT;

    NOTE: The code is untested, but should work.

  5. #5
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that.

    I have another question :-

    What can we use in perl to retreive a date in the Locale format?

    Basically, I am retrieving some data from a database table via a stored proc. The data in the table is in English date format i.e dd/mm/yyyy. However, the perl scrip somehow converts this into mm/dd/yyyy.

    I have used setlocale(LC_ALL, "english"); - but not doing the job.

    Any ideas what I can do here ?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •