From the "answer overkill department": here's a script I wrote once that does what you're asking. I had a directory fully of .txt files and needed to enter their data into an MS SQL 7.0 database. The files each had several lines of data in the form of:

fieldName{tab}value
[download]

This script was a quick hack (which explains the poor commenting), but should give you a nice starting point for writing your own:

#!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]

Hope this helps!

Cheers,
Ovid

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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.