473,569 Members | 2,844 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Search and Replace a column of the csv file

Vasuki Masilamani
18 New Member
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 8700
Vasuki Masilamani
18 New Member
Please find the below code.

$f1 = 'C:\Vasuki\Repl acePerl\chm_com mitmt.csv';

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

$f2= 'C:\Vasuki\Repl acePerl\chm_com mitmt_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_ne w.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 Recognized Expert Top Contributor
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
vmalagi
2 New Member
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 New Member
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

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

Similar topics

4
10185
by: Jane Doe | last post by:
Hi, I need to search and replace patterns in web pages, but I can't find a way even after reading the ad hoc chapter in New Rider's "Inside JavaScript". Here's what I want to do: function filter() { var items = new Array("John", "Jane");
0
1197
by: DaveJG | last post by:
I have a couple of questions involving the search/replace in the IDE and regular expressions. Any help would be greatly appreciated. 1. How would I replace two line feeds with one? That is Var1 = 1 Var2 = 2 becomes
6
1346
by: joltman | last post by:
I am trying to find out how to search for a file. I am making a switchboard program with buttons pointing to programs, but I want to make it so that if the program a button points to ever moves, it will automatically search the local hard drives for it. Thanx -joltman
1
4388
by: Andrew | last post by:
Hi, im trying to create a small function which can create a binary tree from the entries in a text file and after that we can perform all the usual operations like search, insert and delete etc. Now i have entries something like this IPaddress Phone No 192.168.2.1 2223447689 192.168.2.2 3334449898 192.168.2.3 5667869089
2
5518
by: Michael Peters | last post by:
is there a way to replace a certain sequence of characters by line feed (vbCrLf ), for all text columns in a table, using Search+Replace? -Michael
3
1506
by: mse07 | last post by:
hi all i search about statement in text file by this code : LineFlag = LineFlag + 1 Line Input #1, strLine If LineFlag = 15 Then If InStr(1, strLine, " Transfer completed successfully.") <> 0 Then MsgBox "successful"
13
3049
by: ravindarjobs | last post by:
hello friends, i want to seach for a file in the computer. i dont know the exact location where it is. so the requirement is to search entire hard disk and get the path of the specified file. after that we now save the path in a string variable. now, we have to execute the appication with the path stored in the string. may be using...
2
1654
by: yxq | last post by:
Hello, I want to update A program file from A. First startup A, then startup B process from A, close A, B will copy new file to replace A file. But system states that A is using. A code: //////////////////////////////////////////// Dim objProcess As New Process objProcess.StartInfo.FileName = "B.exe"
3
5513
by: Sabine | last post by:
Hi there, I would need your help again. I am using Dev Avish's API function "Search for a file" http://www.mvps.org/access/api/api0006.htm to find a file on my computer which works well. As you may know, in this function the user needs to specify the drive which is then searched for the specific file.
1
1177
by: ahmed222too | last post by:
i use vb6 i want to make a backword search in text file i mean (by backword search) to begin the search from the last for example (i will go to school to learn more....) i want to find the last time the word (to) happened then the next backwordly
0
7695
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7612
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7922
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7668
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7964
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
3653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3637
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1209
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
936
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.