Re^2: Parsing Data in xlsx file

[Rahul Gupta]

Hi Guys, I have this data in text file

------|----------|--------|-------------------------|--------|
 S.No | TimeStamp| UE-Name| Test-Config             | UL/DL  |
------|----------|--------|-------------------------|--------|
 1    | 18:24:38 | -NA-   | Sanity_001_0001_10_00   | System |
------|----------|--------|-------------------------|--------|
 2    | 18:31:34 | ueh1   | Sanity_002_0002_00_11   | UL     |
------|----------|--------|-------------------------|--------|
 3    | 18:23:48 | ueh1   | Sanity_003_0003_06_09   | UL     |
------|----------|--------|-------------------------|--------|
 3    | 18:23:48 | ueh1   | Sanity_003_0003_06_09   | DL     |
------|----------|--------|-------------------------|--------|
 4    | 18:38:30 | ueh1   | Sanity_004_0004_24_00   | DL     |
------|----------|--------|-------------------------|--------|
 5    |  -NA-    | -NA-   | Sanity_001_0001_10_00   | System |
------|----------|--------|-------------------------|--------|
 6    | 18:31:34 | ueh2   | Sanity_002_0002_00_11   | UL     |
------|----------|--------|-------------------------|--------|
 7    | 18:23:49 | ueh2   | Sanity_003_0003_06_09   | UL     |
------|----------|--------|-------------------------|--------|
 7    | 18:23:48 | ueh2   | Sanity_003_0003_06_09   | DL     |
------|----------|--------|-------------------------|--------|
 8    | 18:38:30 | ueh2   | Sanity_004_0004_24_00   | DL     |
------|----------|--------|-------------------------|--------|

i have tried this thing all text file data is in @executioncontents array

 my %data;
    
    foreach (@executioncontents) {
        my $currLine = $_;
        my @Report = split(/\|/, $currLine);
        my $arraysize = $#Report;
        if ($arraysize == 11) {
            foreach (@Report) {
                if ($_ =~ m/--/) {
                }
                else {
                    my $timestamp = $Report[1];
                    my $testcase = $Report[4];
                    my $uename = $Report[2];
                    my $keyvalues =$timestamp."_".$uename."_".$testcas
+e;
                    $data{$keyvalues}{serial_number} = $Report[0];
                    $data{$keyvalues}{time_Stamp} = $Report[1];
                    $data{$keyvalues}{ue_name} = $Report[2];
                    $data{$keyvalues}{test_config} = $Report[3];
                    $data{$keyvalues}{ul_dl} = $Report[4];
                    $data{$keyvalues}{status} = $Report[5];
                    $data{$keyvalues}{jiter} = $Report[6];
                    $data{$keyvalues}{throughput} = $Report[7];
                    $data{$keyvalues}{lost} = $Report[8];
                    $data{$keyvalues}{bandwidth} = $Report[9];
                    $data{$keyvalues}{duration} = $Report[10];
                    $data{$keyvalues}{failed_reason} = $Report[11];
                    
                }   
            }
        }
    }
readhash(\%data);
sub readhash {
    
    my $reff = shift;
    my %xyz = %$reff;
    my $workbook = Excel::Writer::XLSX->new( "Data");    
    my $worksheet = $workbook->add_worksheet("data");
    my @array;
    my $rows  = 14;
    foreach my $key (sort %xyz) {
        if ($xyz{$key}{ue_name} =~ m/ue|NA/)
        {
            push(@array, $xyz{$key}{ue_name});
            $worksheet->write($rows, 0, $xyz{$key}{serial_number});
            $worksheet->write($rows, 1, $xyz{$key}{time_Stamp});
            $worksheet->write($rows, 2, $xyz{$key}{ue_name});
            $worksheet->write($rows, 3, $xyz{$key}{test_config});
            $worksheet->write($rows, 4, $xyz{$key}{ul_dl});
            $worksheet->write($rows, 5, $xyz{$key}{status});
            $worksheet->write($rows, 6, $xyz{$key}{jiter});
            $worksheet->write($rows, 7, $xyz{$key}{throughput});
            $worksheet->write($rows, 8, $xyz{$key}{lost});
            $worksheet->write($rows, 9, $xyz{$key}{bandwidth});
            $worksheet->write($rows, 10, $xyz{$key}{duration});
            $worksheet->write($rows, 11, $xyz{$key}{failed_reason});
            $rows++;
        }
    }
}

It parse the data in xlsx file in differnt manner from the way it is in acutal file

 5          -NA-         -NA-        Sanity_001_0001_10_00        Syst
+em 
 3         18:23:48      ueh1        Sanity_003_0003_06_09        DL  
+   
 3         18:23:48      ueh1        Sanity_003_0003_06_09        UL  
+   
 7         18:23:48      ueh2        Sanity_003_0003_06_09        DL  
+   
 7         18:23:49      ueh2        Sanity_003_0003_06_09        UL  
+   
 1         18:24:38      -NA-        Sanity_001_0001_10_00        Syst
+em 
 2         18:31:34      ueh1        Sanity_002_0002_00_11        UL  
+   
 6         18:31:34      ueh2        Sanity_002_0002_00_11        UL  
+   
 4         18:38:30      ueh1        Sanity_004_0004_24_00        DL  
+   
 8         18:38:30      ueh2        Sanity_004_0004_24_00        DL  
+   
 S.No      TimeStamp     UE-Name     Test-Config                  UL/D
+L

Please anyone can help me to parse the data in xlsx file the way it is in actal file.

Thanks in advance