Thursday, January 20, 2011

Reading the Csv File and inser into Database using the Perl Script

To read the CSV File or Xls file the following packages is needed

Spreadsheet::ParseExcel::Simple
Spreadsheet::ParseExcel
DBI

the perl script


#!/usr/bin/perl

use DBI;
use Spreadsheet::ParseExcel::Simple;
use Spreadsheet::ParseExcel;

my $workbook = Spreadsheet::ParseExcel::Workbook->Parse('Agent Details.xls');

#==================================================
#==================================================
foreach $sheet (@{$workbook->{Worksheet}}) {

       foreach $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
              
               if ($sheet->{Cells}[0][$col]->{Val} eq "COL1") {
                       $col1= $col;
               }
               if ($sheet->{Cells}[0][$col]->{Val} eq "COL2") {
                       $col2= $col;
               }
               if ($sheet->{Cells}[0][$col]->{Val} eq "COL3") {
                       $col3= $col;
               }
               if ($sheet->{Cells}[0][$col]->{Val} eq "COL4") {
                       $col4= $col;
               }
       }
#==================================================
#==================================================
       foreach $row ($sheet->{MinRow}+1 .. $sheet->{MaxRow}) {

                  $col_1 = $sheet->{Cells}[$row][$col1]->{Val};
                  $col_2 = $sheet->{Cells}[$row][$col2]->{Val};
                  $col_3 = $sheet->{Cells}[$row][$col3]->{Val};
                  $col_4 = $sheet->{Cells}[$row][$col4]->{Val};

#==================================================
#==================================================
                if( $col_1!= ' ' && $col_2!= ' ' && col_3 !=' ' && col_4 != ' ')
                {
                    $dbh = DBI->connect("DBI:mysql:databasename:localhost:3306", "username", "password")
                     or die "Couldn't connect to database: " . DBI->errstr;
                    $stmt = "INSERT INTO databasename.tablename (col1,col2,col3,col4) values ( '$col_1' ,'$col_2','$col_3','$col_4');";
                    $affected_rows = $dbh->do($stmt);
                }
       }
}
#==================================================



No comments:

Post a Comment