Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Just another Perl shrine
 
PerlMonks  

Re: Need Help in splitting the files

by Anonymous Monk
on Jan 28, 2013 at 16:08 UTC ( #1015724=note: print w/ replies, xml ) Need Help??


in reply to Need Help in splitting the files

Please suggest

Search, it is for everybody Use Text::CSV or Text::xSV

Examples at

 [CSV hash ] CSV hash
Best way to match a hash with large CSV file
perl hash to CSV using Text::CSV_XS
Issue parsing CSV into hashes?
Veriable Length Array/Hash derived from CSV to populate an XML
extracting data from CSV files and making hash of hashes
Re^2: build hash from csv file
Encoding a hash in perl before saving it as a CSV file
hash from CSV-like structure
Read the csv file to a hash....
Parsing CSV into a hash
build hash from csv file
Converting a CSV list to a list of hashrefs naming the fields


Comment on Re: Need Help in splitting the files
Download Code
Re^2: Need Help in splitting the files
by Corion (Pope) on Jan 28, 2013 at 16:38 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

        Hi, seems to me that tempfile.txt is used at least once instead of tempfile1.txt, therefore I suggest using filenames like tabledata.txt, indexdata.txt etc... This will make things clearer, even if you have to delete even more files afterwards.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (9)
As of 2014-04-21 16:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (497 votes), past polls