my $oldrow;
my(@fields,@values);
while(<DATA>) {
  chomp;
  my ($row,$col,$data) = /^\s*\(\s*(\d+)\s*,\s*(\d+)\s*\)\s*=>(.*)$/;
  # that puts the row number in $1, col number in $2 and data in $3, a
+nd in the variable respectively
  if($oldrow != $row) {
    if(@fields) { #make sure there is data
      $query = "INSERT INTO tablename(".(join ',', @fields)." VALUES("
+.(join ',', @values).')';
      ... do DB calls ...
      @fields = @values = ();
    }
    $oldrow = $row;
  }
  push @fields, $field_names[$col]; # field name with proper data
  push @values, db->quote($data); #run data through DB quoter
}
[download]

so if the original SQL statement is:

SELECT junk.name, st.site_type, junk.location, junk.mapref
FROM junksite junk, site_type st
WHERE junk.site_type_id = st.id

the arrays would map as follows



name      -> 0<br>
site_type -> 1<br>
location  -> 2<br>
mapref    -> 3<br>
[download]

so from there you can write


$array_ref = $sth->fetchall_arrayref( [0, 2] );
[download]

#!/usr/bin/perl -w
#
# Tool to import comma delimited files into a database
# Works on command line
# Usage: perl tableimport.pl
#

use DBI;
use strict;

print "Filename:";      #Name of Comma Delited file
chomp (my $file = <>);
print "DB:";       # Database name
chomp (my $dbname = <>);
print "Table:";     # Table name
chomp (my $table = <>);

open (FILE, "$file") or die "Couldn't open file!\n";
my @lines = <FILE>;

#assume the column names are on the first line

my $colnames = shift (@lines);
chomp ($colnames);
my @cols = split (',', $colnames);
# do quotes separate the data?

my $quotes = 0;

if ($cols[0] =~ m/^\"[\S]*\"$/) {
        $quotes = 1;
        foreach (@cols) {s/\"//g;}
}


print "Column Names:\n";
foreach (@cols) {print; print "\n";};
print "Next Step?";
my $step = <>;

my $num_cols = $#cols;

my $dbh = DBI->connect("dbi:Pg:dbname=$dbname","","") or die "Can't co
+nnect to database\n";

my $firstsql = "SELECT * FROM $table";

my $sth = $dbh->prepare("$firstsql") or die "Can't prepare SQL stateme
+nt: $DBI::errstr\n";
$sth->execute or die "Can't execute: $DBI::errstr\n";

my @column;

my $numfields = $sth->{NUM_OF_FIELDS};
for (my $i=0; $i<$numfields; $i++) {
        $column[$i] = $sth -> {NAME} -> [$i];
}

$sth->finish;

print "Columns in file:$num_cols\n";
print "Columns in database:$numfields\n";

my $dbcol;
my %match = ();

foreach (@cols) {
        foreach $dbcol (@column) {
                if ($_ eq $dbcol) {$match{$_} = 1};
        }
        if (!$match{$_}) {print "$_ does not have a matching field nam
+e - it will not be imported!\n";}
}

print "Next Step?";
$step = <>;
my $j=0;

foreach (@lines) {
        my $chip = chomp;
        print "Row:$j:, $chip\n";
        print "Line:$_\n";

#Put the lines in a hash keyed to column names
    my %rows = (); my $i=0; my $col;
    my @data;
    if ($quotes) {s/"//g;}
    @data = split (',');
    foreach $col (@cols) {
        if ($match{$col}) {
                $rows{$col} = $data[$i];
                print "$col: $rows{$col}\n";
        }
        $i++;

    }

    print "\n";
# Build a SQL statement to insert each line from file

    $i=0;
    my $sql0 = "INSERT INTO $table (";
    my @sqla = (); my @sqlb = ();
    my $key;
    foreach $key (keys %rows) {
            $sqla[$i] = $key;
            $sqlb[$i] = $dbh->quote($rows{$key});
            $i++;
        }


        my $sql1 = join (',',@sqla);
        my $sql2 = ") VALUES (";
        my $sql3 = join (',',@sqlb);
        my $sql4 = ")";
        my $sql = "$sql0$sql1$sql2$sql3$sql4";

        print "SQL:$sql\n";

        print "Import? [Y/n]";
        chomp(my $import = <>);
        if ($import !~ /n/i) {

                my $sth = $dbh->prepare("$sql") or die "Can't prepare 
+SQL statement: $DBI::errstr\n";
                $sth->execute or die "Can't execute: $DBI::errstr\n";
                print "Imported successfully\n";
        }

        $j++;

}
$sth->finish;
$dbh->disconnect;
[download]