51233

How to manually specify the column names using DBD::CSV?

Question:

I am using <a href="http://search.cpan.org/~hmbrand/DBD-CSV-0.36/lib/DBD/CSV.pm#Driver_specific_attributes" rel="nofollow">DBD::CSV</a> to show csv data. Sometimes the file doesn't contain column names, so we have to manually define it. But after I followed the documentation, I got stuck with how to make the attribute skip_first_row work. 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"], }; my $sth = $dbh->prepare ("select * from countries limit 1"); $sth->execute; while (my @row = $sth->fetchrow_array) { print join " ", @row; print "\n" } print join " ", @{$sth->{NAME}};

The countries.txt file is like this:

AF|Afghanistan|A|Asia AX|"Aland Islands"|E|Europe AL|Albania|E|Europe

But when I ran this script, it returns

AX Aland Islands E Europe AF AFGHANISTAN A ASIA

I expected it to either return:

AF AFGHANISTAN A ASIA a b c d

or

a b c d a b c d

Does any know what's going on here?

Answer1:

For some reason, contrary to the documentation, it doesn't see the per-table settings unless you pass them to connect.

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 => { countries => { col_names => [qw( a b c d )], } }, FetchHashKeyName => "NAME_lc", }) or die $DBI::errstr;

Then it works fine:

my $sth = $dbh->prepare ("select * from countries limit 1"); $sth->execute; print "@{ $sth->{NAME} }\n"; # a b c d while (my $row = $sth->fetch) { print "@$row\n"; # AF Afghanistan A Asia }

Recommend

  • How can I undef and def preprocessor value at run time
  • How to manually specify the column names using DBD::CSV?
  • How to compare my string, which is stored in an array, to function names from a complete library in
  • How to suppress #define locally?
  • File Handling in Perl: Reading a file from a specific line
  • convert 0 into string
  • How can create an HTML document from several files in Perl?
  • GoogleTest force #undef on particular test
  • Best way to assign a variable one of two possible values based on defined?
  • when i create a user for rabbitmq-management, an error has occured
  • Read file into variable in Perl [duplicate]
  • How do I call SetWindowLong() in the 64-bit versions of Windows?
  • How to print the profile details individual lines
  • Console.log error in Brackets
  • Unit test for a C program
  • Delphi 2007 and {$IFDEF…} directive, fails to see our conditional
  • Error while accessing element of array in struct
  • How to mock a Node.js module loaded with dojo/node
  • Sequence of enumerators at compile time
  • Insert BOM to a CSV File using PERL
  • Perl: Search text file for keywords from array
  • LWP::UserAgent and proxy error
  • Mysql query works well at workbench but takes too long in r
  • How to do I split a string into hash keys with undef values?
  • What is the best way to create persistent memcached connections under mod_perl?
  • Angular and Jasmine: How to test requestError / rejection in HTTP interceptor?
  • Can std::cout work with UTF-8 on Windows?
  • rabbitmq-erlang-client, using rebar friendly pkg, works on dev env fails on rebar release
  • AsyncTask onPostExecute not being called
  • What's the point of meck:validate?
  • Reading a CSV file into Java as a DB table
  • R connection to postgresql requiring SSL
  • How to save dynamically created textboxes and their values
  • pip in virtualenv gets ConnectTimeoutError
  • Dynamically switching connect in Modelica
  • nonblocking BIO_do_connect blocked when there is no internet connected
  • Redux Form - Not able to type anything in input
  • Run multiple queries from 1 SQL file showing result in multiple tables
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • Installed module is empty