Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to Delete Last Row from .csv file in perl

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to Delete Last Row from .csv file in perl
# 1  
Old 08-27-2009
How to Delete Last Row from .csv file in perl
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  
Old 08-27-2009
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:
Code:
% 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  
Old 08-27-2009
Code:
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
Last edited by radoulov; 08-27-2009 at 12:06 PM.. Reason: added code tags
# 4  
Old 08-27-2009
No,
I believe you need something like this (added code in red):

Code:
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 Smilie

P.S. And of course, you should use a variable instead of hard-coded value for 29.
Last edited by radoulov; 08-27-2009 at 12:32 PM..
# 5  
Old 08-28-2009
No.I tired your code but its not working.It doesn't display anything in the .csv file...!The working code is as follows:

Code:
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

Code:
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
Last edited by Franklin52; 08-28-2009 at 07:48 AM.. Reason: Please use code tags!
# 6  
Old 08-28-2009
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 [code] and [/code] 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  
Old 08-28-2009
OK,
than try this instead of your exec code:

Code:
{
    local ( $^I, @ARGV ) = ( '' , 'your_filename' );
    while (<>) {
        print unless $. <= 28 || eof;
    }
}
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Row Count in .csv file

Hi, I have to find the count of rows starting with "E," in given a.csv file . Sample Data File. E,2333AED,A,MC3,25,31-MAY-18 E,2333AED,A,MC3,25,31-MAY-18 CYMC3 25AED 0000 E,2333CZK,A,MC3,25,31-MAY-18 CYMC3 25CZK 0000 E,2333EUR,A,MC3,25,31-MAY-18... (3 Replies)
Discussion started by: Prabhakar Y
3 Replies

2. UNIX for Beginners Questions & Answers

How to delete row in csv file on date range?

I want to delete row in csv file , which row value from 2009-10-01 to 2011-06-03 using script.my csv row data look like: 2009-10-01 2011-03-30 2011-03-31 2011-04-01 2011-06-03 2011-06-30 2011-07-01 2011-09-28 ... (7 Replies)
Discussion started by: rakibul639
7 Replies

3. Shell Programming and Scripting

Bash - delete from csv all the row if the first column is length >

Hi guys, i have a csv file like: USERID;COG;DESCR;FIL;OFF user001;user;test1;001;A01 user002;user;test2;002;A02 user0003;user;test3;003;A03 user004;user;test4;004;A04 user0005;user;test5;005;A05 etc.. I need to read line for line and, if value of first column is > 7 char (in this example... (4 Replies)
Discussion started by: kamose
4 Replies

4. Shell Programming and Scripting

Append data to new row in CSV file every day

Hi All I will run the same script every day in corn and output should go to same CSV file but in different row with dates on it. Below is my example in attached format. Script i am using to collect switch port online DATE=`date '+%d-%m-%y'` for f in `cat... (1 Reply)
Discussion started by: ranjancom2000
1 Replies

5. Shell Programming and Scripting

Copying down first row in to all the below blank rows in a .csv file

Hi All, I have many of files(.csv) of the format given below. Date,Name,Location 04/02/2012,A,India ,B,China ,C,USA Like this I have 1000's of rows and many columns in all my files. I need a shell script to copy down the Date(in this example column1) to the next 2 rows below(in the... (8 Replies)
Discussion started by: ks_reddy
8 Replies

6. Shell Programming and Scripting

delete a row in csv file based on the date

Hi, I have a csv file with old data..i need to have only last 30 days from the current dateof data in the file.The fourth field in the file is a date field.i need to write a script to delete the old data by comparing the the fourth field with the (current date -30).I need to delete the rows in... (2 Replies)
Discussion started by: pals70423
2 Replies

7. UNIX for Dummies Questions & Answers

How to delete a row in a file

hi team, i have a file txt1 , in that file there r 10 lines . my aim is to cut 2nd and 6th line of the file . what command we can use for this scenario. (8 Replies)
Discussion started by: natraj005
8 Replies

8. Shell Programming and Scripting

delete last row in PERL

How to delete last row in the file in PERL. file1 has a.output b.output c.output d.output e.output expected output is a.output b.output c.output d.output (1 Reply)
Discussion started by: adaleru
1 Replies

9. UNIX for Dummies Questions & Answers

Delete first row of csv file

I have a csv file, which is > 2 Gigs. I need to BCP that file to Sybase db , but I cant upload that b'caz first row of the file is failing. ( having some errors probably.) I can manually insert the first line into db & then I can upload the rest of the data in file, if i can delete the first row. ... (2 Replies)
Discussion started by: kedar.mehta
2 Replies

10. Shell Programming and Scripting

How to delete a row in a file?

Does anyone know a way to delete rows 6-9 from the below output? I have searched the forum but did not find any thing helpful. backups01laxint.liuc(s){jsandova}<0>$ nsrjb -v | awk -F' ' '$3>=01{print $0}' | cut -b 1-79 | cat -n setting verbosity level to `1' 1 slot volume ... (17 Replies)
Discussion started by: jsandova
17 Replies
Login or Register to Ask a Question