The SQL "GROUP BY" clause can do the pivot functions to summarize.

The result can then easily export into a csv file suitable for import into Excel.

Hello, Do you have any specific example of pulling the raw data into DB? Please check below how i am pulling raw data and let me know next step if possible. How i should import into csv and then excel if possible.

my $urlstringlog = $apiurllog . $sortor . $filter . $target . $appID .
+ $date2;

$strExcelFilename = "C:/" . "Analysis-Details-automatic-bk" . $date . 
+".xlsx";

do {

  # Run get the users
  run_api_call($urlstringlog);
  @responsetext = parse_json ($client->responseContent());
  push @responsetextall, @responsetext;
  $linkheader =  $client->responseHeader("link");
  if ($linkheader=~ m/next/) {
  (my $link1 = $linkheader) =~ s/ .*? (self)/$1/gx;
  (my $link2 = $link1) =~ s/self/$1/g;
   print "Post Pagination: $link10";
   $urlstringlog = $link2;
  }
  } while ($linkheader=~ m/next/);
     for my $i (0..$#responsetextall) {
  
     $responsetextall[$i] =~ s/]\[/,/g;
 
        for my $j (0..$#{$responsetextall[$i]}) {
             @responsests1 = $responsetextall[$i][$j]{tat};    
             $responseid = $responsetextall[$i][$j]{act}{id};
             $responsdisp = $responsetextall[$i][$j]{act}{Name};
             $responsalter = $responsetextall[$i][$j]{act}{Id};
             $responseclientipadd = $responsetextall[$i][$j]{cln}{ipAd
+};
             $responseappsdebug = $responsetextall[$i][$j]{deb}{Data};
+    
            
    for  my $m (0..$#responsests1) {
            for my $n (0..$#{$responsests1[$m]}) {
            $responseapps2id = $responsests1[$m][$n]{id};
            $responseapps2 = $responsests1[$m][$n]{ty};
            $responseapps1 = $responsests1[$m][$n]{Name};
                    
                if ( $responseapps2 eq 'AppInstance' ){
                 
                 @responseapps3 = $responsests1[$m][$n]{displayName};
                $responseapps5 = $responseapps3[0];
                 print "Its Matches Appinstances";
                 $responseapps4 = $responsests1[$m][$n]{Name};
                             
                                    
                        if ( $responseapps2 eq 'AppUser'){
                            
                            $responseapps3id = $responsests1[$m][$n]{a
+Id};
                        }
        
             
                }
                                
                      
      push @responseapps3id,$responseapps3id;    
      push @responseapps5 , $responseapps4;
      push @responseapps7, $responseapps7;
                
                
                        }
                        
                    
                        
                        }
                        
                        push @responsalter, $responsalter;
                          push @responseclientipadd,$responseclientipa
+dd;
                           
                    }
                
             }
[download]

Do you have any specific example of pulling the raw data into DB?

I've not had cause to use Sqlite as I usually have another DB available. But this is the sort of thing you need. Untested and without checking for errors creating connections and opening files etc. Hopefully it will give you a start.

use DBI;
use DBD::Sqlite;

# Connect to your database
my $dbh = DBI->connect("dbi:sqlite:$db_name:localhost:$port", $db_user
+, $db_password);

# Create a temporary table
$dbh->do("CREATE TEMPORARY TABLE IF NOT EXISTS ExcelData (name VARCHAR
+(80), data INT)");

# Populate temporary table from your datasource
foreach my $row(@responsetext) {
    my ($name, $data) = split / +/, $row
    $dbh->do("INSERT INTO ExcelData SET name = '$name', data = $data")
+;
}

# Create a query to sort the data how you want it and output to CSV fi
+le
open $fh, '>', 'myfile.csv';
my $query = $dbh->prepare("SELECT name, data FROM ExcelData WHERE data
+ > 20 ORDER BY data");
$query->exceute;
my ($n, $d);
while (($n, $d) = $query->fetchrow_array) {
    print $fh qq["$n",$d\n];
}
close $fh;
[download]