How to Delete Last Row from .csv file in perl

Shell Programming and Scripting
1

Hi ,

I've a perl script to convert .xls to .csv .After conversion I want to delete first 28 and the last row from .csv file.Is there any efficent way to achive this both together.

I'm deleting first 28 rows by using folllowing my perl code:

exec " tail -n+28 infile.csv > outfile.csv

I tried
exec " tail -n+28 infile.csv > infile.csv

but,it failed , I want to achive everthing in one file (infile.csv).I really don't need outfile.csv.

Thanks,
Ajay

2

You can do it directly in Perl, if you want to delete the first 5 and the the last record for instance, you could write something like this:

% cat infile
1
2
3
4
5
6
7
8
9
10
% perl -i -ne'print unless $. < 6 || eof' infile
% cat infile                                      
6
7
8
9
3 
use strict;
BEGIN { unshift @INC, "./lib"; }
use Spreadsheet::ParseExcel;
my $oExcel = Spreadsheet::ParseExcel->new;
#1.1 Normal Excel97
 my @files = glob("*.xls");
    foreach my $file (@files)
    {
        print "$file \n";
my $oBook = $oExcel->Parse($file);
my($iR, $iC, $oWkS, $oWkC);
$file=~ s/\..*//;
print "FILE1 $file \n";
open (FILE,">$file.csv");
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
    {
        $oWkS = $oBook->{Worksheet}[$iSheet];
        for(my $iR = $oWkS->{MinRow} ;
        defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++)
        {
            for(my $iC = $oWkS->{MinCol} ;
            defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++)
            {
            $oWkC = $oWkS->{Cells}[$iR][$iC];
            print FILE "$oWkC->{Val},";
            }
   }
 close(FILE);
 perl -i -ne'print unless $. < 6 || eof' infile
 cat infile 
}
#unlink @files;
exit 0;

Will this work ?

Thanks,
Ajay

4

No,
I believe you need something like this (added code in red):

use strict;
use Spreadsheet::ParseExcel;
my $oExcel = Spreadsheet::ParseExcel->new;
#1.1 Normal Excel97
 my @files = glob("*.xls");
    foreach my $file (@files)
    {
        print "$file \n";
my $oBook = $oExcel->Parse($file);
my($iR, $iC, $oWkS, $oWkC);
$file=~ s/\..*//;
print "FILE1 $file \n";
open (FILE,">$file.csv");
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
    {
        $oWkS = $oBook->{Worksheet}[$iSheet];
        for(my $iR = $oWkS->{MinRow} ;
        defined $oWkS->{MaxRow} && $iR < $oWkS->{MaxRow} ; $iR++)
        {
            $iR < 29 and next;
            for(my $iC = $oWkS->{MinCol} ;
            defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++)
            {
            $oWkC = $oWkS->{Cells}[$iR][$iC];
            print FILE "$oWkC->{Val},";
            }
   }
 close(FILE);
}
}
#unlink @files;
exit 0;

---------- Post updated at 05:26 PM ---------- Previous update was at 05:20 PM ----------

Sorry for the multi-editing :slight_smile:

P.S. And of course, you should use a variable instead of hard-coded value for 29.

5

No.I tired your code but its not working.It doesn't display anything in the .csv file...!The working code is as follows:

use strict;
BEGIN { unshift @INC, "./lib"; }
use Spreadsheet::ParseExcel;
my $oExcel = Spreadsheet::ParseExcel->new;

#1.1 Normal Excel97
# my @files = glob("*.xls");
#    foreach my $file (@files) 
#    {
#        print "$file \n";
my $oBook = $oExcel->Parse('WeeklyException_Out_ContainerDiv_810_855_856');
my($iR, $iC, $oWkS, $oWkC);
$file=~ s/\..*//;
$file=$file.'_1';
print "FILE1 $file \n";
open (FILE,">$file.csv");
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
    {
        $oWkS = $oBook->{Worksheet}[$iSheet];
        for(my $iR = $oWkS->{MinRow} ;
        defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++)
        {
            for(my $iC = $oWkS->{MinCol} ;
            defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++)
            {
            $oWkC = $oWkS->{Cells}[$iR][$iC];
            print FILE "$oWkC->{Val},";
            }
        print FILE "\n";
    }
}
close(FILE);
#}
#unlink @files;
exec "tail -n+28 /oracle/apps/bocs/edi_monitoring/pending/WeeklyException_Out_ContainerDiv_810_855_856_1.csv > /oracle/apps/bocs/edi_monitoring/pending/WeeklyException_Out_ContainerDiv_810_855_856.csv";
exit 0;

This successfully deletes first 28 rows but,when I add following line after that to delete last row it doesnt work

exec "tail -n-1 /oracle/apps/bocs/edi_monitoring/pending/WeeklyException_Out_ContainerDiv_810_855_856.csv > /oracle/apps/bocs/edi_monitoring/pending/WeeklyException_Out_ContainerDiv_810_855_856_5.csv";

Please advice on how i can enhance this to delete last row.

Thanks,
Ajay

6

To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags

```text
 and 
```

by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums

7

OK,
than try this instead of your exec code:

{
    local ( $^I, @ARGV ) = ( '' , 'your_filename' );
    while (<>) {
        print unless $. <= 28 || eof;
    }
}
8

Thanks radoulov!
This worked fine for me.

-Ajay