Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Help formatting a .csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help formatting a .csv file
# 1  
Old 11-08-2013
Error Help formatting a .csv file
Dear friends, please your help. I got a .csv (comma separated values) file with 61 columns I need to make a script to change the original file into a new one with this actions:

Delete Columns: 1,13-20
Change the format of the following columns:
2: '2013-11-07 00:00:00' to '07/11/2013 0:00'
3: '2013-11-07 01:00:00' to '07/11/2013 1:00'
26: '98.74%' to '0.9874'
27: '100.00%' to '1'
32: '98.74%' to '0.9874'
35: '98.74%' to '0.9874'
36: '98.74%' to '0.9874'
45: '-112.0000' to '-112'
46: '-112.0000' to '-112'
48: '0.00%' to '0'
49: '100.00%' to '1'
50: '100.00%' to '1'
51: '0.00%' to '0'
52: '100.00%' to '1'
53: '100.00%' to '1'
55: '100.00%' to '1'
56: '100.00%' to '1'
57: '0.00%' to '0'
59: '100.00%' to '1'
60: '0.00%' to '0'
For example, the original file is (with headers):

Code:
Index,Start Time,End Time,Query Granularity,RNS SubNetwork,RNC Managed NE,RNC Managed NE Name,Location Name,NodeB,NodeB Name,Cell,Cell Name,OID,location(LAC),Local CellID,Latitude,Longitude,Ante Height,Ante Azimuth,Address,"Cell Traffic Volume, CS(Erl)","Traffic Volume, CS AMR(Erl)","Cell UL Traffic Volume, PS(FP)(KB)","Cell DL Traffic Volume, PS(FP)(KB)","Traffic Volume, CS 64k(Erl)",Ratio of successful RRC connection establishment(%),Ratio of successful RRC connection establishment(service relative)(%),Number of successful RRC connection establishment(Times),Number of RRC connection establishment Attempt(Times),Number of successful RRC connection establishment(service relative)(Times),Number of RRC connection establishment Attempt(service relative)(Times),Ratio of successful RAB establishment(%),Number of Successful RAB establishment(Times),Number of Attempted RAB establishment(Times),Ratio of successful CS RAB establishment(%),Ratio of successful PS RAB establishment(%),Number of Successful CS RAB establishment(Times),Number of CS RAB establishment Attempt(Times),Number of Successful PS RAB establishment(Times),Number of PS RAB establishment Attempt(Times),Number of Successful Video call RAB establishment(Times),Number of Video call RAB establishment Attempt(Times),Number of Successful SPEECH RAB establishment(Times),Number of SPEECH RAB establishment Attempt(Times),Maximum Cell Freq RTWP(dbm),Average Cell Freq RTWP(dbm),Number of rejected servicesuplink_RTWP limit(Times),Ratio of CS-Speech Call Drop(%),Call Setup Success rate(CS SPEECH)(%),Call Setup Success rate(CS Videotelephone)(%),Ratio of CS-VideoPhone Call Drop(%),Cell Inter-RAT CS Outgoing Handover Success Rate(WCDMA->GSM)(%),Cell Inter-RAT PS Outgoing Handover Success Rate(WCDMA->GPRS)(%),Cell SHO Uu Overhead,SoftHandover Success Rate(%),Call Setup Success rate(HSDPA)(%),Ratio of PS-HSDPA Call Drop(%),PS HSDPA traffic DL Throughput(MAC)(KB/s),Call Setup Success rate(HSUPA)(%),Ratio of PS-HSUPA Call Drop(%),PS HSUPA traffic UL Throughput(MAC)(KB/s)
1,2013-11-07 00:00:00,2013-11-07 01:00:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),10001,PRUEBA_ZTE_UIO_1(10001),"wcdma:OMMOID=hgiu0gfl-2@sbn=6@me=6,RncFunction=6,IubLink=1001,UtranCell=10001",30751,10001,0.0,-180.0,0,0,,0,0,0,0,0,100.00%,100.00%,0,0,0,0,100.00%,0,0,100.00%,100.00%,0,0,0,0,0,0,0,0,-112.0000,-112.0000,0,0.00%,100.00%,100.00%,0.00%,100.00%,100.00%,0,100.00%,100.00%,0.00%,0,100.00%,0.00%,0
2,2013-11-07 00:00:00,2013-11-07 01:00:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),11001,PRUEBA_ZTE_UIO_2(11001),"wcdma:OMMOID=hgiu0gfl-2@sbn=6@me=6,RncFunction=6,IubLink=1001,UtranCell=11001",30751,11001,0.0,-180.0,0,0,,0,0,0,0,0,100.00%,100.00%,0,0,0,0,100.00%,0,0,100.00%,100.00%,0,0,0,0,0,0,0,0,-112.0000,-112.0000,0,0.00%,100.00%,100.00%,0.00%,100.00%,100.00%,0,100.00%,100.00%,0.00%,0,100.00%,0.00%,0

And I need to change it to (with headers too):

Code:
Start Time,End Time,Query Granularity,RNS SubNetwork,RNC Managed NE,RNC Managed NE Name,Location Name,NodeB,NodeB Name,Cell,Cell Name,"Cell Traffic Volume, CS(Erl)","Traffic Volume, CS AMR(Erl)","Cell UL Traffic Volume, PS(FP)(KB)","Cell DL Traffic Volume, PS(FP)(KB)","Traffic Volume, CS 64k(Erl)",Ratio of successful RRC connection establishment(%),Ratio of successful RRC connection establishment(service relative)(%),Number of successful RRC connection establishment(Times),Number of RRC connection establishment Attempt(Times),Number of successful RRC connection establishment(service relative)(Times),Number of RRC connection establishment Attempt(service relative)(Times),Ratio of successful RAB establishment(%),Number of Successful RAB establishment(Times),Number of Attempted RAB establishment(Times),Ratio of successful CS RAB establishment(%),Ratio of successful PS RAB establishment(%),Number of Successful CS RAB establishment(Times),Number of CS RAB establishment Attempt(Times),Number of Successful PS RAB establishment(Times),Number of PS RAB establishment Attempt(Times),Number of Successful Video call RAB establishment(Times),Number of Video call RAB establishment Attempt(Times),Number of Successful SPEECH RAB establishment(Times),Number of SPEECH RAB establishment Attempt(Times),Maximum Cell Freq RTWP(dbm),Average Cell Freq RTWP(dbm),Number of rejected servicesuplink_RTWP limit(Times),Ratio of CS-Speech Call Drop(%),Call Setup Success rate(CS SPEECH)(%),Call Setup Success rate(CS Videotelephone)(%),Ratio of CS-VideoPhone Call Drop(%),Cell Inter-RAT CS Outgoing Handover Success Rate(WCDMA->GSM)(%),Cell Inter-RAT PS Outgoing Handover Success Rate(WCDMA->GPRS)(%),Cell SHO Uu Overhead,SoftHandover Success Rate(%),Call Setup Success rate(HSDPA)(%),Ratio of PS-HSDPA Call Drop(%),PS HSDPA traffic DL Throughput(MAC)(KB/s),Call Setup Success rate(HSUPA)(%),Ratio of PS-HSUPA Call Drop(%),PS HSUPA traffic UL Throughput(MAC)(KB/s)
07/11/2013 0:00,07/11/2013 1:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),10001,PRUEBA_ZTE_UIO_1(10001),0,0,0,0,0,1,1,0,0,0,0,1,0,0,1,1,0,0,0,0,0,0,0,0,-112,-112,0,0,1,1,0,1,1,0,1,1,0,0,1,0,0
07/11/2013 0:00,07/11/2013 1:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),11001,PRUEBA_ZTE_UIO_2(11001),0,0,0,0,0,1,1,0,0,0,0,1,0,0,1,1,0,0,0,0,0,0,0,0,-112,-112,0,0,1,1,0,1,1,0,1,1,0,0,1,0,0
07/11/2013 0:00,07/11/2013 1:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),12001,PRUEBA_ZTE_UIO_3(12001),0,0,0,0,0,1,1,0,0,0,0,1,0,0,1,1,0,0,0,0,0,0,0,0,-112,-112,0,0,1,1,0,1,1,0,1,1,0,0,1,0,0

Please your help! thank you so much.
Last edited by lestatyela; 11-08-2013 at 07:48 PM..
# 2  
Old 11-09-2013
This is not too difficult a request, but a tedious one. And, I'm not sure if the field count you give is before or after deleting fields. E.g. fields 45 and 46 don't hold 112 in either case (before deleting they're 48 and 49, after 39 and 40). Nevertheless, give this a shot as a starting point to be improved:
Code:
awk -F, '       {for (i=1;  i<12; i++) $i=$(i+1)
                 for (i=12; i<NF; i++) $i=$(i+9)
                 NF-=9}
         NR>1   {split ($1, T, /[- :]/); $1=sprintf("%2d/%2d/%4d %2d:%02d", T[3], T[2], T[1], T[4], T[5])
                 split ($2, T, /[- :]/); $2=sprintf("%2d/%2d/%4d %2d:%02d", T[3], T[2], T[1], T[4], T[5])
                 for (i=19; i<=NF; i++) if (sub("%","",$i)) $i/=100
                 sub (".0000", "", $39)
                 sub (".0000", "", $40)
                }
                 1
        ' OFS=, CONVFMT="%.4f" file

and come back with results/errors...
# 3  
Old 11-09-2013
RudiC, the field count that i gave is before delete fields, i mean, the original field count. That change your code?
# 4  
Old 11-10-2013
I thought so and based the code on it. Just give it a shot... then, try to adapt to your needs and come back for help should you not succeed.
# 5  
Old 11-12-2013
Quote:
Originally Posted by RudiC
I thought so and based the code on it. Just give it a shot... then, try to adapt to your needs and come back for help should you not succeed.
I already run it, but not yet Smilie

ORIGINAL.csv
Code:
Index,Start Time,End Time,Query Granularity,RNS SubNetwork,RNC Managed NE,RNC Managed NE Name,Location Name,NodeB,NodeB Name,Cell,Cell Name,OID,location(LAC),Local CellID,Latitude,Longitude,Ante Height,Ante Azimuth,Address,"Cell Traffic Volume, CS(Erl)","Traffic Volume, CS AMR(Erl)","Cell UL Traffic Volume, PS(FP)(KB)","Cell DL Traffic Volume, PS(FP)(KB)","Traffic Volume, CS 64k(Erl)",Ratio of successful RRC connection establishment(%),Ratio of successful RRC connection establishment(service relative)(%),Number of successful RRC connection establishment(Times),Number of RRC connection establishment Attempt(Times),Number of successful RRC connection establishment(service relative)(Times),Number of RRC connection establishment Attempt(service relative)(Times),Ratio of successful RAB establishment(%),Number of Successful RAB establishment(Times),Number of Attempted RAB establishment(Times),Ratio of successful CS RAB establishment(%),Ratio of successful PS RAB establishment(%),Number of Successful CS RAB establishment(Times),Number of CS RAB establishment Attempt(Times),Number of Successful PS RAB establishment(Times),Number of PS RAB establishment Attempt(Times),Number of Successful Video call RAB establishment(Times),Number of Video call RAB establishment Attempt(Times),Number of Successful SPEECH RAB establishment(Times),Number of SPEECH RAB establishment Attempt(Times),Maximum Cell Freq RTWP(dbm),Average Cell Freq RTWP(dbm),Number of rejected servicesuplink_RTWP limit(Times),Ratio of CS-Speech Call Drop(%),Call Setup Success rate(CS SPEECH)(%),Call Setup Success rate(CS Videotelephone)(%),Ratio of CS-VideoPhone Call Drop(%),Cell Inter-RAT CS Outgoing Handover Success Rate(WCDMA->GSM)(%),Cell Inter-RAT PS Outgoing Handover Success Rate(WCDMA->GPRS)(%),Cell SHO Uu Overhead,SoftHandover Success Rate(%),Call Setup Success rate(HSDPA)(%),Ratio of PS-HSDPA Call Drop(%),PS HSDPA traffic DL Throughput(MAC)(KB/s),Call Setup Success rate(HSUPA)(%),Ratio of PS-HSUPA Call Drop(%),PS HSUPA traffic UL Throughput(MAC)(KB/s)
1,2013-11-07 00:00:00,2013-11-07 01:00:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),10001,PRUEBA_ZTE_UIO_1(10001),"wcdma:OMMOID=hgiu0gfl-2@sbn=6@me=6,RncFunction=6,IubLink=1001,UtranCell=10001",30751,10001,0.0,-180.0,0,0,,0,0,0,0,0,100.00%,100.00%,0,0,0,0,100.00%,0,0,100.00%,100.00%,0,0,0,0,0,0,0,0,-112.0000,-112.0000,0,0.00%,100.00%,100.00%,0.00%,100.00%,100.00%,0,100.00%,100.00%,0.00%,0,100.00%,0.00%,0
2,2013-11-07 00:00:00,2013-11-07 01:00:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),11001,PRUEBA_ZTE_UIO_2(11001),"wcdma:OMMOID=hgiu0gfl-2@sbn=6@me=6,RncFunction=6,IubLink=1001,UtranCell=11001",30751,11001,0.0,-180.0,0,0,,0,0,0,0,0,100.00%,100.00%,0,0,0,0,100.00%,0,0,100.00%,100.00%,0,0,0,0,0,0,0,0,-112.0000,-112.0000,0,0.00%,100.00%,100.00%,0.00%,100.00%,100.00%,0,100.00%,100.00%,0.00%,0,100.00%,0.00%,0
3,2013-11-07 00:00:00,2013-11-07 01:00:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),12001,PRUEBA_ZTE_UIO_3(12001),"wcdma:OMMOID=hgiu0gfl-2@sbn=6@me=6,RncFunction=6,IubLink=1001,UtranCell=12001",30751,12001,0.0,-180.0,0,0,,0,0,0,0,0,100.00%,100.00%,0,0,0,0,100.00%,0,0,100.00%,100.00%,0,0,0,0,0,0,0,0,-112.0000,-112.0000,0,0.00%,100.00%,100.00%,0.00%,100.00%,100.00%,0,100.00%,100.00%,0.00%,0,100.00%,0.00%,0
4,2013-11-07 00:00:00,2013-11-07 01:00:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1004,AMBATO_CENTRO(1004),36004,SW_UTG_AMBATO_CENTRO_1(36004),"wcdma:OMMOID=hgiu0gfl-2@sbn=6@me=6,RncFunction=6,IubLink=1004,UtranCell=36004",30321,36004,0.0,-180.0,0,0,,1.0631,1.0631,18864.0390,24524.2920,0,99.92%,99.95%,2577,2581,2188,2190,100.00%,2114,2114,100.00%,100.00%,16,16,2098,2098,0,0,16,16,-89.7000,-98.9125,0,0.00%,99.95%,100.00%,0.00%,100.00%,100.00%,0.3290,100.00%,99.95%,0.18%,6.7953,100.00%,0.00%,0
5,2013-11-07 00:00:00,2013-11-07 01:00:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1004,AMBATO_CENTRO(1004),37004,SW_UTG_AMBATO_CENTRO_2(37004),"wcdma:OMMOID=hgiu0gfl-2@sbn=6@me=6,RncFunction=6,IubLink=1004,UtranCell=37004",30321,37004,0.0,-180.0,0,0,,0.2289,0.2289,5176.0140,7058.7700,0,100.00%,100.00%,679,681,527,528,100.00%,518,518,100.00%,100.00%,3,3,515,515,0,0,3,3,-91.8000,-104.4990,0,0.00%,100.00%,100.00%,0.00%,100.00%,100.00%,0.9356,100.00%,100.00%,0.00%,1.8816,100.00%,0.00%,1.4133

whised FINAL.csv
Code:
Start Time,End Time,Query Granularity,RNS SubNetwork,RNC Managed NE,RNC Managed NE Name,Location Name,NodeB,NodeB Name,Cell,Cell Name,"Cell Traffic Volume, CS(Erl)","Traffic Volume, CS AMR(Erl)","Cell UL Traffic Volume, PS(FP)(KB)","Cell DL Traffic Volume, PS(FP)(KB)","Traffic Volume, CS 64k(Erl)",Ratio of successful RRC connection establishment(%),Ratio of successful RRC connection establishment(service relative)(%),Number of successful RRC connection establishment(Times),Number of RRC connection establishment Attempt(Times),Number of successful RRC connection establishment(service relative)(Times),Number of RRC connection establishment Attempt(service relative)(Times),Ratio of successful RAB establishment(%),Number of Successful RAB establishment(Times),Number of Attempted RAB establishment(Times),Ratio of successful CS RAB establishment(%),Ratio of successful PS RAB establishment(%),Number of Successful CS RAB establishment(Times),Number of CS RAB establishment Attempt(Times),Number of Successful PS RAB establishment(Times),Number of PS RAB establishment Attempt(Times),Number of Successful Video call RAB establishment(Times),Number of Video call RAB establishment Attempt(Times),Number of Successful SPEECH RAB establishment(Times),Number of SPEECH RAB establishment Attempt(Times),Maximum Cell Freq RTWP(dbm),Average Cell Freq RTWP(dbm),Number of rejected servicesuplink_RTWP limit(Times),Ratio of CS-Speech Call Drop(%),Call Setup Success rate(CS SPEECH)(%),Call Setup Success rate(CS Videotelephone)(%),Ratio of CS-VideoPhone Call Drop(%),Cell Inter-RAT CS Outgoing Handover Success Rate(WCDMA->GSM)(%),Cell Inter-RAT PS Outgoing Handover Success Rate(WCDMA->GPRS)(%),Cell SHO Uu Overhead,SoftHandover Success Rate(%),Call Setup Success rate(HSDPA)(%),Ratio of PS-HSDPA Call Drop(%),PS HSDPA traffic DL Throughput(MAC)(KB/s),Call Setup Success rate(HSUPA)(%),Ratio of PS-HSUPA Call Drop(%),PS HSUPA traffic UL Throughput(MAC)(KB/s)
07/11/2013 0:00,07/11/2013 1:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),10001,PRUEBA_ZTE_UIO_1(10001),0,0,0,0,0,1,1,0,0,0,0,1,0,0,1,1,0,0,0,0,0,0,0,0,-112,-112,0,0,1,1,0,1,1,0,1,1,0,0,1,0,0
07/11/2013 0:00,07/11/2013 1:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),11001,PRUEBA_ZTE_UIO_2(11001),0,0,0,0,0,1,1,0,0,0,0,1,0,0,1,1,0,0,0,0,0,0,0,0,-112,-112,0,0,1,1,0,1,1,0,1,1,0,0,1,0,0
07/11/2013 0:00,07/11/2013 1:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),12001,PRUEBA_ZTE_UIO_3(12001),0,0,0,0,0,1,1,0,0,0,0,1,0,0,1,1,0,0,0,0,0,0,0,0,-112,-112,0,0,1,1,0,1,1,0,1,1,0,0,1,0,0
07/11/2013 0:00,07/11/2013 1:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1004,AMBATO_CENTRO(1004),36004,SW_UTG_AMBATO_CENTRO_1(36004),1.0631,1.0631,18864.039,24524.292,0,0.9992,0.9995,2577,2581,2188,2190,1,2114,2114,1,1,16,16,2098,2098,0,0,16,16,-89.7,-98.9125,0,0,0.9995,1,0,1,1,0.329,1,0.9995,0.0018,6.7953,1,0,0
07/11/2013 0:00,07/11/2013 1:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1004,AMBATO_CENTRO(1004),37004,SW_UTG_AMBATO_CENTRO_2(37004),0.2289,0.2289,5176.014,7058.77,0,1,1,679,681,527,528,1,518,518,1,1,3,3,515,515,0,0,3,3,-91.8,-104.499,0,0,1,1,0,1,1,0.9356,1,1,0,1.8816,1,0,1.4133

Actual FINAL.csv
Code:
Start Time,End Time,Query Granularity,RNS SubNetwork,RNC Managed NE,RNC Managed NE Name,Location Name,NodeB,NodeB Name,Cell,Cell Name,"Cell Traffic Volume, CS(Erl)","Traffic Volume, CS AMR(Erl)","Cell UL Traffic Volume, PS(FP)(KB)","Cell DL Traffic Volume, PS(FP)(KB)","Traffic Volume, CS 64k(Erl)",Ratio of successful RRC connection establishment(%),Ratio of successful RRC connection establishment(service relative)(%),Number of successful RRC connection establishment(Times),Number of RRC connection establishment Attempt(Times),Number of successful RRC connection establishment(service relative)(Times),Number of RRC connection establishment Attempt(service relative)(Times),Ratio of successful RAB establishment(%),Number of Successful RAB establishment(Times),Number of Attempted RAB establishment(Times),Ratio of successful CS RAB establishment(%),Ratio of successful PS RAB establishment(%),Number of Successful CS RAB establishment(Times),Number of CS RAB establishment Attempt(Times),Number of Successful PS RAB establishment(Times),Number of PS RAB establishment Attempt(Times),Number of Successful Video call RAB establishment(Times),Number of Video call RAB establishment Attempt(Times),Number of Successful SPEECH RAB establishment(Times),Number of SPEECH RAB establishment Attempt(Times),Maximum Cell Freq RTWP(dbm),Average Cell Freq RTWP(dbm),Number of rejected servicesuplink_RTWP limit(Times),Ratio of CS-Speech Call Drop(%),Call Setup Success rate(CS SPEECH)(%),Call Setup Success rate(CS Videotelephone)(%),Ratio of CS-VideoPhone Call Drop(%),Cell Inter-RAT CS Outgoing Handover Success Rate(WCDMA->GSM)(%),Cell Inter-RAT PS Outgoing Handover Success Rate(WCDMA->GPRS)(%),Cell SHO Uu Overhead,SoftHandover Success Rate(%),Call Setup Success rate(HSDPA)(%),Ratio of PS-HSDPA Call Drop(%),PS HSDPA traffic DL Throughput(MAC)(KB/s),Call Setup Success rate(HSUPA)(%),Ratio of PS-HSUPA Call Drop(%),PS HSUPA traffic UL Throughput(MAC)(KB/s)
 0/ 0/   0  0:00, 0/ 0/   0  0:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),10001,PRUEBA_ZTE_UIO_1(10001),0,0,,0,0,0,0,0,1.0000,1.0000,0,0,0,0,1.0000,0,0,1.0000,1.0000,0,0,0,0,0,0,0,0,-112,-112,0,0.0000,1.0000,1.0000,0.0000,1.0000,1.0000,0,1.0000,1.0000,0.0000,0,1.0000,0.0000,0
 0/ 0/   0  0:00, 0/ 0/   0  0:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),11001,PRUEBA_ZTE_UIO_2(11001),0,0,,0,0,0,0,0,1.0000,1.0000,0,0,0,0,1.0000,0,0,1.0000,1.0000,0,0,0,0,0,0,0,0,-112,-112,0,0.0000,1.0000,1.0000,0.0000,1.0000,1.0000,0,1.0000,1.0000,0.0000,0,1.0000,0.0000,0
 0/ 0/   0  0:00, 0/ 0/   0  0:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1001,PRUEBA_ZTE_UIO(1001),12001,PRUEBA_ZTE_UIO_3(12001),0,0,,0,0,0,0,0,1.0000,1.0000,0,0,0,0,1.0000,0,0,1.0000,1.0000,0,0,0,0,0,0,0,0,-112,-112,0,0.0000,1.0000,1.0000,0.0000,1.0000,1.0000,0,1.0000,1.0000,0.0000,0,1.0000,0.0000,0
 0/ 0/   0  0:00, 0/ 0/   0  0:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1004,AMBATO_CENTRO(1004),36004,SW_UTG_AMBATO_CENTRO_1(36004),0,0,,1.0631,1.0631,18864.0390,24524.2920,0,0.9992,0.9995,2577,2581,2188,2190,1.0000,2114,2114,1.0000,1.0000,16,16,2098,2098,0,0,16,16,-89.7000,-98.9125,0,0.0000,0.9995,1.0000,0.0000,1.0000,1.0000,0.3290,1.0000,0.9995,0.0018,6.7953,1.0000,0.0000,0
 0/ 0/   0  0:00, 0/ 0/   0  0:00,1 hour(s),6,6,ME(UMTS)(6),Quito,1004,AMBATO_CENTRO(1004),37004,SW_UTG_AMBATO_CENTRO_2(37004),0,0,,0.2289,0.2289,5176.0140,7058.7700,0,1.0000,1.0000,679,681,527,528,1.0000,518,518,1.0000,1.0000,3,3,515,515,0,0,3,3,-91.8000,-104.4990,0,0.0000,1.0000,1.0000,0.0000,1.0000,1.0000,0.9356,1.0000,1.0000,0.0000,1.8816,1.0000,0.0000,1.4133

Script used:
Code:
awk -F, '       {for (i=1;  i<12; i++) $i=$(i+1)
                 for (i=12; i<NF; i++) $i=$(i+9)
                 NF-=9}
         NR>1   {split ($1, T, /[- :]/); $1=sprintf("%2d/%2d/%4d %2d:%02d", T[3], T[2], T[1], T[4], T[5])
                 split ($2, T, /[- :]/); $2=sprintf("%2d/%2d/%4d %2d:%02d", T[3], T[2], T[1], T[4], T[5])
                 for (i=19; i<=NF; i++) if (sub("%","",$i)) $i/=100
                 sub (".0000", "", $39)
                 sub (".0000", "", $40)
                }
                 1
        ' OFS=, CONVFMT="%.4f" ORIGINAL.csv > FINAL.csv

Please your help dear RudiC!!!
# 6  
Old 11-13-2013
That may be a problem with awk versions. What be your system's data (OS, awk version)? Looks like your awk doesn't like the split function syntax used - pls be referred to the man page. For your fields 1 and 2, you may need to fiddle around with the format strings to sprintf. For the other fields, if you can't use the CONVFMT="%.4f", you may need to use a format string of its own for every single field (mayhap you want to define a function for that?).
Why does your empty original field 23 suddenly have a value in your desired output file?
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

2. Shell Programming and Scripting

Data formatting in CSV file to EXCEL

Hello friends I want to convert an csv file on unix (which is generated by a ETL application) to a formatted excel sheet like .I have roughly like 28 columns 1)All numbers need to be stored as numbers with leading zeros-like format as text for this column to preserve leading zeroes e.g... (6 Replies)
Discussion started by: etldev
6 Replies

3. Shell Programming and Scripting

Formatting csv file

Hello I wrote a perl script to output a text file filled with ip addresses and ports that i scanned into microsoft excel. Now that the data is in excel my boss wants me to organize the file in csv format such as Server,port,protocol,random,random,random ns1,25,tcp,stuff,stuff,stuff Can... (0 Replies)
Discussion started by: kingbp
0 Replies

4. Shell Programming and Scripting

Formatting csv file in Unix script

Hi, Am using the following command to create a csv file paste File_1.csv File_4.csv File_7.csv >Out1.csv paste File_2.csv File_5.csv File_8.csv >>Out1.csv paste File_3.csv File_4.csv File_9.csv >>Out1.csv Input Data: Expected Output: Column 1 Column 2 ... (2 Replies)
Discussion started by: meva
2 Replies

5. Shell Programming and Scripting

formatting into CSV format of SQL session output

I am getting a no of fields from a SQL session (e.g. select a,b,c from table). How do I convert the output values into CSV format . The output should be like this 'a','b','c', (4 Replies)
Discussion started by: mady135
4 Replies

6. Shell Programming and Scripting

CSV file horizontal formatting

Hi folks, Please help me with csv file formatting which needs to be done in horizontal fashion. I have data coming in separate files every hour. What I need to do is extract three values into csv file. In the next hour, I need to extract the new values beside the three values which were... (1 Reply)
Discussion started by: vharsha
1 Replies

7. Shell Programming and Scripting

Formatting Data - CSV

I want to check whether if any column data has any + , - , = prefixed to it then convert it in such a form that in excel its not read as formula. echo "$DATA" | awk 'BEGIN { OFS="," } -F" " {print $1,$2,$3,$4,$5,$6,$7,$8.$9,$10,$11,$12}' (4 Replies)
Discussion started by: dinjo_jo
4 Replies

8. Shell Programming and Scripting

CSV formatting with prefixing, appending and padding field

Hi I have a very large csv file with some hundreds of thousands of rows of data. The data is in the following format: Up to four alpha numeric characters for the first word. This is either set as 2 characters followed by 2 spaces, or as a single 4character word. This is then followed by an 8... (7 Replies)
Discussion started by: meself
7 Replies

9. Shell Programming and Scripting

Help with formatting a csv file

Hi, Can anyone help me format a csv file. My csv file have 6 columns. the last 2 columns were put on the second row. Here is the sample output: 000584, 200829014,30,PAPER, 4200059000,'DIXIE BOWLS HEAVY DUTY DISPOSABLE 10 OZ 1. How can I put the last 2 columns in the first row,... (1 Reply)
Discussion started by: softwood
1 Replies
Login or Register to Ask a Question