AnyData and csv file

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

Dear Monks, I'm trying to learn to use the DBI and AnyData modules and start of with AnyData. A tiny text file

1,apple,2005
2,banana,2002
3,peach,2001
4,apple,2002
5,nuts,2008
[download]

And my script:

#!/usr/bin/perl -w
use strict;
use diagnostics;
use DBI;

my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
$dbh->func(
    'test', 'CSV', 'test.csv',
    { sep_char => ',', end_of_line => "\n", col_names => 'number,fruit
+,year' },
    'ad_catalog'
);

my $sth = $dbh->prepare("SELECT year FROM test WHERE fruit ='apple'");
$sth->execute();
while ( my $row = $sth->fetch ) {
    print "@$row\n";
}
[download]

The result I get is: 2005 2 while expecting:

apple,2005
apple,2002
[download]

Someone around who can guide me to the correct outcome?
Thanks, Gert

Comment on AnyData and csv file Select or Download Code

Replies are listed 'Best First'.
Re: AnyData and csv file by CountZero (Bishop) on Oct 13, 2007 at 21:46 UTC
When running your program I get: `2005 2002` [download] which is as expected, since your SQL only selected the field 'year'. To get your expected output, use the following SQL: `SELECT fruit, year FROM test WHERE fruit ='apple'` CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l] [select]
Re: AnyData and csv file by jZed (Prior) on Oct 13, 2007 at 21:55 UTC
I don't see any problems with your code except that the "end-of-line" should be "eol" but that shouldn't matter since the default eol is "\n". So I am guessing that the line endings in your file are something else - try setting eol to "\012" or "\015" or "\015\012". If none of those fix it, then maybe you have an older version of SQL::Statement (the SQL engine behind AnyData) so you could try upgrading. If none of that works, message me here on perlmonks and I'll take a look.	[reply]
Re^2: AnyData and csv file by GertMT (Hermit) on Oct 14, 2007 at 06:54 UTC
great, by changing `end-of-line` to `eol => "\015"` it works! It is maybe because I'm on Mac OS X? I just installed SQL::Statement so that should be up to date. Putting the extra column `fruit` in there was a bit obvious. Thanks a lot, Gert	[reply] [d/l] [select]


Pathologically Eclectic Rubbish Lister
	PerlMonks