Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Help formatting a .csv file
Top Forums Shell Programming and Scripting Help formatting a .csv file Post 302873707 by lestatyela on Tuesday 12th of November 2013 09:26:06 PM
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!!!
 

9 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

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

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

7. 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

8. 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

9. 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
All times are GMT -4. The time now is 02:46 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy