I have input CSV files as
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
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.
RudiC
August 6, 2014, 10:19am
2
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
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
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 ----------
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
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...
RudiC
August 6, 2014, 10:38am
4
Sorry, my crystal ball is in repair. WHAT is not working?
Sorry my bad i didn't put tab properly in explaining my requirements..Input file is like
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
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!!!
RudiC
August 6, 2014, 11:33am
6
Try
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
rudic:
Try
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
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
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
RudiC
August 6, 2014, 12:18pm
8
Please use code tags as required by forum rules!
And, are you talking rows or columns? Where have the last three rows gone?
Wildly guessing, I came up with
awk 'NR==1 {print "TS\tDURATION\tDS_WKLDNM\tOBJNM\tVALUE";next}
{NF=3
gsub (/-/, "", $2); $2="3600\t"$2
$3="INTERFACE_C\t" sprintf("%.0f",$3)}
1
' FS="\t" OFS="\t" file
TS DURATION DS_WKLDNM OBJNM VALUE
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/13 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/14 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/15 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/16 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/17 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/18 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/19 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/48 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet1/7 INTERFACE_C 1000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet2/17 INTERFACE_C 10000000000
30/07/2014 15:00:00 3600 atlqaaggsw01.Ethernet2/18 INTERFACE_C 10000000000
I just want the values which i mentioned the rest of the columns should go off and in the code that you mentioned its not omitting special character as i requested.
RudiC
August 6, 2014, 1:29pm
10
awk 'NR==1 {print "TS\tDURATION\tDS_WKLDNM\tOBJNM\tVALUE";next}
{NF=3
gsub (/[-.]/, "", $2); $2="3600\t"$2
$3="INTERFACE_C\t" sprintf("%.0f",$3)}
1
' FS="\t" OFS="\t" file
1 Like
Sorry Rudi the code is not working as expected