Remove special character while reading file in perl

Question

I have an Excel Sheet (Test.xls) which has following content:

Name,account,flat Number,city
A,111111,1352†,XXXX
B,222222,2352†,YYYY
C,333333,3352†,YYYY

Special character is † in column 3.

Getting the following error when I ran the Perl script a.pl using perl 5.12.5 version.

Wide character in print at test.pl line 2.

But we do not get this error when I execute the same script with Perl 5.6.1 version.

Please can someone guide on how to ignore/remove this special character while reading the content of excel sheet through parseexcel module.

and though Perl 5.6.1, why this special character got ignored while fetching the content of file though parseexcel module.

    #!/Volumes/app/perl/5.12/bin/perl -w
    use Spreadsheet::ParseExcel;

    my $srce_file = "a.xls"; 
    my $oExcel = new Spreadsheet::ParseExcel;
    my $oBook = $oExcel->Parse($srce_file); 
    my %hah_sheet = ();
    my $header_row  = 1;
        my($iR, $iC, $oWkS, $oWkC);
        my $book = $oBook->{File};
        my $nsheet= $oBook->{SheetCount};
        my $author= $oBook->{Author};
        unless($nsheet){
            print "ERR>No worksheet found for source file:$srce_file\n";
            return 0;
        }
        else{
            print "INFO>Excel                         
           File=$srce_file,#WorkSheet=$nsheet,AuthorID=$author\n";
        }


   for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
    next if($iSheet >0);    
    $oWkS = $oBook->{Worksheet}[$iSheet];

    my $rows = 0;
    for(my $iR = $oWkS->{MinRow}; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
        $rows++;

        my $str_len = 0;
        for(my $iC = $oWkS->{MinCol}; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol}; $iC++) {
            $oWkC = $oWkS->{Cells}[$iR][$iC];
            next if ($iR <$header_row);
            
            if (defined($oWkC)){
                my $cell_value = $oWkC->Value;
                $cell_value =~s/\n+//g;               #removed newline inside the value
                #
                ##if the first column at header row is null then skip. Column might be shifted
                if($iR==$header_row && $iC == 0){
                    last unless($cell_value);
                }
                if($iR == $header_row){
                   $hah_sheet{$iR}{$iC} = uc($cell_value);
                }else {
                   $hah_sheet{$iR}{$iC} = $cell_value;
                   $str_len += length($cell_value);  
                   ##View cell value by row/column
                   print "DEBUG>row ${iR} - col ${iC}:$cell_value\n";
                }
            }else{
                $hah_sheet{$iR}{$iC} = "";              #keep position for NULL value
            }
        } # END of Column loop
    } # END of Row loop
} # END of Worksheet
      

Output with perl 5.12 version :

INFO>Excel File=a.xls,#WorkSheet=1,AuthorID=
DEBUG>row 2 - col 0:Name
DEBUG>row 2 - col 1:account
DEBUG>row 2 - col 2:flat Number
DEBUG>row 2 - col 3:city
DEBUG>row 3 - col 0:A
DEBUG>row 3 - col 1:111111
Wide character in print at ./a4_test.pl line 49.
DEBUG>row 3 - col 2:1352†
DEBUG>row 3 - col 3:XXXX
DEBUG>row 4 - col 0:B
DEBUG>row 4 - col 1:222222
Wide character in print at ./a4_test.pl line 49.
DEBUG>row 4 - col 2:2352†
DEBUG>row 4 - col 3:YYYY
DEBUG>row 5 - col 0:C
DEBUG>row 5 - col 1:333333
Wide character in print at ./a4_test.pl line 49.
DEBUG>row 5 - col 2:3352†
DEBUG>row 5 - col 3:YYYY

Output with perl 5.6.1 version : -

INFO>Excel File=a.xls,#WorkSheet=1,AuthorID=
DEBUG>row 2 - col 0:Name
DEBUG>row 2 - col 1:account
DEBUG>row 2 - col 2:flat Number
DEBUG>row 2 - col 3:city
DEBUG>row 3 - col 0:A
DEBUG>row 3 - col 1:111111
DEBUG>row 3 - col 2:1352
DEBUG>row 3 - col 3:XXXX
DEBUG>row 4 - col 0:B
DEBUG>row 4 - col 1:222222
DEBUG>row 4 - col 2:2352
DEBUG>row 4 - col 3:YYYY
DEBUG>row 5 - col 0:C
DEBUG>row 5 - col 1:333333
DEBUG>row 5 - col 2:3352
DEBUG>row 5 - col 3:YYYY

Answer 1

I know nothing of your data or what it means, but I'm generally against changing data to squash programming warnings. If the v5.6 version worked but v5.12 doesn't, you probably want to leave the data alone. (And, 5.6 is ancient and part of Perl's first dealings with Unicode. We didn't do a lot of things correctly and didn't recognize and warn on all situations we should have. Now we know more, so you get a warning on something that was already a problem).

This particular warning tells you that you didn't tell Perl how to encode output and it tried to print a "wide" (more than one octet) character. That often means the form of the output may not be what the destination expects (see mojibake).

A quick fix, assuming that you want UTF-8 output, is to tell Perl to encode its standard filehandles as UTF-8. A common way to do that is through the open pragma:

use open ':std', ':encoding(UTF-8)';

The end of recent versions of Learning Perl has a primer on Unicode and the various things you need to do to handle it correctly.

---

But let's say that you don't want the † (and you have a good reason for that beyond not getting a warning). Use can use Perl's normal text manipulation features, such as the substitution operator:

use utf8;  # tell Perl source is UTF-8
$column =~ s/†//g;

or, if you don't want to type the literal character, you can specify it by code number:

$column =~ s/\x{2020}//; # goodbye 2020