SQL with Perl/CSV

[Arion23]

Hi all,

I've also posted this query to the Perl forum, but thought it also crosses into general SQL as well.

Short story:
I have a CSV file being used as a datasource for a perl script (using the DBI/DBD-CSV modules).

General querying of the data works fine, however a few of the field names in the table contain whitespace. For example field "Marine or Freshwater"

I'd like to construct an SQL query based on the values in this field, something like:

SELECT * FROM table WHERE "Marine or Freshwater" = 'Marine'

However, the DBD-CSV module in Perl has a problem parsing this. I'm just wondering if white-space in field names is an issue in general with databases and SQL, or if it may be a shortcoming of DBD-CSV.

Any comments or suggestions on how I might go about doing this would be most welcome (note: I don't have control of the datasource or its design, so modifying the field names is not an option at this point)

Many thanks in advance...

 

[Tim1]

Hiya,

I will be very surprised if your RDBMS system allows a field name with a space in it, most do not. What you may find is that the system has truncated your field name at the first space, so "Marine or Freshwater" becomes Marine.

The other thing it may have done is added underscores, so becoming "Marine_or_Freshwater", but I would be surprise if it has done that.

What database server are you using? You should be able to check the column names in the table by doing:

SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE
name = 'your table name' AND type = 'U').

HTH

Tim

 

[tlbroadbent]

I prefer to eliminate white space in column names. However, MS Access and MS SQL Sever 7 both allow white space. In both cases column names can be delimited by brackets.

SELECT * FROM table WHERE [Marine or Freshwater] = 'Marine'
Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Arion23]

Thanks both to Tim and Terry,

I've tried the square brackets previously, and still ran into problems, I'll give the single quotes a go and see if that works.

Tim, unfortunately for me, it's not a proper DBMS that is being queried, it's just a single flat file in CSV format. The Perl modules DBI and DBD-CSV allow me to connect to the file and query it as if it was a table in a DBMS.

Thanks again...