Pax1.csv file manipulation


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Pax1.csv file manipulation
# 1  
Old 08-22-2014
Pax1.csv file manipulation

Hallo Team and happy Friday,

I have a .csv file attached called MHG_Only_Billing_only_great_201408_SummaryDetails.csv

The file is as below:
Code:
[mind@util1-pkl MHG_201408]$ cat MHG_Only_Billing_only_great_201408_SummaryDetails.csv
AccountCode,AccountName,TotalCalls,TotalCallDuration,TotalCost,TotalTelkomAsIf,ContactCalls,ContactCallDuration,ContactCallCost,ContactTotalTelkomAsIf,LocalCalls,LocalCallDuration,LocalCallCost,LocalTotalTelkomAsIf,NationalCalls,NationalCallDuration,NationalCallCost,NationalTotalTelkomAsIf,CellularCalls,CellularCallDuration,CellularCallCost,CellularTotalTelkomAsIf,CommunityCalls,CommunityCallDuration,CommunityCallCost,CommunityTotalTelkomAsIf,GlobalCalls,GlobalCallDuration,GlobalCallCost,GlobalTotalTelkomAsIf,FirstCallDate,LastCallDate
485554,MHG - Polmed - BLM,415,06:46:27,132.88,437.72,0,00:00:00,0,0,1,00:12:37,4.42,4.65,39,02:09:48,36.13,49.68,105,02:31:55,85.56,232.55,17,00:45:10,6.77,23.28,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
758495,MGH -  Polmed -  Polokwane,587,13:40:35,235.73,713.02,0,00:00:00,0,0,1,00:04:15,1.49,1.57,75,02:41:39,46.64,66.23,200,05:01:50,167.98,435.59,42,02:10:49,19.62,69.94,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
661178,MHG - Cape Town,238878,14210:29:27,230762.11,558658.12,39819,4033:46:29,36303.97,122416,23486,997:24:45,15112.02,25513.91,55683,3462:23:34,57864.36,96828.61,83028,2844:30:26,94609.77,240402.11,36844,2870:38:01,26767.37,73344.1,18,01:46:12,104.6,154.3,22-JUL-2014,21-AUG-2014
668900,MHG - Polmed - Kimberly,224,03:36:33,72.23,237.76,0,00:00:00,0,0,0,00:00:00,0,0,19,00:58:44,15.36,26.43,51,01:26:46,48.94,119.05,18,00:52:53,7.93,27.68,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
631005,MHG - Polmed - Nelspruit,1426,38:15:09,1054.03,2695.12,0,00:00:00,0,0,1,00:00:10,0.06,0.5,220,08:26:01,129.18,204.46,1083,28:10:58,914.5,2412.22,52,01:08:42,10.3,42.36,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
822234,MHG - Pretoria,94738,7399:38:47,94198.51,273010.77,60709,5875:49:54,53006.06,178499.14,5142,222:23:39,3363.99,5571.01,3663,193:23:56,3216.58,4723.23,23787,1019:04:50,33809.97,81823.8,1437,88:56:28,801.9,2394.49,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
520704,MHG - Polmed - Umtata,267,13:21:46,273.79,693.19,0,00:00:00,0,0,0,00:00:00,0,0,26,01:53:45,34.43,52.04,146,05:35:57,187.43,452.07,61,05:46:12,51.93,173.78,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
155945,MHG- Vois - Durban,18988,999:46:49,18354.78,36190.84,0,00:00:00,0,0,3504,157:58:41,2384.77,3936.31,5216,285:28:38,4727.31,6449.08,6866,241:12:39,8096.79,20232.9,3402,315:06:51,3145.9,5572.56,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
781681,MHG - Polmed - DBN,486,18:29:35,516.74,1212.01,0,00:00:00,0,0,1,00:00:03,0.02,0.5,69,03:07:20,53.84,76.02,384,13:27:41,446.34,1085.64,31,01:50:15,16.54,56.21,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
298334,MHG - Durban,121,07:13:34,65.03,220.06,121,07:13:34,65.03,221.13,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,18-AUG-2014,21-AUG-2014
146536,MHG - Polmed - PE,907,19:02:36,423.68,1266.53,0,00:00:00,0,0,0,00:00:00,0,0,97,06:00:02,100.35,184.16,204,08:41:21,311.82,743.63,26,01:43:06,11.51,49.06,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
563416,MHG - Braamfontein,96329,7622:36:01,106670.89,264574.65,9063,673:10:03,6332.19,20613.26,39093,2521:42:32,36332.08,68009.39,3684,243:20:27,3939.92,5789.69,23616,916:25:09,30427.34,74614.67,20846,3266:09:43,29399.49,95251.26,27,01:48:07,239.87,298.45,22-JUL-2014,21-AUG-2014
995958,MHG - Polmed - Paarl,356,15:40:53,412.96,941.17,0,00:00:00,0,0,2,00:00:43,0.24,1,41,02:45:59,43.31,49.88,168,10:39:36,353.03,775.38,27,01:49:12,16.38,56.38,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
347012,MHG - Polmed - Mafikeng,522,13:34:18,370.33,949.97,0,00:00:00,0,0,1,00:01:28,0.51,0.54,64,02:46:03,48.38,69.75,287,09:36:14,316.66,785.55,18,00:32:57,4.78,16.35,0,00:00:00,0,0,22-JUL-2014,21-AUG-2014
616356,MHG - QUALSA-POLOKWANE,2,00:00:17,0,0.5,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,0,00:00:00,0,0,01-AUG-2014,14-AUG-2014

What i would like to finally achieve is to calculate all the totals minus the Contact columns. Have a look at the .zip attachment to get an idea of what i am talking about. The MHG - Inbound Final line as per my .zip file should be > to a file called pax1.csv as below

Code:
cat pax1.csv
MHG - Inbound Final,344534,5581:43:20,357836.44,820051.9,,,,643499.06,142463,3899:48:53,114394.78,206074.11,137753,4215:25:56,140475.45,229088.84,279745,5106:25:22,339466.7,847997.77,125625,6557:30:19,120514.07,354131.62,90,3:34:19,344.47,452.75,22-Jul-14,21-Aug-14

Please let me know if you need more clarity.

Regards and thank you in advance. Love and peace.

Last edited by Franklin52; 08-22-2014 at 11:21 AM.. Reason: Please use code tags instead of inline code tags for multiple rows
# 2  
Old 08-22-2014
I re posted a new post which is clearer.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 Replies

2. Shell Programming and Scripting

Save output of updated csv file as csv file itself

Hi, all I want to sort a csv file based on timestamp from oldest to newest and save the output as csv file itself. Here is an example of my csv file. test.csv SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0739.JPG,2015:02:17 11:32:21 /home/intannf/foto/IMG_0749.JPG,2015:02:17 11:37:28... (10 Replies)
Discussion started by: refrain
10 Replies

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

4. Shell Programming and Scripting

.csv file manipulation

Hall0 Team, I need you help. I have a .csv file called Pax.csv. It has 13 fields The fields that i am interested in is field 7, field 10(CallDuration),field 12 and field 13(TelkomAsif) Field 12(Cost) is affected by what is on field 7(CallType) if field7= VoIS Community Sharecall then Cost... (14 Replies)
Discussion started by: kekanap
14 Replies

5. Shell Programming and Scripting

Data Manipulation on a .csv file

Hallo Friends, I need you help. My file has 5000 or so lines and currently looks like below(sample). Service Type,Origin,Destination,Rate Per Minute,Minimum Charge,Time Based Rate,Time Based From Day,Time Based To Day,Time Based From Time,Time Based To Time,Destination Prefix List,, VoIS... (3 Replies)
Discussion started by: kekanap
3 Replies

6. Shell Programming and Scripting

CSV data format manipulation

Hi There I need a script which will pick up the data from a .CSV file and reformat it as per the requirement and write it to another .CSV file. I am using an application that will only take data in a particular format and need something that will convert without manual intervention. The... (4 Replies)
Discussion started by: rbggbr16
4 Replies

7. Shell Programming and Scripting

Awk to convert a text file to CSV file with some string manipulation

Hi , I have a simple text file with contents as below: 12345678900 971,76 4234560890 22345678900 5971,72 5234560990 32345678900 71,12 6234560190 the new csv-file should be like: Column1;Column2;Column3;Column4;Column5 123456;78900;971,76;423456;0890... (9 Replies)
Discussion started by: FreddyDaKing
9 Replies

8. Shell Programming and Scripting

csv manipulation

Hi, I want to delete the null values that are present in all the columns except the last column $ cat x.csv baseball,NULL,8798765,Most played,0,5,12367,NULL,NULL,98,67,Reason is not sufficient baseball,NULL,8928192,Most played,0,4,76893,NULL,RAW,54,78,NULL baseball,NULL,5678945,Most... (6 Replies)
Discussion started by: scripter12
6 Replies

9. Shell Programming and Scripting

csv file manipulation

Hi, I generated a csv file.But i have to manipulate it to make it user friendly. This is the structure of the input csv file. partyfailurereasonnumbercvfrejected12cvfapproved45cvfonhold23mvcunknown98mvconcall17pucapproved78puchold34actunknown19 The first line is the header. The... (6 Replies)
Discussion started by: amit.behera
6 Replies
Login or Register to Ask a Question