#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $table  = 'SOWND1';

my $dbh =
  DBI->connect( 'dbi:ODBC:SQLServer', 'new', undef, { PrintError => 0, RaiseError => 1 } );

my $sth = $dbh->prepare("DROP TABLE IF EXISTS '$table'");
$sth->execute;

$sth = $dbh->prepare(
    "CREATE TABLE $table(
        TYPE varchar(300),
        SCHOOL_NAME varchar(300),
        NAME_OF_STUDENT varchar(300),
        ID_NUMBER varchar(300),
        ADDRESS varchar(300),
        PINCODE varchar(300),
        MOBILE_NUMBER varchar(300),
        LAND_LINE varchar(300),
        FATHERS_NAME varchar(300),
        MOTHERS_NAME varchar(300),
        ANNUAL_INCOME varchar(300),
        CHARACTER varchar(300)
    )"
);

$sth->execute;

$sth = $dbh->prepare("INSERT INTO $table VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");

my @files = <C:/strawberry/perl/bin/SCHOOL/STUDENTS/*>;
die "No files found\n" unless scalar @files;

for my $infile (@files) {
    local $/ = '';
    open my $fh, '<', $infile or die "$!\n";
    while (my $record = <$fh>) {
        chomp $record;
        my %info;
        $record =~ s/^=+.+ MALE =+/TYPE :BOYS/;
        $record =~ s/^=+.+ GIRLS =+/TYPE :GIRLS/;
        map {
            my ( $key, $value ) = split / :/, $_;
            $key =~ s/\'//g;
            $info{$key} = $value;
        } split /\n/, $record;
        $info{LAND_LINE} ||= 'NE';

        $sth->execute(
            $info{TYPE},            $info{SCHOOL_NAME},
            $info{NAME_OF_STUDENT}, $info{ID_NUMBER},
            $info{ADDRESS},         $info{PINCODE},
            $info{MOBILE_NUMBER},   $info{LAND_LINE},
            $info{FATHERS_NAME},    $info{MOTHERS_NAME},
            $info{ANNUAL_INCOME},   $info{CHARACTER}
        );
    };
}