How to export this Unicode table as CSV in Perl 5?

Question

I have a table in which there is Unicode. I know that the data in Unicode is beautiful, since the JSON on our web server is excellent. But for some reason, the CSV that I create ends up distorted. Here is our current code:

  my $csv = Text::CSV->new ({ eol => "\015\012" });
  open my $fh, '>:encoding(utf8)', 'Foo.csv';
  my $sth = $dbh->prepare("SELECT * FROM Foo");
  $sth->execute();
  my $i = 0;
  while (my $row = $sth->fetchrow_hashref) {
     $csv->print($fh, [keys %$row]) if $i == 0;
     $csv->print($fh, [values %$row]);
     $i++;
  }

Any ideas?

Answer 1

Besides the encoding problem, I don’t think there is a guarantee that valuesit will always give the fields in the same order. You can get different hashref values ​​from fetchrow_hashrefevery time you call it. The solution to this is to use fetchrow_arrayref.

Text :: CSV recommends Text :: CSV :: Encoded

my $csv = Text::CSV::Encoded->new({ eol => "\015\012" });
open my $fh, '>:raw', 'Foo.csv';
my $sth = $dbh->prepare("SELECT * FROM Foo");
$sth->execute();
$csv->print($fh, $sth->{NAME_lc}); # or NAME or NAME_uc

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

, :

use Encode 'find_encoding';
my $utf8 = find_encoding('utf8');

my $csv = Text::CSV->new({ binary => 1, eol => "\015\012" });
open my $fh, '>:raw', 'Foo.csv';
my $sth = $dbh->prepare("SELECT * FROM Foo");
$sth->execute();
# I'm assuming your field names are ASCII:
$csv->print($fh, $sth->{NAME_lc}); # or NAME or NAME_uc

while (my $row = $sth->fetchrow_arrayref) {
   $csv->print($fh, [ map { $utf8->encode($_) } @$row ]);
}