Write from database using Perl Text::CSV

Question

Am trying to use database query to print query output to CSV but can't get the output on to separate lines. How to do so?

Here's the code:

use warnings;
use DBI;
use strict;
use Text::CSV;

#set up file
my $csv = Text::CSV->new ( { binary => 1 } )  # should set binary attribute.
             or die "Cannot use CSV: ".Text::CSV->error_diag ();

open my $fh, ">:encoding(utf8)", "new.csv" or die "new.csv: $!";

#set up query
my $dbh = DBI->connect("DBI:mysql:db", "name") or die ("Error:  $DBI::errstr");

my $sql = qq(select * from one_table join two_table using (primary_key));
my $query = $dbh->prepare($sql);
$query->execute;

#loop through returned rows of query and save each row as an array
while ( my (@row ) = $query->fetchrow_array ) {
    #print each row to the csv file
    $csv->print ($fh, [@row]);    
        # every line seems to be appended to same line in "new.csv"
        # tried adding "\n" to no avail 
    }
close $fh or die "new.csv: $!";

This must be a common use case but couldn't find anything about issues with new lines.

Answer 1

I assume your problem is that all your CSV data ends up on the same line?

You should set the eol option in your CSV object:

my $csv = Text::CSV->new ( {
         binary => 1,      # should set binary attribute.
         eol    => $/,     # end of line character
}) or die "Cannot use CSV: ".Text::CSV->error_diag ();

This character will be appended to the end of line in print. You might also consider not copying the values from your fetchrow call every iteration, since print takes an array ref. Using references will be more straightforward.

while (my $row = $query->fetchrow_arrayref) {
    ....
    $csv->print($fh, $row);

Answer 2

First of all, you have a missing semicolon at the end of the line

my $sql = qq(select * from one_table join two_table using (primary_key))

By default, Text::CSV uses the current value of $\, the output record separator at end of line. And, again by default, this is set to undef, so you won't get any separator printed.

You can either set up your $csv object with

my $csv = Text::CSV->new({ binary => 1, eol => "\n" });

or just print the newline explicitly, like this. Note that's there's no need to fetch the row into an array and then copy it to an anonymous array to get this to work. fetchrow_arrayref will return an array reference that you can just pass directly to print.

while (my $row = $query->fetchrow_arrayref) {
  $csv->print($fh, $row);
  print $fh "\n";
}