CSV column names

[Anonymous Monk]

Hi, i am making a program that queries a csv file using the DBI module, but can't find out how to get the column names. Can someone help me out. tx.

use DBI;
$dbh = DBI->connect("DBI:CSV:");
$dbh->{'csv_tables'}->{'info'}={'file'=>'info.csv'};

$qu = $dbh->prepare("SELECT * From info");
$qu->execute();
while (@row = $qu->fetchrow_array){
print "@row\n";}

[Mr. Muskrat]

If you haven't enabled skip_first_row (you have not in this script) and info.csv has all of the column names defined in the first row then you should be able to use col_names.

I just threw this together. It is untested!

#!/bin/env perl
use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('DBI:CSV:');
$dbh->{csv_tables}{info} = { file => 'info.csv' };

my $qu = $dbh->prepare('select * from info');
$qu->execute();

my @cols = @{$dbh->{csv_tables}{info}{col_names}}; # col_names won't b
+e defined until you've queried the file.
print "column names: @cols\n";

while( my @row = $qu->fetchrow_array) {
  print "row: @row\n";
}

Update: Corrected script.

[Marshall]

I tried this with one of my CSV files.

Not sure what is going on.

#!/bin/env perl
use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('DBI:CSV:');
$dbh->{csv_tables}{info} = { file => '2011-SSCW.csv' };

#Gives 'Not an ARRAY reference at C:\Projects_Perl\SSPH_Results\Muskra
+t.pl line 11.'
#my @cols = @{$dbh->{csv_tables}};
#print "column names: @cols\n";

my $qu = $dbh->prepare('select * from info');
$qu->execute();

## for unknown reasons, fetchrow_array returns undef values
## I hacked this in to get the CSV contents

while( my @row = grep{defined $_}$qu->fetchrow_array) 
{
  print "row: @row\n"; #I get the CSV file ok, here
}

[Mr. Muskrat]

I've updated my script now that I have had a chance to try it.

[Anonymous Monk]

Sorry to bother you once more. When i use sql to make the table(csv.file), once again the column names are not appearing. So the script is not working. Any ideas?

[erix]

"working", "not working" ? -- please be more explicit: what happens when you do what, exactly? What and why is unexpected?

Post the code and the first few lines of the files (10 lines or so will do).

[Cristoforo]

Note how he defines col_names Help with DBD::CSV

[Anonymous Monk]

I dont want to define them up front, of course i could simply open the the file and extract the first line, but i thought it might be better not to do that

[Tux]

Why so difficult???

$ cat info.csv
id,count,desc,asc,row
1,2,foo,bar,10

$ cat test.pl
#!/pro/bin/perl

use 5.18.2;
use warnings;

use DBI;
use Data::Peek;

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_ext => ".csv/r" 
+});
my $sth = $dbh->prepare ("select * from info");
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
    DDumper $row;
    }

$ perl test.pl
{   asc              => 'bar',
    count            => 2,
    desc             => 'foo',
    id               => 1,
    row              => 10
    }
$

[Anonymous Monk]

Thank you for the help., It is working.