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.

TLP · Accepted Answer · 2013-10-13 21:57:06Z

5

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);

edited Oct 13, 2013 at 21:57

answered Oct 13, 2013 at 21:13

TLP

68.2k10 gold badges97 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Borodin · Accepted Answer · 2013-10-13 22:10:19Z

3

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";
}

answered Oct 13, 2013 at 22:10

Borodin

127k9 gold badges72 silver badges146 bronze badges

1 Comment

Borodin Over a year ago

@TLP: I hope you're not serious?

Suic · Accepted Answer · 2013-10-13 21:09:36Z

-1

try this sql query

select * from one_table join two_table using (primary_key) 
INTO OUTFILE '/tmp/new.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

answered Oct 13, 2013 at 21:09

Suic

2,5112 gold badges19 silver badges31 bronze badges

Collectives™ on Stack Overflow

Write from database using Perl Text::CSV

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Collectives™ on Stack Overflow

Write from database using Perl Text::CSV

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest