Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Append data with substring of nth column fields using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Append data with substring of nth column fields using awk
# 1  
Old 10-25-2016
Append data with substring of nth column fields using awk
Hi guys,
I have problem to append new data at the end of each line of the files where it takes whole value of the nth column. My expected result i just want to take a specific value only. This new data is based on substring of 11th, 12th 13th column that has comma seperated value.

My code:
Code:
awk -F "," '{print $0 "," $11 "," $12 "," $13}'

Input file id.csv:
Code:
A12345,1-4N,1-77,1-15,Name,1-77,ABC,First,John,1-77,0.1 /ser/tel 77777 0,0.1 /id 40001,0.1 /gen 111234567 0,2016-10-24 14:18:58
A67899,1-4U,1-78,1-20,Owner,1-66,XYZ,Last,Jane,1-77,0.1 /ser/web 88881 0,0.1 /ic 40002,0.1 /sac 222345678 0,2016-10-24 14:18:58

Expected Output:
Code:
A12345,1-4N,1-77,1-15,Name,1-77,ABC,First,John,1-77,0.1 /ser/tel 77777 0,0.1 /id 40001,0.1 /gen 111234567 0,2016-10-24 14:18:58,77777,/ser/tel,40001,/id,111234567
A67899,1-4U,1-78,1-20,Owner,1-66,XYZ,Last,Jane,1-77,0.1 /ser/web 88881 0,0.1 /ic 40002,0.1 /sac 222345678 0,2016-10-24 14:18:58,88881,/ser/web,40002,/ic,222345678

Thanks Smilie
# 2  
Old 10-25-2016
Try
Code:
awk -F "," '{split ($11, T1, " "); split ($12, T2, " "); split ($13, T3, " "); print $0, T1[3], T1[2], T2[3], T2[2], T3[3]}' OFS="," file
A12345,1-4N,1-77,1-15,Name,1-77,ABC,First,John,1-77,0.1 /ser/tel 77777 0,0.1 /id 40001,0.1 /gen 111234567 0,2016-10-24 14:18:58,77777,/ser/tel,40001,/id,111234567
A67899,1-4U,1-78,1-20,Owner,1-66,XYZ,Last,Jane,1-77,0.1 /ser/web 88881 0,0.1 /ic 40002,0.1 /sac 222345678 0,2016-10-24 14:18:58,88881,/ser/web,40002,/ic,222345678

This User Gave Thanks to RudiC For This Post:
null7
# 3  
Old 10-25-2016
Hello null7,

Could you please try following and let me know if this helps.
Code:
awk -F',| ' '{print $0,$13,$12,$17,$16,$20}' OFS=,   Input_file

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
null7
# 4  
Old 10-25-2016
Quote:
Originally Posted by RudiC
Try
Code:
awk -F "," '{split ($11, T1, " "); split ($12, T2, " "); split ($13, T3, " "); print $0, T1[3], T1[2], T2[3], T2[2], T3[3]}' OFS="," file
A12345,1-4N,1-77,1-15,Name,1-77,ABC,First,John,1-77,0.1 /ser/tel 77777 0,0.1 /id 40001,0.1 /gen 111234567 0,2016-10-24 14:18:58,77777,/ser/tel,40001,/id,111234567
A67899,1-4U,1-78,1-20,Owner,1-66,XYZ,Last,Jane,1-77,0.1 /ser/web 88881 0,0.1 /ic 40002,0.1 /sac 222345678 0,2016-10-24 14:18:58,88881,/ser/web,40002,/ic,222345678

Hi RudiC, thank you for your help.. but I've problem where my last value seems to appear as exponential notation. I need to use the value in my script rather than opened it in excel and change the format. Can this be fixed?

My output:
Code:
A12345,1-4N,1-77,1-15,Name,1-77,ABC,First,John,1-77,0.1 /ser/tel 77777 0,0.1 /id 40001,0.1 /gen 111234567 0,2016-10-24 14:18:58,77777,/ser/tel,40001,/id,1.11235e+08
A67899,1-4U,1-78,1-20,Owner,1-66,XYZ,Last,Jane,1-77,0.1 /ser/web 88881 0,0.1 /ic 40002,0.1 /sac 222345678 0,2016-10-24 14:18:58,88881,/ser/web,40002,/ic,222345678

Thanks
# 5  
Old 10-25-2016
Try (untested)
Code:
awk -F "," '{split ($11, T1, " "); split ($12, T2, " "); split ($13, T3, " "); print $0, T1[3], T1[2], T2[3], T2[2], T3[3]}' OFS="," OFMT="%.0f" file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk script to append suffix to column when column has duplicated values

Please help me to get required output for both scenario 1 and scenario 2 and need separate code for both scenario 1 and scenario 2 Scenario 1 i need to do below changes only when column1 is CR and column3 has duplicates rows/values. This inputfile can contain 100 of this duplicated rows of... (1 Reply)
Discussion started by: as7951
1 Replies

2. Shell Programming and Scripting

To append new data at the end of each line based on substring of last column

Hi guys, I need to append new data at the end of each line of the files. This new data is based on substring (3rd fields) of last column. Input file xxx.csv: U1234|1-5X|orange|1-5X|Act|1-5X|0.1 /sac/orange 12345 0 U5678|1-7X|grape|1-7X|Act|1-7X|0.1 /sac/grape 5678 0... (5 Replies)
Discussion started by: null7
5 Replies

3. Shell Programming and Scripting

Taking nth column and putting its value in n+1 column using awk

Hello Members, Need your expert opinion how to tackle below. I have an input file that looks like below: USS|AWCC|AFGAW|93|70 USSAA|Roshan TDCA|AFGTD|93|72,79 ALB|Vodafone|ALBVF|355|69 ALGEE|Wataniya (Nedjma)|DZAWT|213|50,550 I like output file in below format: ... (7 Replies)
Discussion started by: umarsatti
7 Replies

4. Shell Programming and Scripting

UNIX append field with comparing fields from multiple column

I have a csv dump from sql server that needs to be converted so it can be feed to another program. I already sorted on field 1 but there are multiple columns with same field 1 where it needs to be compared against and if it is same then append field 5. i.e from ANG SJ,0,B,LC22,LC22(0) BAT... (2 Replies)
Discussion started by: nike27
2 Replies

5. Shell Programming and Scripting

Using AWK to find top Nth values in Nth column

I have an awk script to find the maximum value of the 2nd column of a 2 column datafile, but I need to find the top 5 maximum values of the 2nd column. Here is the script that works for the maximum value. awk 'BEGIN { subjectmax=$1 ; max=0} $2 >= max {subjectmax=$1 ; max=$2} END {print... (3 Replies)
Discussion started by: ncwxpanther
3 Replies

6. Shell Programming and Scripting

How to Print from nth field to mth fields using awk

Hi, Is there any short method to print from a particular field till another filed using awk? Example File: File1 ==== 1|2|acv|vbc|......|100|342 2|3|afg|nhj|.......|100|346 Expected output: File2 ==== acv|vbc|.....|100 afg|nhj|.....|100 (8 Replies)
Discussion started by: machomaddy
8 Replies

7. UNIX for Advanced & Expert Users

awk if/substring/append help

Hi All, I need some help with an awk command: What I'm trying to do is append "MYGROUP: " to text with the substring "AT_" the input file follows this format: AT_xxxxxx Name1 Name2 AT_xxxxxx NameA NameB I want the output to be: MYGROUP: AT_xxxxx Name1 Name2 MYGROUP:... (2 Replies)
Discussion started by: bikecraft
2 Replies

8. Shell Programming and Scripting

Match data based on two fields, and append to a line

I need to write a program to do something like a 'vlookup' in excel. I want to match data from file2 based on two fields (where both match) in file1, and for matching lines, add the data from two of the fields from file2 to file1. If anyone knows something in perl or awk that can do this, I'd be... (20 Replies)
Discussion started by: jamessmith01
20 Replies

9. Shell Programming and Scripting

Append char to the end of string from Nth column

I'm sure this is easy to do but I can't find a one line command with awk or sed to append a char to the end of the string from Nth column. Any sugestion please? Thanks (2 Replies)
Discussion started by: cabrao
2 Replies

10. Shell Programming and Scripting

Append the data to first column

Hi, The below is the content of the file. 008.03.50.21|ID4|0015a3f01cf3 008.04.20.16|ID3|0015a3f02337 008.04.20.17|ID4_1xVoice|00131180d80e 008.04.20.03|ID3_1xVoice|0015a3694125 008.04.30.05|ID3_1xVoice|0015a3f038af 008.06.30.17|ID3_1xVoice|00159660d454... (2 Replies)
Discussion started by: ravi_rn
2 Replies
Login or Register to Ask a Question