Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

search and replace in csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting search and replace in csv file
# 8  
Old 02-27-2012
try my suggestion in the #4 post

Code:
 
$ sed 's/,\([0-9]*\),/,1010,/' input.txt
9788691010,1010,20120120002804,
9705645060,1010,20120120004753,
9014961516,1010,20120120004809,

---------- Post updated at 01:01 PM ---------- Previous update was at 01:00 PM ----------

Quote:
Originally Posted by dondilip
,. means first comma followed by only one character. If it has to four characters then ,.... right?


Here is another file where I want to change the second column to 1010

9788691010,5004,20120120002804,
9705645060,5004,20120120004753,
9014961516,5004,20120120004809,

characters then ,.... right? ---> yes you are correct
This User Gave Thanks to itkamaraj For This Post:
dondilip
# 9  
Old 02-27-2012
Quote:
Originally Posted by itkamaraj
try my suggestion in the #4 post

Code:
 
$ sed 's/,\([0-9]*\),/,1010,/' input.txt
9788691010,1010,20120120002804,
9705645060,1010,20120120004753,
9014961516,1010,20120120004809,

---------- Post updated at 01:01 PM ---------- Previous update was at 01:00 PM ----------


Your command works flawlessly..One more doubt, this command will change all the entries in the file where-ever it matches. '/g' is not required right..


characters then ,.... right? ---> yes you are correct
# 10  
Old 02-27-2012
g is not requried, otherwise it will match the all the numeric which falls between two comma's
# 11  
Old 02-27-2012
Hi Kamaraj,

In your sed, it replace all the second column value to the replace value, but I want to replace only the one which matches column value not the others.


The sed output has both changed entries and non-changed entries. Is there a way to captures only the entries that matched and changed by the sed.?

Thanks in advance
Last edited by dondilip; 02-27-2012 at 04:25 AM.. Reason: Small change
# 12  
Old 02-27-2012
Code:
 
$ cat test.txt
9788691010,5004,20120120002804,
9705645060,5004,20120120004753,
9014961516,1000,20120120004809,
9014961516,5004,20120120004809,
9014961516,1000,20120120004809,
9014961516,1000,20120120004809,
 
$ sed '/5004/ s/,\([0-9]*\),/,1010,/' test.txt
9788691010,1010,20120120002804,
9705645060,1010,20120120004753,
9014961516,1000,20120120004809,
9014961516,1010,20120120004809,
9014961516,1000,20120120004809,
9014961516,1000,20120120004809,

If you want to check only in the 2nd column, then use the below awk
Code:
 
 
$ nawk -F, -v OFS="," '$2=="5004" {$2="1010"}1' test.txt
9788691010,1010,20120120002804,
9705645060,1010,20120120004753,
9014961516,1000,20120120004809,
9014961516,1010,20120120004809,
9014961516,1000,20120120004809,
9014961516,1000,20120120004809,

# 13  
Old 02-27-2012
Quote:
Originally Posted by itkamaraj
Code:
 
$ cat test.txt
9788691010,5004,20120120002804,
9705645060,5004,20120120004753,
9014961516,1000,20120120004809,
9014961516,5004,20120120004809,
9014961516,1000,20120120004809,
9014961516,1000,20120120004809,
 
$ sed '/5004/ s/,\([0-9]*\),/,1010,/' test.txt
9788691010,1010,20120120002804,
9705645060,1010,20120120004753,
9014961516,1000,20120120004809,
9014961516,1010,20120120004809,
9014961516,1000,20120120004809,
9014961516,1000,20120120004809,

If you want to check only in the 2nd column, then use the below awk
Code:
 
 
$ nawk -F, -v OFS="," '$2=="5004" {$2="1010"}1' test.txt
9788691010,1010,20120120002804,
9705645060,1010,20120120004753,
9014961516,1000,20120120004809,
9014961516,1010,20120120004809,
9014961516,1000,20120120004809,
9014961516,1000,20120120004809,

Sorry man I dont have nawk package..Can I use awk instead?
# 14  
Old 02-27-2012
yes.. try with awk.

what OS you are using ?

post the ouput of the below command

Code:
 
uname -a
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

CSV File with Multiple Search Parameter

Dear Team Members, I have a unique problem. Below is the dataset which I have. I am writing a script which will read through the file and pull the invoice no. (Field 2 of C1 row). "C1",990001,"L1","HERO","MOTORCYCLE","ASIA-PACIFIC","BEIJING" "C2","CLUTCH","HYUNDAI",03032017... (13 Replies)
Discussion started by: chetanojha
13 Replies

2. Shell Programming and Scripting

[bash] - Replace blank and string in csv file

Hi all, i have a .csv file with only two columns, like: Login;Status Luca;S Marco; Stefano; Elettra;S Laura; ... I need to replace the blank space on Status column whit Enabled end, on the same column, S whit Disabled, like: Login;Status Luca;Disabled Marco;Enabled Stefano;Enabled... (10 Replies)
Discussion started by: kamose
10 Replies

3. Shell Programming and Scripting

Read in search strings from text file, search for string in second text file and output to CSV

Hi guys, I have a text file named file1.txt that is formatted like this: 001 , ID , 20000 002 , Name , Brandon 003 , Phone_Number , 616-234-1999 004 , SSNumber , 234-23-234 005 , Model , Toyota 007 , Engine ,V8 008 , GPS , OFF and I have file2.txt formatted like this: ... (2 Replies)
Discussion started by: An0mander
2 Replies

4. Shell Programming and Scripting

CSV file REPLACE COLUMN if it matches

I have a file cat 1.txt AAAA , BBBB , CCCC , DDDD DFDF , DFDF , DFDF , FDDD AA11 , DFDF , 0000 , UTIO ADSD , WERT, 0000 , JKJL If the 3rd column is not equal to "0000" , then it should replace "0000" with "XXXX" and if its equal to "0000" then print the line as it is. need help. (9 Replies)
Discussion started by: aravindj80
9 Replies

5. Shell Programming and Scripting

Nested search in a file and replace the inner search

Hi Team, I am new to unix, please help me in this. I have a file named properties. The content of the file is : ##Mobile props east.url=https://qa.east.corp.com/prop/end west.url=https://qa.west.corp.com/prop/end south.url=https://qa.south.corp.com/prop/end... (2 Replies)
Discussion started by: tolearn
2 Replies

6. Shell Programming and Scripting

Perl search csv fileA where two strings exist on another csv fileB

Hi I have two csv files, with the following formats: FileA.log: Application, This occured blah Application, That occured blah Application, Also this AnotherLog, Bob did this AnotherLog, Dave did that FileB.log: Uk, London, Application, datetime, LaterDateTime, Today it had'nt... (8 Replies)
Discussion started by: PerlNewbRP
8 Replies

7. Shell Programming and Scripting

Replace a particular field in all records in a csv file

hi, i have various csv files, the file format is as follows Entry: "1",4,2010/08/15-10-00-00.01,,"E",,,,,,,,,120,0,"M4_","C","KEW-011-5337140-20100916163456-540097","1234567890","N N 0 ",,,"NUK 800100200",,,"NN",,,,,,,,,,,,"0000000001|0001|20150401... (2 Replies)
Discussion started by: niteesh_!7
2 Replies

8. Shell Programming and Scripting

Replace date_time to unixtime in csv.file

Hello, since hours I am trying to replace in a csv-file the date_time to unixtime. I tried sed, awk but not successful. I can not call a shell command within awk. Probably there is an easier way. Thanks in advance for your help Regards, telemi test.csv: 2010-04-22... (5 Replies)
Discussion started by: telemi
5 Replies

9. Shell Programming and Scripting

find & replace comma in a .csv file.

HI, Please find the text below. I receive a .csv file on server. I need the comma(,) in the second column to be replaced by a semi-colon( ; ). How to do it. Please help. Sample text: "1","lastname1,firstname1","xxxxxx","19/10/2009","23/10/2009","0","N","Leave"... (2 Replies)
Discussion started by: libin4u2000
2 Replies

10. Shell Programming and Scripting

replace a field in a CSV file

Hello all, I've a CSV file and need to replace 5th field if its value is "X". The exact requirement is to replace 5th field (column) with "Y" if a. it's value is "X" AND b. the line must start with ABC string i guess this can be done with awk. Pl help. For security reasons, the... (2 Replies)
Discussion started by: prvnrk
2 Replies
Login or Register to Ask a Question