Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

.csv file manipulation

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting .csv file manipulation
# 1  
Old 06-27-2014
.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 = (CallDuration*0.19)/60
if fiel7 = VoIS Contact Sharecall Overflow then Cost =(CallDuration*0.4)/60
and everything on column 13 (TelkomASif) the following should apply
TelkomAsif=(column13*0.5)/60

Below is a a sample of my .csv file:

Code:
[mind@util1-pkl MHG_201406]$ cat Pax.csv|cut -f7 -d","|sort -u
CallType
VoIS Community Sharecall
VoIS Community Tollfree
VoIS Contact Sharecall Overflow
VoIS Contact Tollfree Overflow
[mind@util1-pkl MHG_201406]$ cat Pax.csv
AccountCode,AccountName,UniqueCallId,FromNumber,ToNumber,DestinationDescription,CallType,CallDate,CallTime,CallDuration,CallDurationSec,Cost,TelkomAsIf
162008,MHG - Merchant House,3093361537:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,01-JUN-2014,07:07:43,00:00:31,31,.0878,0.0982
162008,MHG - Merchant House,3093441387:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,01-JUN-2014,08:00:15,00:00:42,42,.119,0.133
162008,MHG - Merchant House,3093683471:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,01-JUN-2014,10:07:22,00:00:04,4,.0113,0.0127
162008,MHG - Merchant House,3093712327:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,01-JUN-2014,10:21:49,00:00:07,7,.0198,0.0222
162008,MHG - Merchant House,3093722407:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Contact Tollfree Overflow,01-JUN-2014,10:27:09,00:00:20,20,.0567,0.0633
162008,MHG - Merchant House,3093774963:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Contact Tollfree Overflow,01-JUN-2014,10:51:40,00:00:39,39,.1105,0.1235
162008,MHG - Merchant House,3093865485:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Contact Tollfree Overflow,01-JUN-2014,11:37:43,00:00:08,8,.0227,0.0253
162008,MHG - Merchant House,3093888693:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Contact Sharecall Overflow,01-JUN-2014,11:49:03,00:00:57,57,.1615,0.1805
162008,MHG - Merchant House,3093895657:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Contact Sharecall Overflow,01-JUN-2014,11:52:17,00:01:06,66,.187,0.209
162008,MHG - Merchant House,3094046721:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Contact Sharecall Overflow,01-JUN-2014,13:06:52,00:00:45,45,.1275,0.1425
162008,MHG - Merchant House,3094079577:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Contact Sharecall Overflow,01-JUN-2014,13:23:39,00:03:41,221,.6262,0.6998
162008,MHG - Merchant House,3094088775:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Community Tollfree,01-JUN-2014,13:28:36,00:00:05,5,.0142,0.0158
162008,MHG - Merchant House,3094090123:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Community Tollfree,01-JUN-2014,13:29:10,00:01:05,65,.1842,0.2058
162008,MHG - Merchant House,3094158063:0A,0214681313,0860004367,Telkom Non-Geographic Universal Services,VoIS Community Tollfree,01-JUN-2014,14:07:38,00:00:18,18,.051,0.057
[mind@util1-pkl MHG_201406]$

Thanking you in advance for your help.

Regards,

Pax
Last edited by vbe; 06-27-2014 at 01:00 PM.. Reason: code tags not ICODE please, thanks
# 2  
Old 06-27-2014
Code:
awk -F, -v OFS="," '
$7 ~ /VoIS Community Sharecall/ { $12=($10*0.19)/60 }
$7 ~ /VoIS Contact Sharecall Overflow/ { $12=($10*0.4)/60 }
{ $13=($13*0.5)/60 }
{ print }' inputfile > outputfile

# 3  
Old 06-27-2014
just little modfication
Code:
$7 == "VoIS Community Sharecall"
$7 == "VoIS Contact Sharecall Overflow"

because if $7 contains "VoIS Community Sharecall XXX" then criteria should not satisfy for first check.
# 4  
Old 06-27-2014
Your method will fail when given things like stray spaces and the like, which are often present in data people post here despite their misguided efforts to "pretty it up" before posting. There's a reason I used //.
# 5  
Old 06-28-2014
Hallo Team,

This is how the file looks like before i run the command that you suggested i run:

Code:
[mind@util1-pkl MHG_201406]$ cat Pax.csv
AccountCode,AccountName,UniqueCallId,FromNumber,ToNumber,DestinationDescription,CallType,CallDate,CallTime,CallDuration,CallDurationSec,Cost,TelkomAsIf
563416,MHG - Braamfontein,3260306859:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,20-JUN-2014,17:03:56,00:00:05,5,.0125,0.5
563416,MHG - Braamfontein,3275326523:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,23-JUN-2014,14:19:07,00:00:20,20,.05,0.5
563416,MHG - Braamfontein,3280178113:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,24-JUN-2014,08:06:23,00:00:06,6,.015,0.5
563416,MHG - Braamfontein,3305018045:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,26-JUN-2014,10:32:13,00:00:06,6,.015,0.5
563416,MHG - Braamfontein,3305020549:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,26-JUN-2014,10:32:22,00:00:04,4,.01,0.5
563416,MHG - Braamfontein,3064064999:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,28-MAY-2014,12:05:07,00:00:06,6,.015,0.5
563416,MHG - Braamfontein,3071554761:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,29-MAY-2014,09:12:05,00:00:20,20,.05,0.5
563416,MHG - Braamfontein,3076236709:0A,0860101280,0113812027,Johannesburg area,VoIS Contact Sharecall Overflow,29-MAY-2014,14:07:51,00:00:01,1,.0025,0.5
563416,MHG - Braamfontein,3082567491:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,30-MAY-2014,10:00:47,00:00:07,7,.0175,0.5
661178,MHG - Cape Town,3257924103:0A,0860102472,0214804774,Cape Town area,VoIS Contact Sharecall Overflow,20-JUN-2014,14:07:13,00:00:16,16,.04,0.5
661178,MHG - Cape Town,3059920319:1830,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,28-MAY-2014,10:52:34,00:10:52,652,1.8473,2.7167
661178,MHG - Cape Town,3059920319:1890,0214804511,0860104111,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,28-MAY-2014,10:57:09,00:10:01,601,1.7028,2.5042
661178,MHG - Cape Town,3070357179:360,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,29-MAY-2014,08:46:20,00:20:39,1239,3.5105,5.1625
661178,MHG - Cape Town,3070357179:824,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,29-MAY-2014,09:35:03,00:18:44,1124,3.1847,4.6833
661178,MHG - Cape Town,3070357179:1103,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,29-MAY-2014,10:03:07,00:00:48,48,.136,0.2
661178,MHG - Cape Town,3070357179:4351,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,29-MAY-2014,14:17:10,00:07:22,442,1.2523,1.8417
661178,MHG - Cape Town,3080634155:1403,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,30-MAY-2014,10:28:53,00:04:43,283,.8018,1.1792
661178,MHG - Cape Town,3080634155:3172,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,30-MAY-2014,13:01:29,00:06:22,382,1.0823,1.5917
661178,MHG - Cape Town,3080634155:3622,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,30-MAY-2014,13:38:33,00:03:08,188,.5327,0.7833
661178,MHG - Cape Town,3080634155:3648,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,30-MAY-2014,13:41:42,00:19:29,1169,3.3122,4.8708
[mind@util1-pkl MHG_201406]$ awk -F, -v OFS="," '
> $7 ~ /VoIS Community Sharecall/ { $12=($10*0.19)/60 }
> $7 ~ /VoIS Contact Sharecall Overflow/ { $12=($10*0.4)/60 }
> { $13=($13*0.5)/60 }
> { print }' Pax.csv > Pax_1.csv
[mind@util1-pkl MHG_201406]$ cat Pax_1.csv
AccountCode,AccountName,UniqueCallId,FromNumber,ToNumber,DestinationDescription,CallType,CallDate,CallTime,CallDuration,CallDurationSec,Cost,0
563416,MHG - Braamfontein,3260306859:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,20-JUN-2014,17:03:56,00:00:05,5,0,0.00416667
563416,MHG - Braamfontein,3275326523:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,23-JUN-2014,14:19:07,00:00:20,20,0,0.00416667
563416,MHG - Braamfontein,3280178113:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,24-JUN-2014,08:06:23,00:00:06,6,0,0.00416667
563416,MHG - Braamfontein,3305018045:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,26-JUN-2014,10:32:13,00:00:06,6,0,0.00416667
563416,MHG - Braamfontein,3305020549:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,26-JUN-2014,10:32:22,00:00:04,4,0,0.00416667
563416,MHG - Braamfontein,3064064999:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,28-MAY-2014,12:05:07,00:00:06,6,0,0.00416667
563416,MHG - Braamfontein,3071554761:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,29-MAY-2014,09:12:05,00:00:20,20,0,0.00416667
563416,MHG - Braamfontein,3076236709:0A,0860101280,0113812027,Johannesburg area,VoIS Contact Sharecall Overflow,29-MAY-2014,14:07:51,00:00:01,1,0,0.00416667
563416,MHG - Braamfontein,3082567491:0A,0860101380,0214805963,Cape Town area,VoIS Contact Sharecall Overflow,30-MAY-2014,10:00:47,00:00:07,7,0,0.00416667
661178,MHG - Cape Town,3257924103:0A,0860102472,0214804774,Cape Town area,VoIS Contact Sharecall Overflow,20-JUN-2014,14:07:13,00:00:16,16,0,0.00416667
661178,MHG - Cape Town,3059920319:1830,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,28-MAY-2014,10:52:34,00:10:52,652,0,0.0226392
661178,MHG - Cape Town,3059920319:1890,0214804511,0860104111,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,28-MAY-2014,10:57:09,00:10:01,601,0,0.0208683
661178,MHG - Cape Town,3070357179:360,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,29-MAY-2014,08:46:20,00:20:39,1239,0,0.0430208
661178,MHG - Cape Town,3070357179:824,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,29-MAY-2014,09:35:03,00:18:44,1124,0,0.0390275
661178,MHG - Cape Town,3070357179:1103,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,29-MAY-2014,10:03:07,00:00:48,48,0,0.00166667
661178,MHG - Cape Town,3070357179:4351,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,29-MAY-2014,14:17:10,00:07:22,442,0,0.0153475
661178,MHG - Cape Town,3080634155:1403,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,30-MAY-2014,10:28:53,00:04:43,283,0,0.00982667
661178,MHG - Cape Town,3080634155:3172,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,30-MAY-2014,13:01:29,00:06:22,382,0,0.0132642
661178,MHG - Cape Town,3080634155:3622,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,30-MAY-2014,13:38:33,00:03:08,188,0,0.0065275
661178,MHG - Cape Town,3080634155:3648,0214804511,0860765633,Telkom Non-Geographic Universal Services,VoIS Community Sharecall,30-MAY-2014,13:41:42,00:19:29,1169,0,0.04059
[mind@util1-pkl MHG_201406]$

The output file name is Pax1.csv, please note that TelkomAsif hearder Changed to 0 in the output file.

Please note that the rates are not correct. They should either be 0.19 or 0.4 and TelkomAsif rate should be 0.5.
Pax_1.zip (1.7 KB) 
# 6  
Old 06-28-2014
You can't use field 10 for arithmetics without prior conversion to seconds; field 11 seems to have that immediately, so why not use it? Try:
Code:
awk -F, -v OFS="," '
         NR==1                                  { print; next}
         $7 ~ /VoIS Community Sharecall/        { $12=($11*0.19)/60 }
         $7 ~ /VoIS Contact Sharecall Overflow/ { $12=$11/150 }
                                                { $13=$13/120; print }
        ' file

# 7  
Old 06-30-2014
Hallo RudiC,

Awesome brother thank you very much BUT Column 13 is still not calculated correctly because where i recalculate the rate on Column 13 my rate is not 0.5 infact my rate is 0.00 which is incorrect.
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

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: $ cat MHG_Only_Billing_only_great_201408_SummaryDetails.csv... (1 Reply)
Discussion started by: kekanap
1 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