fieldName{tab}value
[download]

#!c:\perl\bin -w

use strict;

use DBI;

my @data;
my $DSN      = 'somedsn';
my $USER     = 'someuser';
my $PASSWORD = 'somepass';

# Here we will store files with bad records
my %badrecs; 

# Connect to database
my $dbh = DBI->connect("dbi:ODBC:$DSN", $USER, $PASSWORD,
            {RaiseError => 1})
            or die "Couldn't connect to database: " . DBI->errstr;

my $dir = "G:/local/common/rates/";

# Get all filenames

opendir(DIR, $dir) || die "Can't opendir $dir: $!";
my @files =  readdir(DIR);
closedir DIR;

# Read each file and ad to database
my $numfiles = $#files;
my $i = 0;

foreach my $file (@files) {
    print "Processing " . $i++ . " out of $numfiles files: $file";

    # filename must be letters or digits followed by .txt extension
    if ($file =~ /^[-a-zA-Z0-9]+\.txt/) {
        my @data = readConfig($file);
        my $line = 0;
        PROCESS_RECORD:
        foreach my $arrayref (@data) {
            print ' ' . ++$line;
            for my $j (0..3) {
                # We'll not process record if any of first three value
+s are false
                # Third ($j == 2) value must be numeric
                # Fourth value is not required, but if it exists, it m
+ust be numeric
                
                if (($j < 3 && !@{$arrayref}[$j])
                    || 
                    ($j == 2 && @{$arrayref}[$j] !~ /^[0-9.]+$/)
                    ||
                    ($j == 3 && defined @{$arrayref}[$j] && @{$arrayre
+f}[$j] !~ /^[0-9.]+$/)) {
                    $badrecs{$file} .= $line . ',';
                    next PROCESS_RECORD;
                }
            }
            insertConfig(@{$arrayref});
        }
    } else {
        print " Skipped";
    }
    print "\n";
}

# Disconnect from database

$dbh->disconnect;

open BADRECS, ">BadRecs.txt" or die "Can't open BadRecs for writing: $
+!\n";
for (keys %badrecs) {
    $badrecs{$_} =~ s/,$//;
    print BADRECS $_ . "\t" . $badrecs{$_} . "\n";
}
close BADRECS;
exit();

sub readConfig {
    my $fto = shift;

    $fto =~ /([^.]+)\.txt/;
    my $lenderID = $1;
    
    open(CONFIG, "<$dir$fto") or die "Can't open $dir$fto: $!";
    # my ($lenderID,$progName,$rate,$apr,$fee);
    my (@rows);
    while(<CONFIG>) {
        chomp;

        my @value = ($lenderID, split(/\t/));
        
        push @rows, \@value;
    }
    close(CONFIG);
    return @rows;
}

sub insertConfig {
    my @value = @_;
    my @fieldName = qw(lenderID progName rate apr fee);
    
    my ($sql, $fields, $placeHolders);
    
    for (0..$#value) {
        $fields       .= $fieldName[$_] . ',';
        $placeHolders .= '?' . ',';
    }
    
    $fields       =~ s/,$//;
    $placeHolders =~ s/,$//;
    
    $sql = 'INSERT INTO lenderrecs.dbo.rates (' . $fields . ') VALUES 
+(' . $placeHolders . ')';

    my $sth = $dbh->prepare($sql);
    $sth->execute(@value);
}
[download]

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just go the the link and check out our stats.

If you are working from Linux or other GNUish/BSD ish platforms, you can also install the Sybase libraries and treat the M$SQL server as a Sybase machine. Handy trick once in a while.

--
$you = new YOU;
honk() if $you->love(perl)

You'll find that the documentation for being able to talk to an MS SQL server is quite good, I think.

You should also get your hands on a program called sqsh, which is a free software command line SQL interface to Sybase/MS SQL. Basically, it's to those databases what sqlplus is to Oracle.

Also, you'll want to consider your choices in terms of a Perl interface to the database. The obvious choice is DBI, using DBD::Sybase. However, DBD::Sybase is still relatively young and unstable, and it doesn't do a lot of things one might want. Unless you need your database code to be written so that it could be easily ported to another database, I would recommend using the Sybase::* modules instead. They aren't DBI-based, but will get you mostly everything you need from DBI except the modularity. (Sybase::* is the Perl5 version of sybperl, the TDS equivalent of oraperl). Both DBD::Sybase and Sybase::* are written and maintained by Michael Peppler, and he prefers the latter, so that's what I generally use when forced to interface with MS SQL.

-dlc

++'ed with my last point of the day 'cause I shoulda said dat. Also, check TDS carefully before you use it. It tortured me to the point of exasperation one day. Also, your DBAdmin may not like having to use DBAUTH mode rather than MSAUTH mode. Too bad, MSAUTH mode is still blackbox evil...

--
$you = new YOU;
honk() if $you->love(perl)