469,336 Members | 5,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

Search and Replace a column of the csv file

Vasuki Masilamani
Hi,

I have a csv file which has "1" in the column 5. I need to replace all "1" in column 5 to "1030".

The Pattern which would search for "1" in 5th column is given below.

'^"[^,]*","[^,]*","[^,]*","[^,]*","1"'

Can anyone help me with a Search and Replace step for this in Perl Script?

Any solution will be helpful.

Thanks,
Vasuki.
Dec 13 '06 #1
4 8462
Please find the below code.

$f1 = 'C:\Vasuki\ReplacePerl\chm_commitmt.csv';

open(FILE, "$f1") || die "Could not open file chm_commitmt.csv \n";

$f2= 'C:\Vasuki\ReplacePerl\chm_commitmt_new.csv';

my @outLines1;

while($data = <FILE>)

{
if($data =~ '^"[^,]*","[^,]*","[^,]*","[^,]*","1"')

{

$data =~ s/^"[^,]*","[^,]*","[^,]*","[^,]*","1"/"[^,]*","[^,]*","[^,]*","[^,]*","1030"/g;

push(@outLines,$data);

}

}

open(FILE2, ">$f2") || die "Could not open file chm_commitmt_new.csv \n";

print (FILE2 @outLines);

close (FILE);

close (FILE2);

undef( @outLines );


This code not properly replacing 1 in 5th column to 1030. Can anyone correct the mistake in this??


Thanks,
Vasuki.
Dec 13 '06 #2
miller
1,089 Expert 1GB
For easy parsing of CSV files, use this CPAN module or any of the other ones freely available. This will simplify your code and avoid any needless bugs.

http://search.cpan.org/search?query=Text::CSV
Dec 14 '06 #3
Hope this may be help

Expand|Select|Wrap|Line Numbers
  1.  
  2. #!/usr/bin/perl
  3.  
  4. open(FILE,"test.csv");
  5. open(OUTFILE, "newfile.csv");
  6.  
  7. while($line=<FILE>){
  8.  
  9.   chomp($line);
  10.   $line =~ s/(.*)?\,(.*)?\,(.*)?\,(.*)?\,(1)(.*)/$1\,$2\,$3\,$4\,1030$6/g;
  11.   print OUTFILE $line ."\n";
  12. }
  13.  
  14. close FILE, OUTFILE;
  15.  
  16.  
One more way to do this

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl
  2.  
  3. open(FILE,"test.csv") or die $!;
  4. open(TARGET,"> new.csv") or die $!;
  5.  
  6. while($line = <FILE>){
  7.  
  8.   chomp($line);
  9.   next unless($line =~ m/\S+/);
  10.  
  11.   @array = split(",",$line);
  12.  
  13.   if($array[4] == 1){
  14.     $array[4] = 1035;
  15.   }
  16.  
  17.   $line = join(",",@array);
  18.  
  19.   print TARGET $line ."\n";
  20. }
  21. close FILE;
  22. close TARGET;
  23.  
  24.  
Dec 15 '06 #4
GunnarH
83
Hope this may be help
Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl
  2.  
  3. open(FILE,"test.csv");
  4. open(OUTFILE, "newfile.csv");
  5.  
  6. while($line=<FILE>){
  7.  
  8.   chomp($line);
  9.   $line =~ s/(.*)?\,(.*)?\,(.*)?\,(.*)?\,(1)(.*)/$1\,$2\,$3\,$4\,1030$6/g;
  10.   print OUTFILE $line ."\n";
  11. }
  12.  
  13. close FILE, OUTFILE;
  14.  
I doubt that that buggy code is at all helpful. There is about one error or other kind of obvious shortcoming per line:
  • No check of the return value from open
  • Opening OUTFILE in read mode, even if it's intended for writing
  • Unnecessary escaping of commas in both PATTERN and REPLACEMENT
  • Unnecessary /g modifier
  • Assuming that each column is delimited with dubbelquotes (which is indicated by Vasuki's pattern), the PATTERN does not match
  • Incorrect use of the close() function

This is a better attempt (I hope):
Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl
  2. use strict;
  3. use warnings;
  4.  
  5. my $f1 = 'C:\Vasuki\ReplacePerl\chm_commitmt.csv';
  6. my $f2 = 'C:\Vasuki\ReplacePerl\chm_commitmt_new.csv';
  7.  
  8. open my $file1, '<', $f1 or die "Couldn't open $f1: $!";
  9. open my $file2, '>', $f2 or die "Couldn't open $f2: $!";
  10.  
  11. while (<$file1>) {
  12.     print $file2 $_ if s/^((?:"[^,]*",){4})"1"/$1"1030"/;
  13. }
Dec 15 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Jane Doe | last post: by
6 posts views Thread by joltman | last post: by
1 post views Thread by Andrew | last post: by
2 posts views Thread by Michael Peters | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.