How to search and replace string from nth column from a file?

Login or Register to Ask a Question and Join Our Community

How to search and replace string from nth column from a file?

Tags

awk, sed, shell scripts

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to search and replace string from nth column from a file?

11-10-2015

Registered User

18, 0

Join Date: Oct 2012

Last Activity: 10 November 2015, 6:44 AM EST

Posts: 18

Thanks Given: 6

Thanked 0 Times in 0 Posts

How to search and replace string from nth column from a file?

I wanted to search for a string and replace it with other string from nth column of a file which is comma seperated which I am able to do with below

# For Comma seperated file without quotes

Code:

 awk 'BEGIN{OFS=FS=","}$"'"$ColumnNo"'"=="'"$PPK"'"{$"'"$ColumnNo"'"="'"$NPK"'"}{print}' ${FileName}

# For Comma seperated file with quotes

Code:

awk 'BEGIN{OFS=FS=","}$"'"$ColumnNo"'"=="\""'"$PPK"'"\""{$"'"$ColumnNo"'"="\""'"$NPK"'"\""}{print}' ${FileName}

But I wanted to make it in one command so that it works for both type of file. I know I can do with sed command sed -i "s/$PPK/$NPK/" $FileName but it replaces the value in other columns as well so can anyone suggest what can be done ?

Here ColumnNo is the nth column from which I wanted to replace PPK to NPK.

Please find other details

Code:

 uname -a
Linux XXXXXXX 2.6.32-220.25.1.el6.x86_64 #1 SMP Tue Aug 14 13:14:38 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
echo $SHELL
/bin/ksh
echo $KSH_VERSION
Version JM 93t+ 2010-06-21

Last edited by Amit Joshi; 11-10-2015 at 12:59 AM.. Reason: Added more details

Amit Joshi

View Public Profile for Amit Joshi

Find all posts by Amit Joshi

11-10-2015

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

This description is a bit diffuse. Try

Code:

awk -v COL=$ColumnNo -v SRCH="$PPK" -v REP="$NPK" '
        {FS=OFS="\""; $0=$0                                     # recalculate fields based on " delimiter
         for (i=2; i<=NF; i+=2) gsub (SEP,"\001", $i)           # replace , in " delimited fields with a token
         FS=OFS=SEP; $0=$0                                      # recalculate fields based on , delimiter

         sub (SRCH, REP, $COL)                                  # do whatever you need 

         gsub ("\001", SEP)                                     # just before printing anything, put back the inner ","
        }
1 
' SEP="," file

This is untested as a sample is missing.

This User Gave Thanks to RudiC For This Post:

RudiC

View Public Profile for RudiC

Find all posts by RudiC

11-10-2015

Registered User

18, 0

Join Date: Oct 2012

Last Activity: 10 November 2015, 6:44 AM EST

Posts: 18

Thanks Given: 6

Thanked 0 Times in 0 Posts

Thanks RudiC, your solution worked for me and I have learnt 2 new function sub & gsub but I simplified it as per below command

Code:

echo '"Amit","Amit"' | awk  -v COL=2 -v SRCH="Amit" -v REP="XXX" 'BEGIN{OFS=FS="," } {sub (SRCH, REP, $COL);print }'

Code:

echo 'Amit,Amit'     | awk  -v COL=2 -v SRCH="Amit" -v REP="XXX" 'BEGIN{OFS=FS="," } {sub (SRCH, REP, $COL);print }'

It is also working fine. Can you please suggest, Is there any limitation or drawback from my commands or it will work perfectly ?

Amit Joshi

View Public Profile for Amit Joshi

Find all posts by Amit Joshi

11-10-2015

Registered User

92, 11

Join Date: Oct 2015

Last Activity: 15 December 2017, 3:39 AM EST

Location: Akhand Bharath

Posts: 92

Thanks Given: 62

Thanked 11 Times in 11 Posts

Hi Amit,
what does awk -v stands for it.?

looney

View Public Profile for looney

Find all posts by looney

11-10-2015

Registered User

1,781, 705

Join Date: May 2008

Last Activity: 10 November 2021, 5:38 PM EST

Posts: 1,781

Thanks Given: 62

Thanked 705 Times in 653 Posts

Quote:

Originally Posted by looney

Hi Amit,
what does awk -v stands for it.?

More like:

Code:

awk  -v COL=2

-v requires a variable name of your choosing and an assignment.

Man pages for awk in Linux (gawk)

Quote:

-v var=val
--assign var=val
Assign the value val to the variable var, before execution of the program begins.
Such variable values are available to the BEGIN block of an AWK program.

This User Gave Thanks to Aia For This Post:

Aia

View Public Profile for Aia

Find all posts by Aia

11-10-2015

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

Amit, you can simplify as you did unless there are fields in quotes that have commas in them.

RudiC

View Public Profile for RudiC

Find all posts by RudiC

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Search partial string in a file and replace the string - UNIX

I have the below string which i need to compare with a file and replace this string in the file which matches closely. Can anyone help me on this. string(Scenario 1)- user::r--,user::ourfrd:r-- String(Scenario 2)- user::r-- File **** # file: /local/Desktop/myfile # owner: me # group:...

2. Shell Programming and Scripting

Replace Value of nth Column of Each Line Using Array

Hello All, I am writing a shell script with following requirement: 1. I have one input file as below CHE01,A,MSC,INO CHE02,B,NST,INC CHE03,C,STM,INP 2. In shell script I have predefined array as below: Array1={A, B, C} Array2= {U09, C04, A054}

3. Shell Programming and Scripting

Search term in nth field and replace kth column

Hi, I have a text file which looks like this a.txt A,12,Apple,Red B,33,Banana,Yellow C,66,Sky,Blue I need to search for a particular field(s) in particular column(s) and for that matching line need to replace the nth column. Sample scenario 1: Search for 66 in second field and Sky in...

4. Shell Programming and Scripting

awk search and replace nth column by using a variable.

I am passing a variable and replace nth value with the variable. I tried using many options in awk command but unable to ignore the special characters in the output and also unable to pass the actual value. Input : "1","2","3" Output : "1","1000","3" TempVal=`echo 1000` Cat...

5. UNIX for Dummies Questions & Answers

Search for a string,delete the line and replace with new string in a file

Hi Everyone, I have a requirement in ksh where i have a set of files in a directory. I need to search each and every file if a particular string is present in the file, delete that line and replace that line with another string expression in the same file. I am very new to unix. Kindly help...

6. Shell Programming and Scripting

How to search and replace string in column in file with command sed?

how to search and replace string in column in file with command sed or other search "INC0000003.in" and replace column 4 = "W" $ cat file.txt INC0000001.in|20150120|Y|N|N INC0000002.in|20150120|Y|N|N INC0000003.in|20150120|Y|N|N INC0000004.in|20150120|Y|N|Noutput...

7. Shell Programming and Scripting

awk to search for specific line and replace nth column

I need to be able to search for a string in the first column and if that string exists than replace the nth column with "-9.99". AW12000012012 2.38 1.51 3.01 1.66 0.90 0.91 1.22 0.82 0.57 1.67 2.31 3.63 0.00 AW12000012013 1.52 0.90 1.20 1.34 1.21 0.67 ...

8. Shell Programming and Scripting

Replace the nth column date as MM/DD/YYYY

Hi, I need some unix command to replace the following thing. cat test.dat 1234|test|8/19/2009|8/20/2009|test 1234|test|8/9/2009|8/21/2009|test 1234|test|8/1/2009|8/2/2009|test after processing 1234|test|08/19/2009|08/20/2009|test 1234|test|08/09/2009|08/21/2009|test...

9. UNIX for Dummies Questions & Answers

Search a string in the file and then replace another string after that position

Hi I am looking for a particular string in a file.If the string exists, then I want to replace another string with some other text.Once replaced, search for the same text after that character position in the file. :wall: E.g: Actual File content: Hello Name: Nitin Raj Welcome to Unix...

10. UNIX for Dummies Questions & Answers

Search and replace string only in a particular column in a delimited file

I have file with multiple columns. Column values for a record may be same. Now i have to replace a column value(this can be same for the other columns) with new value. File.txt A,B,C,D,A,B,C,D,A,B,C,D A,B,C,D,A,B,C,D,A,B,C,D A,B,C,D,A,B,C,D,A,B,C,D A,B,C,D,A,B,C,D,A,B,C,D...

Login or Register to Ask a Question