Re: Data insert into SQL Server?

Here's a tidied up and working version of your script

#$dbh->do('DROP TABLE test1'); # uncomment after first run
my $sql = 'CREATE TABLE test1 (
             type     char(5),
             students int, 
             class    int, 
             teachers int, 
             hostel   char(10) )';
             
$dbh->do($sql);
my $sqli = 'INSERT INTO test1 VALUES (?,?,?,?,?)';
my $sth = $dbh->prepare($sqli);

# 1 records
my @fld=();

# read data
foreach (<DATA>) {
  chomp $_;
  my ($event,$data)=split(/:/,$_);
  
  if ($event =~ /\(B\)/){
     insert_record() if ($fld[0]);
     $fld[0] = "BOYS";
  }
  
  if( $event =~ /\(G\)/){
     insert_record() if ($fld[0]);
     $fld[0] = "GIRLS";
  }

  if ($event =~ /^No.of stud/i){
    $fld[1] = $data;
  }

  if($event =~ /^No.of class/i){
    $fld[2] = $data;
  }

  if($event =~ /^No.of teach/i){
    $fld[3] = $data;
  }

  if($event =~ /^Hostel facility/i){
    $fld[4] = $data;
  }
}
# don't forget last record
insert_record() if ($fld[0]); 

# insert one records
sub insert_record {
  print "Inserting @fld\n";
  $sth->execute(@fld[0..4]);
  @fld=();
}

__DATA__
+++++++++++School(B):Students++++++++++
No.of students:120
No.of classes:2
No.of teachers:5
Hostel facility:available
++++++++++School(G):Students+++++++++++
No.of students:300
No.of classes:3
No.of teachers:10
[download]

poj

Comment on Re: Data insert into SQL Server? Download Code

Replies are listed 'Best First'.
Re^2: Data insert into SQL Server? by gmoque (Acolyte) on Aug 08, 2011 at 02:17 UTC
Here is another approach, by using the file record separator ($/) to actually a "record" in your data file. There are a few approaches to it, one approach requires to change the source data file to use two newlines between records; this is because $/ can't take a regex as input. When $/ value is an empty string it will match \n\n+ or a paragraph. Also, if you use a hash to generate a table to translate the description to the field number you can simply the script for maintaning and when adding/removing fields as well as generating a template in case your need to feed default values. Disclosure: I don't have a SQL server to test the code, that's why I commented the code, I focused the attention on how the data is handled. #!/usr/bin/perl use warnings; use strict; my %fields = ( 'Type' => 0, 'No.of students' => 1, 'No.of classes' => 2, 'No.of teachers' => 3, 'Hostel facility' => 4, ); my $dbh; #$dbh->do('DROP TABLE test1'); # uncomment after first run #my $sql = 'CREATE TABLE test1 ( # type char(5), # students int, # class int, # teachers int, # hostel char(10) )'; #$dbh->do($sql); my $sqli = 'INSERT INTO test1 VALUES (?,?,?,?,?)'; #my $sth = $dbh->prepare($sqli); my @record = (); local $/ = ""; foreach(<DATA>) { chomp; # Fix the type field if(!s/^\++School$B$:Students\++/Type:BOYS/) { s/^\++School$G$:Students\++/Type:GIRLS/; } my @arr = split /[\r\n]+/; foreach(@arr) { my ($key, $data) = split /\s:\s/; $record[$fields{$key}] = $data; } print "Inserting \"@record\"\n"; # $sth->execute(@record[0..4]); } __DATA__ +++++++++++School(B):Students+++++++++++ No.of students : 120 No.of classes:2 No.of teachers:5 Hostel facility:available +++++++++++School(G):Students+++++++++++ No.of students:300 No.of classes:3 No.of teachers:10 [download]	[reply] [d/l]
Re^3: Data insert into SQL Server? by poj (Abbot) on Aug 08, 2011 at 18:21 UTC
Your script works fine except because the second record does not have a Hostel facility field, it uses the data from the previous record `c:\Temp\perlmonks>perl 919135.pl Inserting "BOYS 120 2 5 available" Inserting "GIRLS 300 3 10 available"` [download] Just need to clear the @record after the execute with `@record=();` [download] poj	[reply] [d/l] [select]