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.

You are using () inside qq(), which is rather weird (but seems to work for some reason!), and don't have a semi-colon on the end of the line, which should result in syntax error. You should not ever, for any reason, try to type your source code into a question: Always cut and paste. — TLP
– TLP, Commented Oct 13, 2013 at 21:11
semicolon added! The code had to be anonymized/ generalized in order to make sense. Cutting and pasting the actual code would not have worked. Nb. the parantheses in MySQL statements whether or not bounded by qq() always seem to work for me! — user1849286
– user1849286, Commented Oct 13, 2013 at 22:56
With qq() (and many other perl operators, such as m//, s///, qr//, etc) you can change the parentheses to almost any other punctuation character. This reduces the need to escape characters, such as m/\/usr\/bin\// could be m#/usr/bin/#. But in this case, it seems that qq() can handle parentheses inside, just so long as they are balanced. I did not know that. One of those edge cases that I am not sure are really useful, but an interesting piece of trivia. :) — TLP
– TLP, Commented Oct 14, 2013 at 10:18

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

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related