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
# 8  
Old 06-30-2014
So what be the algorithm for Col 13?
# 9  
Old 07-01-2014
everything on column 13 (TelkomASif) the following should apply
TelkomAsif=(column13*0.5)/60
# 10  
Old 07-02-2014
This is the result of my proposal applied on field 13:
Code:
'{print $13, $13/120}' FS="," file1
TelkomAsIf 0
0.5 0.00416667
0.5 0.00416667
0.5 0.00416667
0.5 0.00416667
0.5 0.00416667
0.5 0.00416667
0.5 0.00416667
0.5 0.00416667
0.5 0.00416667
0.5 0.00416667
2.7167 0.0226392
2.5042 0.0208683
5.1625 0.0430208
4.6833 0.0390275
0.2 0.00166667
1.8417 0.0153475
1.1792 0.00982667
1.5917 0.0132642
0.7833 0.0065275
4.8708 0.04059

I'd guess it is correct...
# 11  
Old 07-03-2014
Thank you RudiC,

But how to i put your syntax into the existing syntax below:

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 }
' Pax1.csv > Pax11.csv


Regards,

Pax
# 12  
Old 07-03-2014
It's already in there. print by default prints $0, which includes $13. And, $13 is calculated for every single line, regardless of the conditions / patterns above.
# 13  
Old 07-04-2014
Hallo RudiC,

Column 13 is still being incorrectly calculated eg.
look at file Pax1.csv before it gets changed

[mind@util1-pkl MHG_201407]$ grep "3093361537:0A" Pax1.csv
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

AFTER CHANGE

[mind@util1-pkl MHG_201407]$ grep "3093361537:0A" Pax11.csv
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,0.0981667,0.000818333
[mind@util1-pkl MHG_201407]$


Note that after change column 13 value should be 0.19 and this is how i calculated it below:

TelkomAsif Cost =( duration*0.5)/60 and in this case the amount for TelkomAsif should be 0.2583

Regards,

Pax
# 14  
Old 07-04-2014
Kidding? You tell us repeatedly the algorithm for $13 (=TelkomAsif) should be TelkomAsif=(column13*0.5)/60 and keep telling me my calculations are wrong. Now you come up with TelkomAsif Cost =( duration*0.5)/60 (which, btw, is a non-existant field). Should you mean "Callduration" (field 10), I can tell you this will NOT satisfy your requirements.
I leave it up to you to adapt (NOT correct!) the script to your needs.
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