#!/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 connect to database\n";

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

my $sth = $dbh->prepare("$firstsql") or die "Can't prepare SQL statement: $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 name - 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;