Print Cell Value, based on Input 1

[zincyo]

I am trying to write a script which will take the users input, and then look to match that input in a specific column. THen once that input is found in the column, I want the script to print the values of two different cells in that row.

For example, if the data is in cell C4, I want the script to print what is in cells j4, W4 and x4.

I know I need to parse the file, but i'm not sure how to go about telling it to print the cells in these specific columns.

Any help is greatly appreciated!

 

[KevinADC]

Lets ee what code you have so far. What does the file look like?

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

 

[zincyo]

The file is a regular excel CSV file, with information in many of the columns. Here is the code I have so far, which doesn't work.

#!/usr/bin/perl -w
use strict;
use Spreadsheet:arseExcel;

my $FILE = "c/location/Works3.csv";
#my $SHEETNAME = $Book->Worksheets("Works3");

# the column that contains searchable key
my $KEY_COLUMN = 2;

my $searchstring = $ARGV[0];

my $excel = Spreadsheet:arseExcel::Workbook->Parse($FILE);
my $sheet = $excel->Worksheets("Works3");

foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow})
{
my $key = $sheet->Cell($row,$KEY_COLUMN);

if($key)
{
my $f1 = $sheet->Cell($row,4);
my $f2 = $sheet->Cell($row,5);
my $f3 = $sheet->Cell($row,7);

if($key->Value() =~ m/$searchstring/)
{
print "\n\n";
print "Key: " . $key->Value() . "\n";
print "Field 1: " . $f1->Value() . "\n" if($f1);
print "Field 2: " . $f2->Value() . "\n" if($f2);
print "Field 3: " . $f3->Value() . "\n" if($f3);
print "\n\n";
}
}
}

 

[KevinADC]

What does it do? Anything? Are you sure this is what you want to do to find a match?

if($key->Value() =~ m/$searchstring/)

that would be a case sensitive partial match of whatever $key->Vaue() returns.



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

 

[zincyo]

As of now it doesn't run-- As far as finding a match goes, I need it to ask the user for input, and then go down column C and search for that input, then print out the values in that row in columns J, W, and X... Not exactly sure how to do it.

 

[zincyo]

This is my newer version of the code, which does compile:

#!/usr/bin/perl -w
use strict;
use Spreadsheet:arseExcel;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on errors...

print "Please Enter mSym:" ;
my $x = <STDIN> ;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
# open Excel file
my $Book = $Excel->Workbooks->Open('Works3.csv');

my $Sheet = $Book->Worksheets("Works3");

# the column that contains searchable key
my $KEY_COLUMN = 3;



my $excel = Spreadsheet:arseExcel::Workbook->Parse($Book);
#my $sheet = $excel->Worksheets("Works3");

foreach my $row (1..70)
{
my $key = $Sheet->Cells($row,$KEY_COLUMN);

if($key)
{
my $f1 = $Sheet->Cells($row,9);
my $f2 = $Sheet->Cells($row,22);
my $f3 = $Sheet->Cells($row,23);

if($key->Value() =~ m/$x/)
{
print "\n\n";
print "Key: " . $key->Value() . "\n";
print "Field 1: " . $f1->Value() . "\n" if($f1);
print "Field 2: " . $f2->Value() . "\n" if($f2);
print "Field 3: " . $f3->Value() . "\n" if($f3);
print "\n\n";
}
}
}

When it asks for the mSym, it is supposed to go down column C, search for the mSYM entered, and then spit out the values of the values in columns J,W, and X, for the row found by the mSYM. If anyone sees why this may not be working correctly, please let me know!

 

[MillerH]

You need to chomp $x after inputting from <STDIN>. It will contain a return character and that is keeping it from every matching the value contained in your "special key"

- Miller

 

[zincyo]

it's funny you should mention that, I just figured it out!

Thanks for your help.

my $x = <STDIN> ;
chop $x;

in case anyone was interested.

 

[MillerH]

How I would suggest your process this file:

[gray]#!/usr/bin/perl -w[/gray]

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]Tie::File[/green][red];[/red]
[black][b]use[/b][/black] [green]Text::CSV_XS[/green][red];[/red]

[black][b]use[/b][/black] [green]strict[/green][red];[/red]
[black][b]use[/b][/black] [green]warnings[/green][red];[/red]

[gray][i]# Constants[/i][/gray]
[url=http://perldoc.perl.org/functions/our.html][black][b]our[/b][/black][/url] [blue]$csvfile[/blue] = [red]'[/red][purple]Work3.csv[/purple][red]'[/red][red];[/red]
[black][b]our[/b][/black] [blue]$searchkey[/blue] = [url=http://perldoc.perl.org/functions/ord.html][black][b]ord[/b][/black][/url][red]([/red][red]'[/red][purple]c[/purple][red]'[/red][red])[/red] - [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]a[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]our[/b][/black] [blue]$field1key[/blue] = [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]j[/purple][red]'[/red][red])[/red] - [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]a[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]our[/b][/black] [blue]$field2key[/blue] = [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]w[/purple][red]'[/red][red])[/red] - [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]a[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]our[/b][/black] [blue]$field3key[/blue] = [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]x[/purple][red]'[/red][red])[/red] - [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]a[/purple][red]'[/red][red])[/red][red];[/red]

[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]"[/red][purple]Please Enter mSym:[/purple][red]"[/red] [red];[/red]
[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$searchField[/blue] = <STDIN>[red];[/red]
[url=http://perldoc.perl.org/functions/chomp.html][black][b]chomp[/b][/black][/url] [blue]$searchField[/blue][red];[/red]

[url=http://perldoc.perl.org/functions/tie.html][black][b]tie[/b][/black][/url] [black][b]my[/b][/black] [blue]@array[/blue], [red]'[/red][purple]Tie::File[/purple][red]'[/red], [blue]$csvfile[/blue] or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]Can't open [blue]$csvfile[/blue]: [blue]$![/blue][/purple][red]"[/red][red];[/red]
[black][b]my[/b][/black] [blue]$csv[/blue] = Text::CSV_XS->[maroon]new[/maroon][red]([/red][red])[/red][red];[/red]

[olive][b]foreach[/b][/olive] [black][b]my[/b][/black] [blue]$row[/blue] [red]([/red][blue]@array[/blue][red])[/red] [red]{[/red]
	[blue]$csv[/blue]->[maroon]parse[/maroon][red]([/red][blue]$row[/blue][red])[/red] or [black][b]die[/b][/black] [red]"[/red][purple]parse() failed: [/purple][red]"[/red] . [blue]$csv[/blue]->[maroon]error_input[/maroon][red]([/red][red])[/red][red];[/red]
	[black][b]my[/b][/black] [blue]@data[/blue] = [blue]$csv[/blue]->[maroon]fields[/maroon][red]([/red][red])[/red][red];[/red]

	[olive][b]if[/b][/olive] [red]([/red][blue]$searchkey[/blue] <= [blue]$#data[/blue] && [blue]$data[/blue][red][[/red][blue]$searchkey[/blue][red]][/red] =~ [red]m/[/red][purple][purple][b]\Q[/b][/purple][blue]$searchField[/blue][purple][b]\E[/b][/purple][/purple][red]/[/red][red])[/red] [red]{[/red]
		[black][b]print[/b][/black] [red]"[/red][purple][purple][b]\n[/b][/purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple]Key: [/purple][red]"[/red] . [blue]$data[/blue][red][[/red][blue]$searchkey[/blue][red]][/red] . [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple]Field 1: [/purple][red]"[/red] . [blue]$data[/blue][red][[/red][blue]$field1key[/blue][red]][/red] . [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red] [olive][b]if[/b][/olive] [blue]$field1key[/blue] <= [blue]$#data[/blue][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple]Field 2: [/purple][red]"[/red] . [blue]$data[/blue][red][[/red][blue]$field2key[/blue][red]][/red] . [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red] [olive][b]if[/b][/olive] [blue]$field2key[/blue] <= [blue]$#data[/blue][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple]Field 3: [/purple][red]"[/red] . [blue]$data[/blue][red][[/red][blue]$field3key[/blue][red]][/red] . [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red] [olive][b]if[/b][/olive] [blue]$field3key[/blue] <= [blue]$#data[/blue][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple][purple][b]\n[/b][/purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
	[red]}[/red]
[red]}[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.8.8) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[li]warnings - Perl pragma to control optional warnings[/li]
[/ul]
Core (perl 5.8.8) Modules used :
[ul]
[li]Tie::File - Access the lines of a disk file via a Perl array[/li]
[/ul]
Other Modules used :
[ul]
[li]Text::CSV_XS[/li]
[/ul]
[/tt]

- Miller

 

[zincyo]

I don't think I have tie::file

 

[MillerH]

Tie::File is a core library, although I do not know long it's been included as part of the perl installation. Either way, it's just a means to an end, namely the potential desire to want to edit the file back. You haven't stated that this is an eventual goal, but it's so easy to use that module it doesn't hurt to add it in from the very beginning regardless of whether you need to edit.

Either way, it could just as easily have been translated to use following. The point was treating a csv file as an excel file is superfluous.

Not only that, but you use Win32::OLE in addition to Spreadsheet:arseExcel. Spreadsheet:arseExcel would also be more than sufficient, but I would advise you to treat a csv file like a csv file.

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]Text::CSV_XS[/green][red];[/red]

[black][b]use[/b][/black] [green]strict[/green][red];[/red]
[black][b]use[/b][/black] [green]warnings[/green][red];[/red]

[gray][i]# Constants[/i][/gray]
[url=http://perldoc.perl.org/functions/our.html][black][b]our[/b][/black][/url] [blue]$csvfile[/blue] = [red]'[/red][purple]Work3.csv[/purple][red]'[/red][red];[/red]
[black][b]our[/b][/black] [blue]$searchkey[/blue] = [url=http://perldoc.perl.org/functions/ord.html][black][b]ord[/b][/black][/url][red]([/red][red]'[/red][purple]c[/purple][red]'[/red][red])[/red] - [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]a[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]our[/b][/black] [blue]$field1key[/blue] = [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]j[/purple][red]'[/red][red])[/red] - [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]a[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]our[/b][/black] [blue]$field2key[/blue] = [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]w[/purple][red]'[/red][red])[/red] - [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]a[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]our[/b][/black] [blue]$field3key[/blue] = [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]x[/purple][red]'[/red][red])[/red] - [black][b]ord[/b][/black][red]([/red][red]'[/red][purple]a[/purple][red]'[/red][red])[/red][red];[/red]

[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]"[/red][purple]Please Enter mSym:[/purple][red]"[/red] [red];[/red]
[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$searchField[/blue] = <STDIN>[red];[/red]
[url=http://perldoc.perl.org/functions/chomp.html][black][b]chomp[/b][/black][/url] [blue]$searchField[/blue][red];[/red]

[black][b]my[/b][/black] [blue]$csv[/blue] = Text::CSV_XS->[maroon]new[/maroon][red]([/red][red])[/red][red];[/red]

[url=http://perldoc.perl.org/functions/open.html][black][b]open[/b][/black][/url][red]([/red]FILE, [blue]$csvfile[/blue][red])[/red] or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]Can't open [blue]$csvfile[/blue]: [blue]$![/blue][/purple][red]"[/red][red];[/red]

[olive][b]while[/b][/olive] [red]([/red]<FILE>[red])[/red] [red]{[/red]
	[blue]$csv[/blue]->[maroon]parse[/maroon][red]([/red][blue]$_[/blue][red])[/red] or [black][b]die[/b][/black] [red]"[/red][purple]parse() failed: [/purple][red]"[/red] . [blue]$csv[/blue]->[maroon]error_input[/maroon][red]([/red][red])[/red][red];[/red]
	[black][b]my[/b][/black] [blue]@data[/blue] = [blue]$csv[/blue]->[maroon]fields[/maroon][red]([/red][red])[/red][red];[/red]

	[olive][b]if[/b][/olive] [red]([/red][blue]$searchkey[/blue] <= [blue]$#data[/blue] && [blue]$data[/blue][red][[/red][blue]$searchkey[/blue][red]][/red] =~ [red]m/[/red][purple][purple][b]\Q[/b][/purple][blue]$searchField[/blue][purple][b]\E[/b][/purple][/purple][red]/[/red][red])[/red] [red]{[/red]
		[black][b]print[/b][/black] [red]"[/red][purple][purple][b]\n[/b][/purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple]Key: [/purple][red]"[/red] . [blue]$data[/blue][red][[/red][blue]$searchkey[/blue][red]][/red] . [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple]Field 1: [/purple][red]"[/red] . [blue]$data[/blue][red][[/red][blue]$field1key[/blue][red]][/red] . [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red] [olive][b]if[/b][/olive] [blue]$field1key[/blue] <= [blue]$#data[/blue][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple]Field 2: [/purple][red]"[/red] . [blue]$data[/blue][red][[/red][blue]$field2key[/blue][red]][/red] . [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red] [olive][b]if[/b][/olive] [blue]$field2key[/blue] <= [blue]$#data[/blue][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple]Field 3: [/purple][red]"[/red] . [blue]$data[/blue][red][[/red][blue]$field3key[/blue][red]][/red] . [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red] [olive][b]if[/b][/olive] [blue]$field3key[/blue] <= [blue]$#data[/blue][red];[/red]
		[black][b]print[/b][/black] [red]"[/red][purple][purple][b]\n[/b][/purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
	[red]}[/red]
[red]}[/red]

[url=http://perldoc.perl.org/functions/close.html][black][b]close[/b][/black][/url][red]([/red]FILE[red])[/red][red];[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.8.8) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[li]warnings - Perl pragma to control optional warnings[/li]
[/ul]
Other Modules used :
[ul]
[li]Text::CSV_XS[/li]
[/ul]
[/tt]

- Miller