Help formatting a .csv file

Shell Programming and Scripting
1

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):

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):

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.

2

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:

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

RudiC, the field count that i gave is before delete fields, i mean, the original field count. That change your code?

4

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

I already run it, but not yet :frowning:

ORIGINAL.csv

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

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

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:

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

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?