Remove carriage return characters when using Perl module: Spreadsheet::WriteExcel

Posted on 2007-08-10
Last Modified: 2013-11-05
I'm using the following Perl script to create a spreadsheet from a text file. Basically the script works, but it's inserting a little square(carriage return character) as part of the $ACTIVITY field.


use strict;
use Spreadsheet::WriteExcel;

my $InputFile = "/nmltest/dailyops/data/Log.txt";
my $OutputFile = "//ho/dfs01/work/IS/Operations/GROUP0K/Automation/DailyActivities/Activities/Daily Activities test.xls";

open (INPUT, "$InputFile") or die "$InputFile $!";

my $workbook = Spreadsheet::WriteExcel->new($OutputFile);
my $worksheet = $workbook->add_worksheet();
$worksheet->write('A1', "LAN ID");
$worksheet->write('B1', "Activity");

my $row = 1;

while (<INPUT>)
my ($LANID, $ACTIVITY) = split(/\|/, $_);
$worksheet->write($row, 0, $LANID);
$worksheet->write($row, 1, $ACTIVITY);
close INPUT;

Question by:rkckjk
    LVL 17

    Accepted Solution

    Do every single one of them have it?

    If so, try adding after this line:
    my ($LANID, $ACTIVITY) = split(/\|/, $_);
    $ACTIVITY =~ s/.$//;
    LVL 2

    Author Comment

    Great that did it!!
    LVL 84

    Expert Comment

    If all lines end in "\r\n" then if you set $/="\r\n", the chomp would remove them both
    if not all lines end that way, then s/\r$// would remove the last character only if the line ended with \r
    you could also use  s/\s+$// to remove all whitespace at the end
    my ($LANID, $ACTIVITY) = split(/\s*$|\|/,);
    would not get whitespace at the end into  $ACTIVITY) at all
    you could also define a :crlf lI/O ayer on the open

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
    A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (,  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now