How to write the contents of the text file into Excel sheet-CodePudding

The text file input.txt has the following columns which are tab delimited

result_1:test_a result_2:test_b result_1_res:pass result_2_res:fail result_1_str= result_2_str=err result_1_t: result_2_t:100 string: wrong

There are some 1000 rows like this and I need to print these lines in Excel sheet as follows

test_a test_b pass fail  err  100 wrong

The code I have written is as follows

use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('out.xls');
my $worksheet = $workbook->add_worksheet();

$worksheet->write("A1","Table");
$worksheet->write("A2","result_1");
$worksheet->write("B2","result_2");
$worksheet->write("C2","result_1_res");
$worksheet->write("D2","result_2_res");
$worksheet->write("E2","result_1_str");
$worksheet->write("F2","result_2_str");
$worksheet->write("G2","result_1_t");
$worksheet->write("H2","result_2_t");
$worksheet->write("I2","Comment");

open my $c, "<input.txt" or die "Cannot open:$!";

while (my $c_r = <$c>) {
    chomp;
    my @arr = split ("\t", $c_r);
    chomp(my $test_name1 = $arr[0]);
    chomp(my $test_name2 = $arr[1]);
    chomp(my $res_name1 = $arr[2]);
    chomp(my $res_name2 = $arr[3]);
    chomp(my $sign_name1 = $arr[4]);
    chomp(my $sign_name2 = $arr[5]);
    chomp(my $time_name1 = $arr[6]);
    chomp(my $time_name2 = $arr[7]);
    chomp(my $comment = $arr[8]);
    ($l0,$l1) = split(":",$test_name1);
    ($l2,$l3) = split(":",$test_name2);
    ($l4,$l5) = split(":",$res_name1);
    ($l6,$l7) = split(":",$res_name2);
    ($l8,$l9) = split("=",$sign_name1);
    ($l10,$l11) = split("=",$sign_name2);
    ($l12,$l13) = split(":",$time_name1);
    ($l14,$l15) = split(":",$time_name2);
    ($l16,$l17) = split(":",$comment);


        }


$workbook->close;

CodePudding user response：

Here I have modified your script. See if this works for you.

#! /usr/bin/perl                                                                                                                      
use strict; use warnings; 

use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('out.xls');
my $worksheet = $workbook->add_worksheet();

$worksheet->write("A1","Table");
$worksheet->write("A2","result_1");
$worksheet->write("B2","result_2");
$worksheet->write("C2","result_1_res");
$worksheet->write("D2","result_2_res");
$worksheet->write("E2","result_1_str");
$worksheet->write("F2","result_2_str");
$worksheet->write("G2","result_1_t");
$worksheet->write("H2","result_2_t");
$worksheet->write("I2","Comment");

my $filename = 'input.txt';
open(my $fh, '<:encoding(UTF-8)', $filename)
  or die "Could not open file '$filename' $!";

my $row = 2; my $col = 0;

while (my $each_row = <$fh>) {
  chomp $each_row;
  my @contents = split(" ", $each_row);
  my @result1_name = split(":", $contents[0]); $worksheet->write($row,$col  , $result1_name[1]);
  my @result2_name = split(":", $contents[1]); $worksheet->write($row,$col  , $result2_name[1]);
  my @result1 = split(":", $contents[2]);      $worksheet->write($row,$col  , $result1[1]);
  my @result2 = split(":", $contents[3]);      $worksheet->write($row,$col  , $result2[1]);
  my @result1_str = split("=", $contents[4]);  $worksheet->write($row,$col  , $result1_str[1]);
  my @result2_str = split("=", $contents[5]);  $worksheet->write($row,$col  , $result2_str[1]);
  my @result1_t = split(":", $contents[6]);    $worksheet->write($row,$col  , $result1_t[1]);
  my @result2_t = split(":", $contents[7]);    $worksheet->write($row,$col  , $result2_t[1]);
  my @comment = split(":", $contents[8]);      $worksheet->write($row,$col  , $comment[1]);
  $row  ; $col=0;
}

$workbook->close;

Result:

Note: I have not considered the space between : and wrong in string: wrong of your input.txt.