I call bullshit. That script won't even compile. If you are using a script to insert the data, it isn't that one. Or possibly you stuck a use strict; and use warnings; at the top and hoped we wouldn't notice what a godawful pile of crap it is.

You have undeclared variables. You are redefining the global $a and $b variables. (Probably not hurting you here but a bad idea in general.) You aren't doing any error checking. Not only are you not enabling error checking, you are actively disabling it. You are looking for a line that contains:

======== SCHOOL (G): FEMALE =========

when your example data uses

======== SCHOOL (G): GIRLS =========

I highly doubt that SQL Server allows column names with apostrophes in them, and even if it does, using them seems like a stupendously bad idea. You should do some kind of $dbh quoting to to avoid weird data errors, or better yet use placeholders in your prepare statement (which will do quoting automatically). Don't prepare a statement for every record, prepare it once then execute it for each record. Don't set yourself up for failure, format your code so it is readable. Use some indenting so you can tell where blocks start and end. Use descriptive variable names. In the words of MJD, "You can't just make shit up and expect the computer to know what you mean."

Oh what the heck... Do something like this.

#!/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}
        );
    };
}

[download]

In reply to Re: Data insert into DB by thundergnat
in thread Data insert into DB by sowraaj

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.