Re: [Spreadsheet::ParseExcel] How to remove white space from cells - Spreadsheet::ParseExcel

[Anupam Varma]

For white spaces you need to trim the value. Chomp will remove new line character from the end of the string.

On Sep 5, 2012 6:50 AM, "Tanscia" <rbmweb...@googlemail.com> wrote:

Hi,

I'd really appreciate some help.

I am using Spreadsheet::ParseExcel to search an excel spreadsheet for a specific entry (specified by the user).

I've created a hash with the data collected from the excel spreadsheet the key of which (an array) I use it to compare with the users entry and hey presto deliver the info required.

The problem I have is that the excel spreadsheet has white space mainly carriage returns which I need to chomp but I've tried several ways of doing this and none of them work!

Here's the part of the code which I'm trying to chomp


                if ($col == 0 || $col == 4 || $col == 9) {
               
                    #Collect hash key and assign to @key array
                    if ($col == 0) {
                        $key[$row] = $worksheet->get_cell($row, $col);
                    }

I've tried

                        chomp ($key[$row] = $worksheet->get_cell($row, $col));
                        chomp ($key[$row] = $key[$row]->{_Value});
                        chomp (@key);
                        chomp $key[$row]->{_Value};
                        chomp ($key[$row]);

I can't understand why I can print $key[$row]->{_Value}; but chomp $key[$row]->{_Value}; is ineffective.

I've basically tried every combination of chomp I can think of but the white spaces are still there!!!!

Thanks in advance for looking into this one.

Tanscia.

--
--
You received this message because you are subscribed to the Spreadsheet::ParseExcel Google Group.
 
For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-parseexcel?hl=en
 
You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-parseexcel?hl=en

[Tanscia]

Hi,

Thank you for your response.

I may not have been entirely clear I do need to remove the new line character from the end of the string sitting in $key[$row].

White space within the string or at the beginning if the string is not an issue.

[Anupam Varma]

I haven't worked on this for some time but I have these in my old script. Hope it works.

 

my StringVal = $worksheet->get_cell($row, $col));

StringVal = trim (StringVal);

 

Copy the below sub at the end of your script.

sub trim{

 my $string = shift;
 $string =~ s/^\s+//;
 $string =~ s/\s+$//;
 return $string;

[Tanscia]

IT WORKS, IT WORKS, IT WORKS! Thank you very much.

I had to change it slightly for my script

$key[$row] = trim ($key[$row]->{_Value});

instead of

$key[$row] = trim ($key[$row]);

but it works a treat thanks very much for your help with this one.

[Tanscia]

Just an additional note to the previous solution

In the end I used

$key[$row]->{_Value} = trim($key[$row]->{_Value});

The previous solution generated another error which again took some time to figure out was related to this line errrrgh!!!!!

[devnullius Plussed]

Strange this is so hard.

Glad you guys figured it out :)

But I am a complete stranger to this scripting.. I tried creating a new script, which immediately showed me already in-place code. I have *no* idea how to go forward now...

If someobody could post the script source I should use / create... That would be great! :)

Thank you?