separate rows based on specific cell text

[zincyo]

Hey,

I'm trying to get a perl script working which would essentially go down a specific column in my spreadsheet (column D) and, baside on whether the text in the cell is "A" or "B," do something. If the text in the cell is "a" I want to remove the ENTIRE rown from the sheet, and add it to a new sheet.

Any help would be greatly appreciated!

 

[travs69]

By spreadsheet I'm assuming you mean a .xls file.. have you figured out how to read it in yet or is it actually a csv file?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those Who Say It Cannot Be Done Are Usually Interrupted by Someone Else Doing It

 

[zincyo]

yea I meant that it is a csv file, sorry for the confusion

 

[KevinADC]

what have you tried so far?

------------------------------------------
- Kevin, perl coder unexceptional!

 

[ishnid]

Best way to work with CSV data is to use either Text::CSV or Text::CSV_XS. Have a look on CPAN for them.

 

[zincyo]

Well, I have this code that I was using from an old script to remove the first column from a csv file. I figured maybe I could have it look through the rows, and say for example if this cell in column D = "a", then cut it and paste it into another sheet. If not, then continue down the column.

#!/usr/bin/perl
use strict;
use warnings;
use Text::CSV;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; # die on errors...

my $csv = Text::CSV->new();
my $columns;
my @headings;
while (<>) {
$csv->parse($_);
my @columns = ($csv->fields());
if ($. == 1) {
@headings = @columns;
next;
}
# my $i = pop(@columns);
# print join(' ', @columns), "\n";
}

print (@headings);

 

[stevexff]

I figured maybe I could have it look through the rows, and say for example if this cell in column D = "a", then cut it and paste it into another sheet. If not, then continue down the column.

Can't fault the design. Why don't you give it a go?


BTW, you still have the redundant my $columns; in your "old script" that I pointed out to you in your other thread...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[zincyo]

I'm not sure how to go about changing this code so that it looks for specific text.. have been looking and can't find the right code

 

[zincyo]

this is what I have so far, it doesn't work but maybe someone can point me in the right direction:
#!/usr/bin/perl -w

use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; # die on errors...

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');



my $csv = Text::CSV->new();
my $rows;
foreach my $row(1..11000) # (trying to have it search every row)
{
foreach my $col (4) # (Trying to have it only search column D)
{
next if $Sheet->Cells($row,$col)->{'Value'} == "MYVALUE",
$csv->parse($_);
my @rows = ($csv->fields());
my $i = pop(@rows);
print join(' ', @rows), "\n";
}
}