homer4all has asked for the wisdom of the Perl Monks concerning the following question:

DB: oracle

With my previous query I have got this far and trying to solve couple of tricky data.

With below code, I'm opening csv file, read each line, insert into table, if any error rollback and move to next csv file.

My file contains 50,000 records but with below script it only enters 500 rows ignoring all other rows.

What I believe is csv data format is messing up with the inserts and somehow it's ignoring rows.

1.I think allow_loose_quotes=>1 and allow_whitespace=> needs to be modified to use properly or new parameter?

2.Also how to have n2 value stored as 0.0 and not just 0 in the table OR 0.3 values as 0.3 and not .3?

3.With my current approach it prepare each line and insert, any quick trick to insert 10,000 rows at a time? 

data e.g.  (Date, server, n1, n2, command)
20141201 00:00:01, sun_solaris, 30308, 0.0, "sun_gen0_db1 "
20141201 00:00:10, hp_host, 30308, 0.0, "[(jdbc/migration] -/^perl "


#!/usr/bin/perl

use warnings;    
use strict;
use Text::CSV;
use DBD::Oracle;

my $exitStatus = 0;

# Connect to database and disable autocommit
my $dbAgent = OracleAgent->dbLogin(dbName);
$dbAgent->connect();
$dbAgent-> setAutoCommit(0);

# 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) 
 {
   $logFile-> ("Working under: $file");
   &insertRecords;
 }

$logFile-> ("Exit status")


#----------------

sub insertRecords 
{

my $csv;
my $fileToInsert = shift;
my $row;
my $SQL;
my $sth;
my $dbh;
my $rc;


open my $fh, "<", $fileToInsert or die "$filetoInsert: $!"

$logFile -> ("Working under $file")

my $csv = Text::CSV->new (
    {  binary             =>1,
       auto_diag          =>1,
       allow_loose_quotes =>1,
       allow_white_space  =>1,
    });

 $SQL1 = "Insert into TAB1 (sample_date, server, n1, n2, command)
         values (?,?,?,?,?)";
 $sth = prepare($SQL1);


while ($row = $csv->getline ($fh))
 {
 $sth -> execute($row[0], $row[1], $row[2], $row[3], $row[4]);
       if ($sth1->err)
           {
              my $status = "FAIL";
                 last;
           }
       $rc++;
  }

 if $status eq 'FAIL'
    {
      $dbAgent->rollback();
      $rc=0;
    }
  else
    $dbAgent-> commit;
close $fh;

}
}

[download]
Note: this question is also posted by me on another website Stack overflow, inorder to get more expert answer and different ways to solve it.

Replies are listed 'Best First'.
Re: Perl csv insert with special character
by Tux (Canon) on Dec 31, 2014 at 09:13 UTC

    The code still is way too terse to give useful comments. I understand that you had to make a manual copy (which had too many errors to expect useful comments), but otoh you could have taken a bit more time to come up with valid code that still shows the erroneous behavior.

    From your code, it looks like you do not use the "standard" DBI interface, so suggesting debug actions in that area are likely to be inapplicable.

    You have posted no single error message, so what do you expect us to reply on? WE HAVE NO CLUE IN WHAT GOES WRONG!

    In your three line data example I see no reason for allow_loose_quotes

    Simplify your code to a more perlish manner:

    open my $fh, "<", $fileToInsert or die "$filetoInsert: $!"
my $csv = Text::CSV->new ({
    binary             => 1,
    auto_diag          => 1,
    allow_white_space  => 1,
    });

# I have to assume prepare () is a *function* in a singleton
# instead of the method provided by DBI
my $sth = prepare (
    "insert into TAB1 (sample_date, server, n1, n2, command) values (?
+,?,?,?,?)");

while (my $row = $csv->getline ($fh)) {
    $sth->execute (@$row);
    }

    [download]

    Text::CSV's getline returns an arraref, so the next line - would you have used strict and warnings - would have caused a lot of noise, as you would need a dereference like $row->[0] instead of $row[0], and on that line there is no need to name all of them separately, as you are using all of them anyway, so use @$row.

    All of my comments show that you really really need to post code that parses, as we cannot see where the original code goes awry.


    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
Re: Perl csv insert with special character
by graff (Chancellor) on Dec 31, 2014 at 10:08 UTC
    Step 1: Whatever computer you are using to post questions here, have Perl and Text::CSV (and Text::CSV_XS) installed there, and either copy or create a suitable quantity of relevant CSV data on that computer.

    Step 2: Write a Perl script (on that computer) to read all the csv data, using the same approach you intend to use on your "work laptop"; this script should print out a simple summary report: how many files were opened for reading, and how many rows of data were read (in total, and/or per csv file).

    Step 3: If this test script (written and run on the computer that you use to post questions on PM) does not report the expected quantities, or doesn't run at all, and you can't figure out why, copy/paste that script, along with its output, including error messages/warnings, all within code tags when you post your next question here.

    Step 4: Once you confirm that your test code reads all your test csv data correctly, run that exact same code on your "work laptop", and see whether it reports correct numbers for the actual (production) csv data that you need to load into your database. If it doesn't, you need to figure out how your test data differs from the production data, and adjust your code accordingly. If necessary, add to the test data on your testing/posting machine to replicate the problem, so you can copy/paste the records that cause the trouble.

    Step 5: Once you confirm that your test code reads all your production data correctly, make a copy of it and use the copy to add the Oracle DB stuff. I would recommend using DBI if possible (this will automatically invoke DBD::Oracle for the connection); that way, you would be able to put some other database handler on your testing/posting machine - e.g. SQLite or MySQL - in order to perform the same DB operations without having to run your own separate Oracle server. (There could be differences between Oracle and other DBs that might cause problems for you, but at least you'd be able to isolate problems of this sort with some confidence.)

    Maybe that sounds like a lot of work, but from what I can see, if you don't do that, you'll never finish the job.

[reply]
Re: Perl csv insert with special character
by Anonymous Monk on Dec 30, 2014 at 23:16 UTC
    With below code I'm ...

    That's impossible, since the code doesn't compile due to many syntax errors. You were told this last time too: since you claim you have it, post the working code. The current "code" has plenty of simple errors that you can even fix yourself, you were told how, and you even said you "100% agree to" that suggestion.

    This is starting to look like a pattern, it looks like you're just trying to sweet talk us into doing your work for you.

[reply]
      Seriously getting someone else do my work will be the last thing in my mind. Spent half day resolving this then finally decided to get experts help.

      I have typed each line here manually since I can't copy paste from my work laptop. Out of 500 codes of line I have written here what was relevant to my question. Code does work but somehow speical character gets ignore or something else. For which I though TEXT::CSV -> parameter and sample data will be more than enough.

[reply]
[reply]
[d/l]
      NOTE: code will give compilation error, db connection is not included since it public sub used by entire company. Also validate command sub is not included.
[reply]
Re: Perl csv insert with special character
by Anonymous Monk on Dec 30, 2014 at 23:04 UTC
[reply]
Re: Perl csv insert with special character
by GotToBTru (Prior) on Dec 31, 2014 at 14:27 UTC

    It's been a little while since I worked with Oracle, but the quick way to insert large amounts of data is SQL*Loader.

    1 Peter 4:10
[reply]

Back to Seekers of Perl Wisdom