Load multiple csv file in oracle table from perl

Question

after some research decided to put question here for more expert answers.Couldn't find exact scenario as my problem so here it goes...

I think it will take few days for me to get something working, can't even think about how to move forward now.

DB: 11gR2

OS: Unix

I'm trying to load multiple csv file into Oracle table using perl script.

1. List what all csv I need to work on, since directory where csv file exist contains many other files.

2. Open csv file and insert into table

3. If there are any error then rollback all inserts of that file and move into next file

4. Record how many inserts done by each file

   #!/usr/bin/perl
   
   use warnings;    
   use strict;
   use Text::CSV;
   use DBD::Oracle;
   
   my $exitStatus = 0;
   my $dow     = `date +%a`; chomp $dow;
   my $csvDow  = `date -dd +%a`; chomp $csvDow;
   
   # define logfile
   my logFile;
   $logFile = "log.dbinserts"
   
   # define csv file directory
   my $csvLogDir  = "Home/log/$csvDow";
   
   # csv Files in array to list all possible match of file
    opendir(my $dh, $csvLogDir ) || die "can't opendir $csvLogDir : $!";
    my @csvFile = grep { /csv.*host1/ && -f "$csvLogDir/$_" } readdir($dh); chomp @csvFile;
    closedir $dh;
   
      foreach my $i (@csvFile)
         {
           $logFile (CSV File: $i);
         }
   
      foreach my $file (@csvFile) 
        {
          chomp ($item);
          $logFile-> ("Working under: $file");
          &insertRecords($csvLogDir."/".$file);
        }
   
   $logFile-> ("Exit status")
   
   #----------------
   
   sub insertRecords 
   {
    my $filetoInsert=shift;
    my $row;
   
    open my $fh, "<" or die "$fileToInsert: $!";
   
    my $csv = Text::CSV->new ({
       binary    =>1,
       auto_diag =>1,
      });
   
    while ($row = $csv->getline ($fh))
      {
         print "first column : $row->[0]\n,";
      }
    close $fh; 
   
    } 
   
   ========
   CSV File
   =========
   date, host, first, number1, number2
   20141215 13:05:08, S1, John, 100, 100.20
   20141215 13:06:08, S2, Ray, 200, 200.50
   ...
   ...
   ... 
   
   =========
   Table - tab1
   =========
   Sample_Date
   Server
   First
   N1
   N2
   

Answer 1

For the first step it depends one which criteria you'll need to select your CSV files
if it's on the name of those CSV you could simply use opendir and get the list of files with readd :

my $dirToScan = '/var/data/csv';
opendir(my $dh, $dirToScan ) || die "can't opendir $dirToScan : $!";
    my @csvFiles = grep { /.csv$/ && -f "$some_dir/$_" } readdir($dh);
closedir $dh;

In this example you'll retrieve a array with all the files that end whith .csv (whithin the design dir)
After that you'll need to use your foreach on your array.

You can find more example and explanation here I don't know the structure of your CSV but I would advise to use a module like Text::CSV, it's a simple CSV parser that will wrap Text::CSV_PP or Text::CSV_XS, if it's installed on your system ( it's faster than the PP version (because written in perl/XS)
this module allows you to transform a CSV row in a array like this :

 use Text::CSV;
 my $file = "listed.csv";
  open my $fh, "<", $file or die "$file: $!";

  my $csv = Text::CSV->new ({
      binary    => 1, # Allow special character. Always set this
      auto_diag => 1, # Report irregularities immediately
      });
  while (my $row = $csv->getline ($fh)) {
      print "first colum : $row->[0]\n";
      }
  close $fh;

from : perlmeme.org
You'll need to open() your file (within the foreach loop), pass it to the Text::CSV element (you can declare your parser outside of the loop)

That's the easiest case where you know the column number of you CSV, if you need to use the column name you'll need to user the getline_hr() function (see the CPAN doc of Text::CSV)
And once you have your values (you should be whithin the foreach loop of you file list and in the while, that list the rows of your CSV, you will need to insert this data in your database.

For this you'll need the DBD::Oracle module that will allow you to connect to the database.
Like every DBI connector you'll need to instanciate a connection, using this syntax :

 use DBI;
 $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);

And then in your loop (while your reading you CSV rows) you should be able to do something like this :

$SQL = "INSERT INTO yourTable (foobar,baz) VALUES (?,?)";
$sth = $dbh->prepare($SQL);
$sth->execute($row->[0],$row->[1]);

here you have tree step where you prepare the request with the value replaced by '?' (you can also use declared variable instead, if you have a lot of columns)
after the preparation you execute the request with the desired value (once again you don't have to use anonymous vars)

To catch if the request failed you only have to set RaiseError to when the connection is declared, that would look like something like this :

 $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd, 
 {        
    PrintError => 1,
    PrintWarn  => 1,
    RaiseError => 1
   });

And then when playing the request :

try
{
    $sth->execute($row->[0],$row->[1]);
 }
 catch
 {
    warn "INSERT error : $_";
    $CSVhasFailures = 1;
 };

You'll need to set the value of $CSVhasFailures to 0 before each CSV After that, by testing the value of the CSVhasFailures at the end of the while loop you could decide to execute a commit or a rollback using the integrated function commit and rollback whithin the DBD::Oracle module if you wan't to count the number of insert you'll just have to put a $counter++ after the $sth->execute statement for more info on the DBD::Oracle I would suggest you to read the CPAN documentation page.

Last suggestion, begin step by step : Lists your CSV files, read the rows of each CSV, read a column, print a set of column and then insert you data in a temporary table.