How to use DBD::CSV to get column names row?

Question

I am using DBD::CSV to show csv data. The code I have is:

#! perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:CSV:", undef, undef, {
    f_dir            => ".",
    f_ext            => ".txt/r",
    f_lock           => 2,
    csv_eol          => "\n",
    csv_sep_char     => "|",
    csv_quote_char   => '"',
    csv_escape_char  => '"',
    csv_class        => "Text::CSV_XS",
    csv_null         => 1,
    csv_tables       => {
        info => {
            file => "countries.txt"
        }
    },  
    FetchHashKeyName => "NAME_lc",
}) or die $DBI::errstr;

$dbh->{csv_tables}->{countries} = {
  skip_first_row => 0,
  col_names => ["a","b","c","d"],
  raw_header => 1,
};

my $sth = $dbh->prepare ("select * from countries limit 1");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
  print join " ", @row;
  print "\n"
}

The countries.txt file is like this:

ISO_COUNTRY|COUNTRY_NAME|REGION_CODE|REGION_NAME
AF|Afghanistan|A|Asia
AX|"Aland Islands"|E|Europe
AL|Albania|E|Europe

But when I ran this script, it returns

AF Afghanistan A Asia

I wanted it to return:

ISO_COUNTRY COUNTRY_NAME REGION_CODE REGION_NAME

Does any one know how to achieve this using DBD::CSV module?

Another question is why the col_names attribute setting didn't take effect? How to make it return the following?

 a b c d

score 2 · Accepted Answer · answered Oct 22 '12 at 16:56

2

$sth->{NAME}, $sth->{NAME_lc} and $sth->{NAME_uc} return a reference to an array containing the names.

my $sth = $dbh->prepare("select * from countries limit 1");
$sth->execute;
print "$_\n" for @{ $sth->{NAME} };

answered Oct 22 '12 at 16:56

ikegami

367,544
15
269
518

Thanks. Actually your code will return what I want. I have another question, I manually specified the column names to a, b, c, d. Why it doesn't work? I updated my question. – Peiti Li Oct 22 '12 at 17:13
http://search.cpan.org/~hmbrand/DBD-CSV-0.36/lib/DBD/CSV.pm#Driver_specific_attributes This is the docs. But I don't know why it doesn't work as expected. By default DBD::CSV assumes that column names are stored in the first row of the CSV file and sanitizes them (see raw_header below). If this is not the case, you can supply an array ref of table names with the col_names attribute. In that case the attribute skip_first_row will be set to FALSE. If you supply an empty array ref, the driver will read the first row for you, count the number of columns and create column names like col0, col1, ... – Peiti Li Oct 22 '12 at 17:18
Please ask your new question as a new question. (Do note that using both `raw_headers => 1` and `col_names` doesn't make any sense. Also note that the row is being skipped even though you said no, so it could be that none of those options are being used for some reason.) – ikegami Oct 22 '12 at 17:34
Ok resolved this question and I made another question: http://stackoverflow.com/questions/13016794/how-to-manually-specify-the-column-names-using-dbdcsv – Peiti Li Oct 22 '12 at 17:43

How to use DBD::CSV to get column names row?

1 Answers1