Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Adding column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Adding column
# 1  
Old 08-06-2014
Adding column
I have input CSV files as
Code:
TS	                DS_WKLDNM	InterfaceSpeed
29/07/2014 20:00:00	xxx112/1/18	10000000000
29/07/2014 09:00:00	xxx112/1/19	10000000000
29/07/2014 21:00:00	xxx112/1/2	        10000000000
29/07/2014 20:00:00	xxx112/1/20	10000000000
29/07/2014 20:00:00	xxx112/1/25	10000000000
29/07/2014 20:00:00	xxx112/1/26	10000000000

and i want output CSV file as

Code:
TS	                DURATION   DS_WKLDNM	OBJNM	       VALUE
29/07/2014 20:00:00	3600	xxx112/1/18	INTERFACE_C	10000000000
29/07/2014 09:00:00	3600	xxx112/1/19	INTERFACE_C	10000000000
29/07/2014 21:00:00	3600	xxx112/1/2	        INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	xxx112/1/20	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	xxx112/1/25	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	xxx112/1/26	INTERFACE_C	10000000000

Can someone help me with the code on how to convert this into expected output format. It requires two columns to be added and certains tabs in first row to be changed.

Moderator's Comments:
Mod Comment Please use code tags next time for your code and data. Thanks
Last edited by vbe; 08-06-2014 at 11:36 AM..
# 2  
Old 08-06-2014
Please use code tags as requiored by forum rules!

I can't see any <TAB> chars in your data, maybe due to missing code tags. Try
Code:
 awk 'NR==1 {$2="DURATION "$2; $3="OBJNM VALUE";print;next}{$3="3600 "$3;$4="INTERFACE_C "$4}1' file
TS DURATION DS_WKLDNM OBJNM VALUE
29/07/2014 20:00:00 3600 xxx112/1/18 INTERFACE_C 10000000000
29/07/2014 09:00:00 3600 xxx112/1/19 INTERFACE_C 10000000000
29/07/2014 21:00:00 3600 xxx112/1/2 INTERFACE_C 10000000000
29/07/2014 20:00:00 3600 xxx112/1/20 INTERFACE_C 10000000000
29/07/2014 20:00:00 3600 xxx112/1/25 INTERFACE_C 10000000000
29/07/2014 20:00:00 3600 xxx112/1/26 INTERFACE_C 10000000000

# 3  
Old 08-06-2014
Thanks Rudi..It is not giving me expected result..can you relook please

---------- Post updated at 10:37 AM ---------- Previous update was at 10:34 AM ----------

Quote:
Originally Posted by RudiC
Please use code tags as requiored by forum rules!

I can't see any <TAB> chars in your data, maybe due to missing code tags. Try
Code:
 awk 'NR==1 {$2="DURATION "$2; $3="OBJNM VALUE";print;next}{$3="3600 "$3;$4="INTERFACE_C "$4}1' file
TS DURATION DS_WKLDNM OBJNM VALUE
29/07/2014 20:00:00 3600 xxx112/1/18 INTERFACE_C 10000000000
29/07/2014 09:00:00 3600 xxx112/1/19 INTERFACE_C 10000000000
29/07/2014 21:00:00 3600 xxx112/1/2 INTERFACE_C 10000000000
29/07/2014 20:00:00 3600 xxx112/1/20 INTERFACE_C 10000000000
29/07/2014 20:00:00 3600 xxx112/1/25 INTERFACE_C 10000000000
29/07/2014 20:00:00 3600 xxx112/1/26 INTERFACE_C 10000000000


this is not working..can you please send me proper script..sorry for not using code tags...
# 4  
Old 08-06-2014
Sorry, my crystal ball is in repair. WHAT is not working?
# 5  
Old 08-06-2014
Sorry my bad i didn't put tab properly in explaining my requirements..Input file is like
Code:
	mynode	InterfaceSpeed
30/07/2014 15:00:00	atl-qa-agg-sw01.hernet1/13	1.00E+09
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/14	1.00E+09
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/15	1.00E+09
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/16	1.00E+09
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/17	1.00E+09
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/18	1.00E+09

Output file should be like

Code:
TS	DURATION	DS_WKLDNM	OBJNM	VALUE
29/07/2014 20:00:00	3600	atlqaaggsw01Ethernet1/13	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	atlqaaggsw01Ethernet1/14	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	atlqaaggsw01Ethernet1/15	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	atlqaaggsw01Ethernet1/16	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	atlqaaggsw01Ethernet1/17	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	atlqaaggsw01Ethernet1/18	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	atlqaaggsw01Ethernet1/19	INTERFACE_C	10000000000
29/07/2014 20:00:00	3600	atlqaaggsw01Ethernet1/48	INTERFACE_C	10000000000

If you observer two new rows need to be added the input mynode which comes in 2nd row should come as DS_WKLDNM file as output and in that row special characters '-' '.' should be removed.

Please let me know if this is possible.

Thanks in Advance!!!
# 6  
Old 08-06-2014
Try
Code:
awk     'NR==1  {print "TS\tDURATION\tDS_WKLDNM\tOBJNM\tVALUE";next}
                {$2="3600\t"$2;$3="INTERFACE_C\t" sprintf("%d",$3)}
         1
        ' FS="\t" OFS="\t" file
TS    DURATION    DS_WKLDNM    OBJNM    VALUE
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.hernet1/13   INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/14 INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/15 INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/16 INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/17 INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/18 INTERFACE_C    1000000000

# 7  
Old 08-06-2014
Quote:
Originally Posted by RudiC
Try
Code:
awk     'NR==1  {print "TS\tDURATION\tDS_WKLDNM\tOBJNM\tVALUE";next}
                {$2="3600\t"$2;$3="INTERFACE_C\t" sprintf("%d",$3)}
         1
        ' FS="\t" OFS="\t" file
TS    DURATION    DS_WKLDNM    OBJNM    VALUE
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.hernet1/13   INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/14 INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/15 INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/16 INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/17 INTERFACE_C    1000000000
30/07/2014 15:00:00 3600    atl-qa-agg-sw01.Ethernet1/18 INTERFACE_C    1000000000

Sorry Rudi i am not able to explain my requirement clearly if you can help me one more time that will be great. Input file

Code:
mynode	InterfaceSpeed	max_in_avgbps	avg_in_avgbps	max_out_avgbps	avg_out_avgbps	max(OutPercentUtil)	avg(OutPercentUtil)	max(InPercentUtil)	avg(InPercentUtil)
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/13	1.00E+09	2433069	421879.3908	2841680.8	628767.6217	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/14	1.00E+09	2320417.8	397569.6458	2491975.5	619813.5967	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/15	1.00E+09	37.554054	34.13236	14036.291	13765.4985	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/16	1.00E+09	15304.666	12987.29819	998.77527	812.193049	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/17	1.00E+09	9925.495	9841.887167	11343.8545	11188.63333	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/18	1.00E+09	9235.603	9076.870667	35776.91	33911.39333	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/19	1.00E+09	34.327526	34.132121	27627.633	25796.3585	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/48	1.00E+09	357729.28	357729.28	497518.3	497518.3	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet1/7	1.00E+09	1.8673587	0.338755	746.53296	612.301833	0	0	0	0
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet2/17	1.00E+10	213690608	56275550	223111312	69233119.17	2	0.583333	2	0.5
30/07/2014 15:00:00	atl-qa-agg-sw01.Ethernet2/18	1.00E+10	1116668420	382595910.8	244597376	108570014.1	2	1	11	3.833333

Expected output file
Code:
TS	DURATION	DS_WKLDNM	OBJNM	VALUE
30/07/2014 15:00:00	3600	atlqaaggsw01Ethernet1/13	INTERFACE_C	10000000000
30/07/2014 15:00:00	3600	atlqaaggsw01Ethernet1/14	INTERFACE_C	10000000000
30/07/2014 15:00:00	3600	atlqaaggsw01Ethernet1/15	INTERFACE_C	10000000000
30/07/2014 15:00:00	3600	atlqaaggsw01Ethernet1/16	INTERFACE_C	10000000000
30/07/2014 15:00:00	3600	atlqaaggsw01Ethernet1/17	INTERFACE_C	10000000000
30/07/2014 15:00:00	3600	atlqaaggsw01Ethernet1/18	INTERFACE_C	10000000000
30/07/2014 15:00:00	3600	atlqaaggsw01Ethernet1/19	INTERFACE_C	10000000000
30/07/2014 15:00:00	3600	atlqaaggsw01Ethernet1/48	INTERFACE_C	10000000000

Also i want the input mynode which comes in 2nd row should come as DS_WKLDNM as output row and in that row special characters '-' '.' should be removed.

Thanks
Last edited by Franklin52; 08-07-2014 at 03:20 AM.. Reason: fixed code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Adding a column

Hello , My requirement is to add additional column sequentially to a text file based on the column value - i/p file id1|varchar id2|varchar id3|number id4|number id5|date id6|date --------------------------------------- o/p file colv1|id1 (if second column value is varchar then... (3 Replies)
Discussion started by: Pratik4891
3 Replies

2. Shell Programming and Scripting

Special column adding

Hi Team, I would like to add two dummy columns at the end of the file. I know how to add the columns using awk . Here is my problem. we have bit strange delimiter when I od -bc it shows 244 as its binary value. 0002200 060 244 060 244 060 244 066 065 244 060 244 244 012 0... (1 Reply)
Discussion started by: gvkumar25
1 Replies

3. Shell Programming and Scripting

Adding values of a column based on another column

Hello, I have a data such as this: ENSGALG00000000189 329 G A 4 2 0 ENSGALG00000000189 518 T C 5 1 0 ENSGALG00000000189 1104 G A 5 1 0 ENSGALG00000000187 3687 G T 5 1 0 ENSGALG00000000187 4533 A T 4 2 0 ENSGALG00000000233 5811 T C 4 2 0 ENSGALG00000000233 5998 C A 5 1 0 I want to... (3 Replies)
Discussion started by: Homa
3 Replies

4. Shell Programming and Scripting

Adding of two column values

Hi cat /tmp/xx.txt 1 4 1 5 1 6 2 1 2 1 2 1 i want to add the values of 2nd column resepect to 1st column values..for 1 in 1st column i need sum of all the values in 2nd column ..pls tell me hw to do it?? (8 Replies)
Discussion started by: Aditya.Gurgaon
8 Replies

5. Shell Programming and Scripting

Adding a specified value to a specified column - awk?

Hi everyone! I sometimes need to do some simple arithmetics, like adding a number to a certain column of a file. So I wrote a small function in the .bashrc file, which looks like this shifter() { COL=$1 VAL=$2 FILE=$3 cp $FILE $FILE.shifted awk 'NF==4 {$(( $COL )) = $(( $COL ))... (6 Replies)
Discussion started by: radudownload
6 Replies

6. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (1 Reply)
Discussion started by: Vavad
1 Replies

7. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (4 Replies)
Discussion started by: Vavad
4 Replies

8. Shell Programming and Scripting

Adding column

Hello, I'm struggling with the following problem in sh script: Adding a column to the right-end of a file ("master-file": non-constant column number, tab and linux formatted) where the column is the 4th one of file1 (space and DOS formatted) Changing the header of column 4 of file 1 at the... (4 Replies)
Discussion started by: lco
4 Replies

9. UNIX for Dummies Questions & Answers

adding a column

Hi everybody, I've got a file with 36074 fields. I need to insert an additional new columns 2,3,4 and 5 after the first field. The columns to insert are the same for all the lines: it's "1" doe the new field 2 and "0" for the rest. I did have a look in the forum and the suggested solution I... (7 Replies)
Discussion started by: zajtat
7 Replies

10. UNIX for Advanced & Expert Users

Adding a column of numbers

Hello, I have a file, and one column has both positive and negative numbers. Does anyone know how I can calculate the total of all the values (i.e, +ve and -ve). eg: col1 col2 col3 data 23 data data 76 data data -30 data Thanks Khoom (1 Reply)
Discussion started by: Khoomfire
1 Replies
Login or Register to Ask a Question