comment on

Hello all.

I am having problems with DBI:CSV. I keep getting the following error:

"SQL ERROR: Bad table or column name 'col2 + col3' has chars not alphanumeric or underscore!
DBD::CSV::db prepare failed: SQL ERROR: Bad table or column name 'col2 + col3' has chars not alphanumeric or underscore!
for Statement "SELECT AVG(col2 + col3) FROM mydata.csv" at test_script.pl line 11."

I have the following code to query a csv file.

#!/usr/bin/perl

use DBI;
use strict;
use warnings;

## Set up connection to csv data file as a database
my $dbh = DBI->connect("dbi:CSV:f_dir=./", "", "",
        {AutoCommit => 1, RaiseError => 1});

my $sth = $dbh->prepare("SELECT AVG(col2 + col3) FROM mydata.csv");
$sth->execute();
my $value = $sth->fetchrow_array;

foreach (@value){
    print $_."\n";
}

$sth->finish();
$dbh->disconnect();
[download]

This works fine:

my $sth = $dbh->prepare("SELECT AVG(col3) FROM mydata.csv");
[download]

So does this:

my $sth = $dbh->prepare("SELECT (col2 + col3) FROM mydata.csv");
[download]

Just not this:

my $sth = $dbh->prepare("SELECT AVG(col2 + col3) FROM mydata.csv");
[download]

The mydata.csv file has the following in it:

col1,col2,col3,col4

575,480,192,5

388,485,194,5

379,447,210,5

504,465,215,0.8

356,439,219,0.7

481,435,225,0.6
[download]

Any idea what is going wrong? Any help would be appreciated.

In reply to DBI:CSV SQL parsing problem by rlewisuk

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


"be consistent"
	PerlMonks