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


#!/usr/bin/perl
use MongoDB;
use MongoDB::OID;
use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;
use Spreadsheet::Read;

$date=$ARGV[0];
$date or $date=`date --date='1 day ago'  +%d-%m-%Y`;
chomp $date;

my $file_path="/root/prac/packages/SDP_VAS_NQI-17-06-2015.xlsx";
my $workbook = ReadData($file_path,cells => 0 );

if(defined $workbook->[0]{'error'})
{
    print "Error occurred while processing $file_path:".$workbook->[0]
+{'error'}."\n";
    exit(-1);
}

my $worksheet = $workbook->[1];
my $max_rows = $worksheet->{'maxrow'};
my $max_cols = $worksheet->{'maxcol'};

for my $row_num (15..($max_rows))
{
    my $cell  = $worksheet->{cell}[2][$row_num]; # same, unformatted
    if($date eq $cell)
    {
        my $sqi  = $worksheet->{cell}[36][$row_num]; # same, unformatt
+ed
        print "$sqi\n";
        print "$cell\n";
        print "$row_num\n";
        break;
    }
}
chomp $sqi;
$db_host="172.16.15.115";
$db_name="ravi";
$client     = MongoDB::MongoClient->new(host => $db_host, port => 2701
+7);
$database   = $client->get_database( $db_name );
$data = $database->get_collection("SDP_SQI");
%insert_data_hourly=();
$insert_data_hourly{'date'}=$date;
$insert_data_hourly{'SQI'}=$sqi;
$id=$data->insert(\%insert_data_hourly);

[download]

Here I am trying to read a particular date data in the excel sheet and insert it into MongoDB. Here for my understanding I printed the values (column 36 of that date present, date & row of that date) all are giving correct values 

97.327499999999986
15-06-2015
30

[download]

But the problem is in DB only. Its showing null in the place of data. 

{ "_id" : ObjectId("55cd825c783d4e53ee307201"), "date" : "06-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd82ed783d4e55b22035c1"), "date" : "02-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd83a6783d4e582a085531"), "date" : "06-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd83fa783d4e58555cf201"), "date" : "06-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd8457783d4e58c2363781"), "date" : "06-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd84de783d4e598e302711"), "date" : "06-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd8644783d4e5a0667b1b1"), "date" : "06-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd8684783d4e5a1e2ed1a1"), "date" : "15-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd86cb783d4e5a2c05b7b1"), "date" : "15-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd8acb783d4e5f8d272f71"), "date" : "13-08-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd8ae6783d4e5fa51b6e21"), "date" : "15-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd8b6c783d4e601d19b791"), "date" : "15-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd8c44783d4e604261e121"), "date" : "15-06-2015",
+ "SQI" : NumberLong(0) }
{ "_id" : ObjectId("55cd8cab783d4e6050106bc1"), "date" : "15-06-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cd8d08783d4e60653b84c1"), "date" : "27-05-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cdab74783d4e791c7b7531"), "date" : "13-08-2015",
+ "SQI" : null }
{ "_id" : ObjectId("55cdab96783d4e791f0321b1"), "date" : "15-06-2015",
+ "SQI" : null }

[download]

Note: The data field contains not the exact data its a formulae.  =SUMIF($C$14:$AH$14,"SubScore",C15:AH15) if there is any non-numeric values it shows #N/A. But even the values are displaying in the terminal the DB showing NULL. What is the problem???? Thanks for reply

Replies are listed 'Best First'.
Re: Problem with inserting data into MongoDB
by marto (Cardinal) on Aug 14, 2015 at 10:54 UTC

    "What is the problem????"

    Scoping. $sqi is defined in an if statement (within a for loop), and you expect to use it outside of this. See your previous question usage of "my" keyword. use strict; would have caught this, among other problems:

    Global symbol "$sqi" requires explicit package name at mongodb.pl line
+ 42.

    [download]

    Also Coping with Scoping.

    Update: slight rewording

[reply]
[d/l]
[select]
      #!/usr/bin/perl
#
use strict;
use warnings;
use MongoDB;
use MongoDB::OID;
use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;
use Spreadsheet::Read;
use Tie::IxHash;
my $date=$ARGV[0];

$date or $date=`date --date='1 day ago'  +%d-%m-%Y`;
chomp $date;

my $file_path="/root/prac/packages/SDP_VAS_NQI-17-06-2015.xlsx";
my $workbook = ReadData($file_path,cells => 0 );

if(defined $workbook->[0]{'error'})
{
    print "Error occurred while processing $file_path:".$workbook->[0]
+{'error'}."\n";
    exit(-1);
}

my $worksheet = $workbook->[1];
my $max_rows = $worksheet->{'maxrow'};
my $sqi;
my $cell_data=0;
for my $row_num (15..($max_rows))
{
    $cell_data  = $worksheet->{cell}[2][$row_num]; # same, unformatted
    if($date eq $cell_data)
    {
        $sqi  = $worksheet->{cell}[23][$row_num]; # same, unformatted
        print "$sqi\n";
        print "$cell_data\n";
        print "$row_num\n";
    }
}
my $db_host="172.16.15.115";
my $db_name="ravi";
my $client     = MongoDB::MongoClient->new(host => $db_host, port => 2
+7017);
my $database   = $client->get_database( $db_name );
my $data = $database->get_collection("SDP_SQI");
my %insert_data_hourly=();
$insert_data_hourly{'date'}=$cell_data;
$insert_data_hourly{'SQI'}=$sqi;
my $id=$data->insert(\%insert_data_hourly);

      [download]

      I updated as you guided. But it showing a repeated warning

       Use of uninitialized value $cell_data in string eq at ./excel_read.pl line 32.

      Its nearly repeating 35 to 40 times. Whats behind in it

[reply]
[d/l]
[select]

        Have you looked at line 32 in your code? What happens there? Have you inspected the values that are used on that line? What is in them? Perl comes with documentation for all its warnings in warnings. Maybe you should read them.

[reply]

        What does the message say? $cell_data is uninitialized. The assignment immediately preceding it is not finding a value. Perhaps those cells are empty?

        Dum Spiro Spero
[reply]

Back to Seekers of Perl Wisdom