Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Solution for replacement of 4th column with 3rd column in a file using awk/sed preserving delimters

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Solution for replacement of 4th column with 3rd column in a file using awk/sed preserving delimters
# 1  
Old 07-12-2016
Solution for replacement of 4th column with 3rd column in a file using awk/sed preserving delimters
input
Code:
"A","B","C,D","E","F"
"S","T","U,V","W","X"
"AA","BB","CC,DD","EEEE","FFF"

required output:
Code:
"A","B","C,D","C,D","F"
"S", T","U,V","U,V","X"
"AA","BB","CC,DD","CC,DD","FFF"

tried using awk but double quotes not preserving for every field. any help to solve this is much appreciated.
Code:
cat input | awk -v FS="\",\"" '{ $4=$3;print }'
"A B C,D C,D F"
"S T U,V U,V X"
"AA BB CC,DD CC,DD FFF"

Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, sample output, and code segments.
Last edited by Don Cragun; 07-12-2016 at 09:14 PM.. Reason: Add CODE and ICODE tags.
# 2  
Old 07-12-2016
Quote:
Originally Posted by khblts
input
Code:
"A","B","C,D","E","F"
"S","T","U,V","W","X"
"AA","BB","CC,DD","EEEE","FFF"

required output:
Code:
"A","B","C,D","C,D","F"
"S", T","U,V","U,V","X"
"AA","BB","CC,DD","CC,DD","FFF"

tried using awk but double quotes not preserving for every field. any help to solve this is much appreciated.
Code:
cat input | awk -v FS="\",\"" '{ $4=$3;print }'
"A B C,D C,D F"
"S T U,V U,V X"
"AA BB CC,DD CC,DD FFF"

Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, sample output, and code segments.
You were very close, but you need to specify the output field separator as well as input field separator. (And there is no reason to read and write the contents of your input file twice using a pipeline instead of letting awk read the input file directly.) Try:
Code:
awk 'BEGIN{FS=OFS="\",\""}{ $4=$3;print }' input

or:
Code:
awk 'BEGIN{FS=OFS="\",\""}{ $4=$3 }1' input

These 2 Users Gave Thanks to Don Cragun For This Post:
khblts rbatte1
# 3  
Old 07-27-2016
Thanks for providing the solution.


Have another thing that I'm trying to solve for 4th column if empty replace with "novalue" what is the best way to get this in awk

Code:
col1,col2,col3,col4,col5,col6
"1","2","3","4","5","6"
"1","2","3","","5","6"
"a","b","c","","e","f"

Code:
col1,col2,col3,col4,col5,col6
"1","2","3","4","5","6"
"1","2","3","novalue","5","6"
"a","b","c","novalue","e","f"


Moderator's Comments:
Mod Comment Start using code tags as you have been asked for, thanks.
# 4  
Old 07-27-2016
Hello khblts,

Could you please try following and let me know if this helps you.
Code:
awk -F, 'NR==1{print;next} ($4 ~ /^\"\"$/){$4="\"novalue\""} 1' OFS=","   Input_file

Output will be as follows.
Code:
col1,col2,col3,col4,col5,col6
"1","2","3","4","5","6"
"1","2","3","novalue","5","6"
"a","b","c","novalue","e","f"

Thanks,
R. Singh
# 5  
Old 07-27-2016
Code:
awk 'BEGIN{FS=OFS="\",\""}{$4=$3; gsub("\"\"", "\"novalue\"")}1' input

# 6  
Old 07-27-2016
Quote:
Originally Posted by khblts
Thanks for providing the solution.


Have another thing that I'm trying to solve for 4th column if empty replace with "novalue" what is the best way to get this in awk

Code:
col1,col2,col3,col4,col5,col6
"1","2","3","4","5","6"
"1","2","3","","5","6"
"a","b","c","","e","f"

Code:
col1,col2,col3,col4,col5,col6
"1","2","3","4","5","6"
"1","2","3","novalue","5","6"
"a","b","c","novalue","e","f"


Moderator's Comments:
Mod Comment Start using code tags as you have been asked for, thanks.
Moderator's Comments:
Mod Comment Please start a new thread for a new problem. That way you don't confuse people like rdrtx1 who try to give yu a solution that matches the thread title when you have a new problem that does NOT match the thread title.

In addition to what RavinderSingh13 suggested, you could also try the slightly simpler:
Code:
awk 'BEGIN{FS=OFS=","} {sub(/""/, "\"novalue\"", $4)} 1' file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Changing values only in 3rd column and 4th column

#cat file testing test! nipw asdkjasjdk ok! what !ok host server1 check_ssh_disk!102.56.1.101!30!50!/ other host server 2 des check_ssh_disk!192.6.1.10!40!30!/ #grep check file| awk -F! '{print $3,$4}'|awk '{gsub($1,"",$1)}1' 50 30 # Output: (6 Replies)
Discussion started by: kenshinhimura
6 Replies

2. UNIX for Dummies Questions & Answers

Search word in 3rd column and move it to next column (4th)

Hi, I have a file with +/- 13000 lines and 4 column. I need to search the 3rd column for a word that begins with "SAP-" and move/skip it to the next column (4th). Because the 3rd column need to stay empty. Thanks in advance.:) 89653 36891 OTR-60 SAP-2 89653 36892 OTR-10 SAP-2... (2 Replies)
Discussion started by: AK47
2 Replies

3. Shell Programming and Scripting

awk or sed: change the color of a column w/o screwing up column spacing

Hey folks. I wrote a little awk script that summarizes /proc/net/dev info and then pipes it to the nix column command to set up column spacing appropriately. Here's some example output: Iface RxMBytes RxPackets RxErrs RxDrop TxMBytes TxPackets TxErrs TxDrop bond0 9 83830... (3 Replies)
Discussion started by: ryran
3 Replies

4. Shell Programming and Scripting

AWK script to create max value of 3rd column, grouping by first column

Hi, I need an awk script (or whatever shell-construct) that would take data like below and get the max value of 3 column, when grouping by the 1st column. clientname,day-of-month,max-users ----------------------------------- client1,20120610,5 client2,20120610,2 client3,20120610,7... (3 Replies)
Discussion started by: ckmehta
3 Replies

5. Shell Programming and Scripting

1st column,2nd column on first line 3rd,4th on second line ect...

I need to take one column of data and put it into the following format: 1st line,2nd line 3rd line,4th line 5th line,6th line ... Thanks! (6 Replies)
Discussion started by: batcho
6 Replies

6. Shell Programming and Scripting

Awk or Sed, fubd match in column, then edit column.

FILE A: 9780743551526,(Abridged) 9780743551779,(Unabridged) 9780743582469,(Abridged) 9780743582483,(Unabridged) 9780743563468,(Abridged) 9780743563475,(Unabridged) FILE B: c3saCandyland 9780743518321 "CANDYLAND" "MCBAIN, ED" 2001 c3sbCandyland 9780743518321 ... (7 Replies)
Discussion started by: glev2005
7 Replies

7. Shell Programming and Scripting

Looking for AWK Solution for column comparison in a single file

- I am looking for different kind of awk solution which I don't think is mentioned before in these forums. Number of rows in the file are fixed Their are two columns in file1.txt 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10 I am looking for 3... (1 Reply)
Discussion started by: softwarekids23
1 Replies

8. Shell Programming and Scripting

Extracting 3rd column using awk from file with spaces.

BAQ001 /dev/rdsk/c2t0d7 1C13 (M) RW 69053 The line above is from a text file. I want to use awk to extract the value in the third column 1C13. I just can't seem to get the syntax right or something. Any help would be appreciated. Thanks, (5 Replies)
Discussion started by: ricnetman
5 Replies

9. Shell Programming and Scripting

How to extract 3rd line 4th column of a file

Hi, Shell script: I would need help on How to extract 3rd line 4th column of a file with single liner Thanks in advance. (4 Replies)
Discussion started by: krishnamurthig
4 Replies

10. UNIX for Dummies Questions & Answers

Trim String in 3rd Column in Tab Delimited File...SED/PERL/AWK?

Hey Everybody, I am having much trouble figuring this out, as I am not really a programmer..:mad: Datafile.txt Column0 Column1 Column2 ABC DEF xxxGHI I am running using WGET on a cronjob to grab a datafile, but I need to cut the first three characters from... (6 Replies)
Discussion started by: rickdini
6 Replies
Login or Register to Ask a Question