Solved

Parsing Excel Data

Posted on 2006-11-27
10
546 Views
Last Modified: 2008-02-01
I have been asking this question but due to the vague nature of the question I am not getting what exactly I am looking for.
I have come up with a mechanism how I want it and I will detail it down now. But to understand it you should open both links of the excel sheets.

http://socr.uwindsor.ca/~choudh8/output.xls
http://socr.uwindsor.ca/~choudh8/dummy.xls

As you see in the output.xls some data is sitting there. Please notice for every IPD file path there is a data. It Contents, Number of Records and Size.
Notice in the other file dummy.xls Contents are already there. All I need is to transfer these records and file sizes for every file. With the file name on top and its data underneath and then it moves towards right
Here is the Mechanism
IF{ @ column A of output file the script sees a path to IPD file
GO TO {dummy.xls on column B first row and copy/paste the IPD file name and in the next row copy/paste (Number of Records and Size)
GO back to output.xls
Match the contents underneath this IPD file
IF {the contents match place its data in front of the matched contents}
ELSE{try to match the next content (If it macths place the data otherwise move forward}
I guess    FOR LOOP    will be used
Do this process until the script hits the BLANK row (as seen in the output.xls file)
Go back to the first IF statement and do the exactly same process.
THE ONLY DIFFERENCE WILL BE THIS TIME THE IPD FILE NAME SHOULD BE COPY/PASTED TO THE NEXT COLUMN AND SO ON UNTIL THERE ARE IS NO DATA LEFT IN THE OUTPUT.XLS
To further understand the last point suppose the first IPD data copy/pasted in from Column B to C then next data should be from Column E to F and so on.
0
Comment
Question by:Musaab1
  • 5
  • 5
10 Comments
 
LVL 84

Accepted Solution

by:
ozo earned 500 total points
ID: 18027097
#is this what you mean?
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel::Simple;
my $oExcel = new Spreadsheet::ParseExcel;
my $contents=$oExcel->Parse('dummy.xls');
@C=map {[map{$_&&$_->{Val}}@$_]} @{$contents->{Worksheet}[0]{Cells}};
@row{map{$_->[0]=~/(\S.*\S)/}@C}=0..$#C;
my $log=$oExcel->Parse('output.xls');
for( @{$log->{Worksheet}[0]{Cells}} ){
    push @{$C[0]},$arg = $_->[0]{Val},undef if @{$_}==1;
    my($c,$n,$s)=map{$_&&$_->{Val}}@$_;
    s/^\s+//,s/\s+$// for $c;
    @{$C[$row{$c}]}[$#{$C[0]}-1,$#{$C[0]}] = $n,$s if $row{$c};
}
my $ss = Spreadsheet::WriteExcel::Simple->new;

$ss->write_row($_) for @C;
$ss->save("new.xls");
0
 

Author Comment

by:Musaab1
ID: 18027662
I am trying to test this code and it says that
Can't locate spreadsheet/writeexcel/simple.pm in @INC contains: c:/perl/lib c:/perl/site/lib .) at test1.pl line 3
BEGIN failed--compilation aborted at test1.pl line 3

Thanks a lot OZO for helping
0
 

Author Comment

by:Musaab1
ID: 18029607
Hello OZO
I placed the simple.pm in the spreadsheet/writeexcel loaction but now I see this

Can't locate object method "new" via package "spreadsheet::writeexcel::simple.pm" (
perhaps you forgot to load "spreadsheet::writeexcel::simple.pm"? at test1.pl line 15
0
 
LVL 84

Expert Comment

by:ozo
ID: 18032346
0
 

Author Comment

by:Musaab1
ID: 18032514
Hello Ozo I totally agree wiht your code as you are the master in perl but i dont know what should I do in this situation when I am getting this error even though everythign seems fine.

Thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 84

Expert Comment

by:ozo
ID: 18032893
did you install Spreadsheet::WriteExcel::Simple?
try the command
cpan Spreadsheet::WriteExcel::Simple
0
 

Author Comment

by:Musaab1
ID: 18038528
its working now OZo but now I am getting this
It runs and then stops with this comment
Modification of read-only value attempted at  test.pl line 5
0
 
LVL 84

Expert Comment

by:ozo
ID: 18042469
Is dummy.xls the same as the one you posted?
What do you get from

use Spreadsheet::ParseExcel;
my $oExcel = new Spreadsheet::ParseExcel;
my $contents=$oExcel->Parse('dummy.xls');
use Data::Dumper;
print Dumper map{ref} @{$contents->{Worksheet}[0]{Cells}};
print Dumper map {[map{ref}@$_]} @{$contents->{Worksheet}[0]{Cells}};
0
 
LVL 84

Expert Comment

by:ozo
ID: 18042503
and what do you get from
print Dumper map {[map{$_&& join', ',keys %$_}@$_]} @{$contents->{Worksheet}[0]{Cells}};
0
 

Author Comment

by:Musaab1
ID: 18042509
Thanks for ur help Ozo I am closing this question too I wrote a macro to do this job
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Reading fields from the text file. 4 92
PERL variable conundrum 9 89
Get a Perl script to return content from a module 7 78
quoting a comma separated list 20 82
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 …
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
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…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

861 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

27 Experts available now in Live!

Get 1:1 Help Now