Re: Re: The @$ array construct

Basically I have inherited a massive excel workbook. In one of the worksheets I have a column where I have server names and the next column I have user Ids like this;

server1 JohnB
server5 JohnB
server3 LisaG
Server2 LisaG
server7 AdamX
server1 Adamx
server3 PaulK
server5 PaulK

For each account name there is only one server where the users home drives are located, what I need to do is to:
1- Check which is the correct server.
2- Edit the excel sheet by inserting ok next to the correct server name or Del for the incorrect one.
So i get this results below;

server1 JohnB Ok
server5 JohnB Del
server3 LisaG Del
Server2 LisaG Ok
server7 AdamX Del
server1 Adamx Ok
server3 PaulK Ok
server5 PaulK Del

I have written a script that that checks which is the correct home server using combination of AdminMisc and Netadmin, but I need to do is get the script to edit this massive list with correct information rather than me doing it manually so I wrote this bit (or nicked it form someone here)

use strict 'vars';
use OLE;
use Win32::NetAdmin;
use Win32::AdminMisc;
my @Domains = ('Dom_UK','Dom_US');
my $xl = CreateObject OLE 'Excel.Application' || die $!;
my $workbook = $xl->Workbooks->Open("C:\\myfiles\\test.xls");
my $worksheet = $workbook->Worksheets(1);
my $xls = $worksheet->Range("A1:B10000")->{'Value'};
my @DataLst;
my $count=0;
for (@$xls)
{
    print ++$count." @$_\n";
}
[download]

Now, I need to have a hash where I have the following information
Row -> Row number, ID -> User ID, Server -> the server where the correct home drive is.
And push each record (or Hash) into and a one big array, so that I can loop this array, and access each record, check the user ID against the server is correct or not, if its correct then I use the row number (columns is constant and = 3) to insert ok or del. And that’s it. But I am not sure on how to split $@_ into two element $srv and $id, insert them into and array and loop through the array to access each item the amend the excel file with the findings. Any suggestions or Perls of wisdom (with and example or 2) are welcome

Many thanks indeed.

Comment on Re: Re: The @$ array construct Download Code

Replies are listed 'Best First'.
Re3: The @$ array construct by dragonchild (Archbishop) on Jun 27, 2003 at 18:21 UTC
Try this on for size: Build a hash that maps userid to servername. Iterate through the rows in your excel workbook. This will give you an array reference, each element of which is an array reference. Grab the userid element (let's say it's column F) and server (in G). Do a compare and you're done! `my %User_Server = MapUsersToServer(); my $Rows = $worksheet->SomeMethodICan'tRemember(); foreach my $row (@$Rows) { my ($user_id, $server) = ($row->[5], $row->[6]); if ($User_Server{$user_id} ne $server) { # Do something useful here. } }` [download] I hope that helps. ------ We are the carpenters and bricklayers of the Information Age. Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement. Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.	[reply] [d/l]
Re: Re3: The @$ array construct by blackadder (Hermit) on Jun 29, 2003 at 20:44 UTC
($row->5, $row->6); did it for me,....Many thanks cheers.	[reply]