Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Need Help in splitting the files

by documents9900 (Initiate)
on Jan 28, 2013 at 15:35 UTC ( #1015718=perlquestion: print w/ replies, xml ) Need Help??
documents9900 has asked for the wisdom of the Perl Monks concerning the following question:

I have three files of which I need to generate 6 worksheet of a workbook from these 3 files. Each file contains multiple rows and multiple columns. Example
C1|C2|C3 A|1|Y B|1|Z C|2|Q
This is first file in which second field is Key. With this info, I need to generate 2 worksheets. First worksheet will contain the duplicate information i.e first 2 records (second column is duplicate i.e 1). Second worksheet contains unique key information i.e C|2|Q will go in second worksheet.

Similary three files will result into six worksheets and it should be part of same xls file.

Please suggest

Comment on Need Help in splitting the files
Download Code
Re: Need Help in splitting the files
by Corion (Pope) on Jan 28, 2013 at 15:38 UTC

    Have you looked at perlfaq4 and what it has to say about finding duplicates?

Re: Need Help in splitting the files
by blue_cowdawg (Monsignor) on Jan 28, 2013 at 15:44 UTC

    Show us your best efforts and explain why they failed...


    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
Re: Need Help in splitting the files
by Anonymous Monk on Jan 28, 2013 at 16:08 UTC

        I have written the code and its working fine. Below is the code

        print "Fetch in Progress - Tables\n"; my $sth = $dbh->prepare($sql1) or die $dbh->errstr; $sth->execute() or die $dbh->errstr; my $tlast =''; open(INFILE1,">tempfile.txt"); open(INFILE2,">newtables.txt"); open(INFILE3,">updatetables.txt"); while (@data = $sth->fetchrow_array()) { print INFILE1 (@data[0]."\t".@data[1]."\t".@data[2]."\t".@data[3]); print INFILE1 ("\n"); } close INFILE1; open(INFILE1,"tempfile.txt"); my @contents=<INFILE1>; close INFILE1; open(INFILE1,"tempfile.txt"); while (<INFILE1>) { my @elements = split /\t/, $_; my $tc = @elements[0]."\t"; if ($tc ne $tclast) { my @filtered = grep /$tc/, @contents; if ($#filtered ==0) { print INFILE2 (@filtered);} else {print INFILE3 (@filtered);} } $tclast=$tc; } close INFILE1; close INFILE2; close INFILE3; print "Fetch in Progress - Column\n"; my $sth = $dbh->prepare($sql2) or die $dbh->errstr; $sth->execute() or die $dbh->errstr; my $tlast =''; open(INFILE1,">tempfile1.txt"); open(INFILE2,">newcolumn.txt"); open(INFILE3,">updatecolumn.txt"); while (@data = $sth->fetchrow_array()) { print INFILE1 (@data[0]."\t".@data[1]."\t".@data[2]."\t".@data[3]."\t" +.@data[4]."\t".@data[5]."\t".@data[6]."\t".@data[7]."\t".@data[8]."\t +".@data[9]."\t".@data[10]); print INFILE1 ("\n"); } close INFILE1; open(INFILE1,"tempfile.txt"); my @contents=<INFILE1>; close INFILE1; open(INFILE1,"tempfile.txt"); while (<INFILE1>) { my @elements = split /\t/, $_; my $tc = @elements[1]."\t".@elements[2]."\t"; if ($tc ne $tclast) { my @filtered = grep /$tc/, @contents; if ($#filtered ==0) { print INFILE2 (@filtered);} else {print INFILE3 (@filtered);} } $tclast=$tc; } close INFILE1; close INFILE2; close INFILE3; print "Fetch in Progress - Indexes\n"; my $sth = $dbh->prepare($sql3) or die $dbh->errstr; $sth->execute() or die $dbh->errstr; my $tlast =''; open(INFILE1,">tempfile.txt"); open(INFILE2,">newindexes.txt"); open(INFILE3,">updateindexes.txt"); while (@data = $sth->fetchrow_array()) { print INFILE1 (@data[0]."\t".@data[1]."\t".@data[2]."\t".@data[3]."\t" +.@data[4]); print INFILE1 ("\n"); } close INFILE1; open(INFILE1,"tempfile.txt"); my @contents=<INFILE1>; close INFILE1; open(INFILE1,"tempfile.txt"); while (<INFILE1>) { my @elements = split /\t/, $_; my $tc = @elements[0]."\t".@elements[1]."\t"; if ($tc ne $tclast) { my @filtered = grep /$tc/, @contents; if ($#filtered ==0) { print INFILE2 (@filtered);} else {print INFILE3 (@filtered);} } $tclast=$tc; } close INFILE1; close INFILE2; close INFILE3; print "Database retrieval completed\n"; # # Disconnect from database # $dbh->disconnect; print "Summary Sheet in Progress\n"; my $SummarySheet='Perl.xls'; my $workbook = Spreadsheet::WriteExcel->new($SummarySheet); $worksheet = $workbook->add_worksheet("New Tables"); $worksheet->set_column('A:C', 20); my $row_curr=1; open(INFILE1,"newtables.txt"); while (<INFILE1>) { my @elements = split /\t/, $_; $worksheet->write($row_curr, 0,@elements[0]); $row_curr=$row_curr+1; } close INFILE1; $worksheet = $workbook->add_worksheet("Modified Tables"); $worksheet->set_column('A:C', 20); my $row_curr=1; my $cnt=1; open(File3,"updatetables.txt"); while (<File3>) { my @elements = split /\t/, $_; if ($cnt%2==1) {$worksheet->write($row_curr, 0,"Rep1");} else {$worksheet->write($row_curr, 0,"Rep2");} $worksheet->write($row_curr, 1,@elements[0]); $cnt=$cnt+1; if ($cnt%2==1 and $cnt>1) {$row_curr=$row_curr+1;} $row_curr=$row_curr+1; } close INFILE1; $worksheet = $workbook->add_worksheet("New Columns"); $worksheet->set_column('A:C', 20); my $row_curr=1; open(INFILE1,"newcolumn.txt"); while (<INFILE1>) { my @elements = split /\t/, $_; $worksheet->write($row_curr, 0,@elements[1]); $worksheet->write($row_curr, 1,@elements[2]); $worksheet->write($row_curr, 2,@elements[3]); $row_curr=$row_curr+1; } close INFILE1; $worksheet = $workbook->add_worksheet("Modified Columns"); $worksheet->set_column('A:C', 20); my $row_curr=1; my $cnt=1; open(INFILE1,"updatecolumn.txt"); while (<INFILE1>) { my @elements = split /\t/, $_; if ($cnt%2==1) {$worksheet->write($row_curr, 0,"Rep1");} else {$worksheet->write($row_curr, 0,"Rep2");} $worksheet->write($row_curr, 1,@elements[1]); $worksheet->write($row_curr, 2,@elements[2]); $worksheet->write($row_curr, 3,@elements[3]); $cnt=$cnt+1; if ($cnt%2==1 and $cnt>1) {$row_curr=$row_curr+1;} $row_curr=$row_curr+1; } close INFILE1; $worksheet = $workbook->add_worksheet("New Indexes"); $worksheet->set_column('A:C', 20); my $row_curr=1; open(INFILE1,"newindexes.txt"); while (<INFILE1>) { my @elements = split /\t/, $_; $worksheet->write($row_curr, 0,@elements[0]); $worksheet->write($row_curr, 1,@elements[1]); $worksheet->write($row_curr, 2,@elements[2]); $worksheet->write($row_curr, 3,@elements[3]); $worksheet->write($row_curr, 4,@elements[4]); $row_curr=$row_curr+1; } close INFILE1; $worksheet = $workbook->add_worksheet("Modified Indexes"); $worksheet->set_column('A:C', 20); my $row_curr=1; my $cnt=1; open(INFILE1,"updateindexes.txt"); while (<INFILE1>) { my @elements = split /\t/, $_; if ($cnt%2==1) {$worksheet->write($row_curr, 0,"Rep1");} else {$worksheet->write($row_curr, 0,"Rep2");} $worksheet->write($row_curr, 1,@elements[0]); $worksheet->write($row_curr, 2,@elements[1]); $worksheet->write($row_curr, 3,@elements[2]); $worksheet->write($row_curr, 4,@elements[3]); $worksheet->write($row_curr, 5,@elements[4]); $cnt=$cnt+1; if ($cnt%2==1 and $cnt>1) {$row_curr=$row_curr+1;} $row_curr=$row_curr+1; } print "Completed"; close INFILE1; #remove temporaray files unlink updatecolumn.txt; unlink updateindexes.txt; unlink updatetables.txt; unlink newcolumn.txt; unlink newindexes.txt; unlink newtables.txt; unlink tempfile.txt; exit 0;
        This is working fine. Though I am not an expert in perl but i have taken the help of all pointers given above and wrote this.

        I wanted someone to review and check if it can improved. If there are lot of changes required then I would suggest to ignore this.

        Also, there may be few code which is relavant to my requirement while copying data into excel (some formatting done, inserting blank row and etc). So you can ignore that part

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1015718]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (7)
As of 2014-09-22 11:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (189 votes), past polls