holli has asked for the wisdom of the Perl Monks concerning the following question:

Brethren,

I want to access a csv datafile from a template for the Template Toolkit. I have managed to read from a "real" database, using Template::Plugin::DBI as follows: 
[% USE DBI('dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/pr
+ojekte/vdx/daten/users.mdb', '', '') -%]
[% FOREACH user = DBI.query( 'SELECT * FROM users' ) -%]
    [% user.id %] [% user.name %]
[% END -%]

[download]
That is simple and straightforward. Now I have this script, that uses DBD::CSV to access a textfile. 
use strict;
use warnings;

use DBI;
use DBD::CSV;

my $dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\;});
$dbh->{'csv_tables'}->{'users'} =
{
    'file' => 'c:/projekte/vdx/daten/users.txt',
    'col_names' => ["id", "name"]
};

my $sth = $dbh->prepare("SELECT * FROM users");
$sth->execute;

while( my $row = $sth->fetchrow_hashref )
{
    print $row->{id}, ";", $row->{name}, "\n";
}

[download]
I want to do the same with a template like the one above. But I cannot figure out how to pass the additional parameters into Template::Plugin::DBI, namely the filename and the fieldnames. All my searching was fruitless.

Btw, I know there is Template::Plugin::Datafile, but this plugin won't let me specify the fieldnames.

I'm pretty sure I could write my own plugin to interface with DBD::CSV, but I don't want to if I don't have to. Any suggestions?

Thanks in advance.


holli, /regexed monk/

Replies are listed 'Best First'.
Re: Using DBD::CSV with Template::Plugin::DBI
by jZed (Prior) on Aug 25, 2005 at 16:41 UTC
    For the filename, it may work to use a delimited identifier, e.g. 
        SELECT * FROM "c:/projekte/vdx/daten/users.txt" WHERE ...

    [download]
    As for the column names, I *think* you can specify those in the connect string driver params with csv_col_names. If not, ask me again there are more evil ways. :-)
[reply]
[d/l]
      Thanks, jZed. That works. As it turns out the plugin automatically takes the first line of the file and uses it for the fieldnames. Any way to avoid that? It can't be evil enough. :)


      holli, /regexed monk/
[reply]
        Template::Plugin::DBI is on my list of modules to check out, but I've not yet worked with it. Does it allow you to specify a \%attr in the connect? If so, then this should work: 
        [% USE DBI(
    'dbi:CSV:',undef,undef,
    { RaiseError => 1
    , csv_tables => 
        { users => { file      => 'c:/projekte/vdx/daten/users.txt'
                   , eol       => "\n"
                   , col_names => ['id','name']
                   }
        }
    }
) -%]

        [download]
        If the module doesn't support setting \%attrs in the connect string, does it allow you to pass a pre-defined $dbh? If it supports neither, that seems pretty lame. That would be an issue which impacts more than DBD::CSV. OTOH in order to support \%attrs in a template probably requires a string eval which is pretty lame also. (Please don't take this as a criticism of the module, it's more of general remark on modules that support DBI connections but don't support setting \%attrs during connection). Hmm, a thought - if the templating plugin uses YAML, it could avoid string eval.
[reply]
[d/l]

Back to Seekers of Perl Wisdom