Perl DBI CSV column name problems

[gnubie]

I’m trying to create a perl script which reads an exported Contacts file in CSV (comma-separated-values) format. I’m new to SQL and DBI and I’ve run into several problems:

1. I’m baffled by the CREATEd database column names. I used fname and lname in my test case. When I look at the successfully created database, I find the column names: FNAME and LNAME. Why uppercase? Furthermore, a SELECT would only work when I used the uppercase names. So, am I doing something wrong or is uppercase a requirement? The complete code follows:

  #!/usr/local/bin/perl -w
  use DBI;
  use strict;
  my $createTable="CREATE TABLE contacts (fname Char, lname Char)";

  my $db=DBI->connect("DBI:CSV:") or die "Cannot connect: ".$DBI::errstr;
  $db->do($createTable) or die "Error: Couldn't CREATE TABLE, ".$db->errstr();
  $db->disconnect();

  exit(1);
  1

2. I really need to use a name format with an embedded space, like “First Name”, but I cannot find the correct syntax. For example, single quotes result in the error:

  SQL ERROR: Bad table or column name '?0?' has chars not alphanumeric or underscore!

  my $createTable="CREATE TABLE contacts (‘First Name’ Char, lname Char)";

But using:

  my $createTable="CREATE TABLE contacts (\"First Name\" Char, lname Char)";

(with escaped double quotes) didn’t result in an error but the column name had three pair of quotes:

  """FIRST NAME""",LNAME

So question 2, can a column name like ‘First Name’ be created using DBI with a CSV connect and if so how?

If you know of a specific tutorial that covers this specific use of perl DBI/CSV, please point me to it. I’ve read a lot of DBI stuff and found no solution. I couldn’t even get Martin Brown’s examples to work (from Perl! I Didn’t Know You Could Do That…” I’ve read the DBI tutorial on CPAN.

I’m running ActiveState Perl v5.6.1 and ppm installed the latest DBI. I’ve ppm verified my installation. I’m running Windows XP.

Thanks,

Gnubie

 

[gnubie]

Problem 1 solved. I removed and reinstalled ActiveState Perl and the DB packages. Now, column names are the same as input. HOWEVER, problem 2 remains (can't create column names like "First Name". Any assistance would be appreciated.

Gnubie

 

[MikeLacey]

I tried "kas kjsd" for a column name with DBD::CSV a while ago and gave up..... Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[gnubie]

OK, so apparently you can't CREATE a column name like "First Name". What about SELECTing a db with column names like "First Name","Last Name"? Has anyone done that? After all, that's what a CSV file IS.

Gnubie

 

[gnubie]

Here is my attempt at SELECTing by "Last Name". I cannot get it to properly SELECT a column by name (a standard CSV formatted column name that is).

#!/usr/bin/perl -w
#
# TABLE is 'contact' and contains:
#
# "First Name","Last Name"
# "Jane","Doe"
# "John","Doe"
# "Jack","Benimble"
#

use strict;
use DBI;

# Connect

my $dbh=DBI->connect('DBI:CSV:',undef,undef,{RaiseError=>1}) or die $DBI::errstr;

# Make sure configured for Windows XP CSV format

$dbh->{csv_tables}->{contact}={
  'eol'=>"\015\012",
  'sep_char'=>",",
  'quote_char'=>'"',
};

# Test Goal: SELECT Last Name FROM contact WHERE First Name is Jack
#
# ("SELECT * FROM contact&quot works (meaning no error; returns all);
# ("SELECT 'Last Name' FROM contact&quot results in SQL ERROR: Bad table or column name '?0?' has chars not alphanumeric or underscore!
# ("SELECT ".$dbh->quote("Last Name&quot." FROM contact&quot results in SQL ERROR: as above

my $sth=$dbh->prepare("SELECT ".$dbh->quote("Last Name")." FROM contact");
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
  print "$row->{'First Name'} $row->{'Last Name'}\n";
}
exit;
1

 

[MikeLacey]

Have you read this section from the documentation?

col_names
skip_first_row

By default DBD::CSV assumes that column names are stored in the first row of the CSV file. If this is not the case, you can supply an array ref of table names with the col_names attribute. In that case the attribute skip_first_row will be set to FALSE.
If you supply an empty array ref, the driver will read the first row for you, count the number of columns and create column names like col0, col1, ...
Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[gnubie]

Thank you for your response.

1. Yes, I've read, read, and re-read the documentation, searched the Intenet, read FAQs etc.

2. I tried the method you suggested above and it works. The problem is that I want a method which accepts a standard CSV file (from any Microsoft application that exports CSV) without having to alter the CSV file OR create an array of column names each time.

3. I'm still trying to determine why it doesn't work:
a) is it my fault (wrong configuration or syntax)? or,
b) is it not supported by Perl DBI and/or CSV?

I think it is really powerful to be able to treat any CSV file like a SQL database!

 

[MikeLacey]

I don't think it's your fault - looks to me that DBD::CSV doesn't support embedded spaces in column names.

Have you considered:

Reading the first line of the CSV file, before you do a DBI connect to it, and building your column names array.

Connect to the CSV file as if you were going to use the first row as column names but *ALSO* set skip_first_row to true. Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[gnubie]

Thanks, Mike, for the response. Can you elaborate? How do you know? Did you look at the code? Some Spec? I can't image why it wouldn't be supported. I checked and found that both Microsoft DOS CSV and Microsoft Windows CSV have column names of the format "xxx xxx","yyy yyy".

Do you know of a CSV spec, standard, or RFC, etc.?

Fortunately, the workarounds are easy to generate.

Thanks again,

Gnubie

 

[MikeLacey]

I don't know of a proper standard no.

To be honest, I just tried the things you tried, read the documentation and, eventually, saw that skip_first_row thing. Was only bit I didn't try myself, so I'm quite glad it was useful

And reading CPAN modules, otherwise known as Software Archeology, can quickly turn into a full time job, so no; I didn't do that.

Glad you're sorted. Sorry I wasn't able to just answer your question. Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884