LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   Script to multiply 29th field in CSV file by 1,56 (http://www.linuxquestions.org/questions/programming-9/script-to-multiply-29th-field-in-csv-file-by-1-56-a-4175418417/)

knilux 07-24-2012 09:55 AM

Script to multiply 29th field in CSV file by 1,56
 
Hi,

I want to change prices in a very long .csv file. This price is in the 29th field and uses a dot as a decimalseparator (i.e. 1.23 instead of 1,23). The fieldseparator is a comma and at the end of a line there is always a CTRLF.

Is there a simple way to do this? I am not much of a programmer...

thanks

Wim Sturkenboom 07-24-2012 10:08 AM

Is there a guarantee that there are no comma's in any of the fields?

And to what do you want to change the prices? Simple maths? Or lookup from another file? Or ...


Edit:
Just realized that you want to multiply by 1.56
Have you considered a little macro in open office / libre office?

schneidz 07-24-2012 10:28 AM

seems like you would want to start by merging the 29th and 30th fields and include a '.':
Code:

awk -F , '{print $29 "." $30}' prices.csv
what the hell is 1,56 ?

edit: the second part would be easy:
Code:

[schneidz@hyper ~]$ cat knilux.csv
hello,1.23
world,4.56
[schneidz@hyper ~]$ awk -F , '{print $2 * 1.56}'  knilux.csv
1.9188
7.1136


knilux 07-24-2012 10:59 AM

There are no comma's inside other fields. Suppose in a field there is a price of 12.34 that has to be recalculated. I want to multiply it by 1,56 so the outcome should be 19.25. This is the new number that has to be written back into that field.

The calculation is always the same, no input from another file needed

grail 07-24-2012 11:58 AM

Assuming you mean 1.56, ie a decimal number, something like the following might work for you:
Code:

awk -F, '$29 = sprintf("%.2f", $29 * 1.56)' file

Wim Sturkenboom 07-24-2012 01:19 PM

knilux is dutch, just like me :)

In Holland, the dot is the thousand separator and the comma is the decimal separator

so 1.000.000 is one million and $1,33 is one dollar and 33 cents

PS
I think that grail has a correct solution.

schneidz 07-24-2012 01:25 PM

why is dutch, holland and the netherlands the same thing ?

Wim Sturkenboom 07-24-2012 01:49 PM

The Netherlands is the country (translated to Dutch: Nederland)
The Dutch are the citizens (translated to Dutch: Nederlanders)
Dutch is the language (translated to Dutch: Nederlands)

Holland, as I used it, is actually (in the Dutch language) wrong as far as I remember; it's old and as far as I know is only used for the provinces Noord-Holland and Zuid-Holland. But I might be mistaken. Plenty English speaking people around me use 'Holland' for 'the Netherlands', so in English it might be correct.

whizje 07-24-2012 02:16 PM

Quote:

Originally Posted by Wim Sturkenboom (Post 4736962)
Holland, as I used it, is actually (in the Dutch language) wrong as far as I remember; it's old and as far as I know is only used for the provinces Noord-Holland and Zuid-Holland. But I might be mistaken. Plenty English speaking people around me use 'Holland' for 'the Netherlands', so in English it might be correct.

You are right it should be the Netherlands, but nowadays even in the Netherlands it is often seen as synonym.

schneidz 07-24-2012 02:56 PM

also, england, great britian and the united kingdom.

knilux 07-24-2012 03:02 PM

It definitely looks good ;) But as I said before, I am not much of a programmer. I am not sure how it works, but I will give it a try tomorrow.

Quote:

Originally Posted by grail (Post 4736902)
Assuming you mean 1.56, ie a decimal number, something like the following might work for you:
Code:

awk -F, '$29 = sprintf("%.2f", $29 * 1.56)' file

And by the way, Wim is right about the comma in the 1,56. It is common here to use it as a decimal separator, a bit confusing in this case. Thanks Wim.

Thanks for all the help so far. I will let you know the outcome tomorrow.

grail 07-24-2012 08:52 PM

Sorry for the confusion (learn something new each day and all that :) ). I would be guessing, but will assume that your locale will be happy for you to replace the
dot with a comma. After now re-reading the original question and using our new information on locale:
Code:

awk -F, '$29 = sprintf("%.2f", gensub(/[.]/,",","1",$29) * 1,56)' file

knilux 07-25-2012 04:46 AM

1 Attachment(s)
I tried both of the suggestions of Grail. Something goes wrong with the comma's and dots I think. Sometimes it changes the 29th column, sometimes the 28th and the calculation is not working. I made a sample file for testing purposes and attached it here. Maybe this helps. Thanks.

schneidz 07-25-2012 06:48 AM

i dont speak dutch but for example i see in line 6, i think there a comma's (,) within fields so awk is getting confused and using them as field-separators.

Wim Sturkenboom 07-25-2012 07:27 AM

Code:

"Audio / Video aansluitkabel voor de PS2, XBOX of GameCube naar een TV, LCD of Plasma scherm.<br /><br /><br /><br />€ Nikkel plated"
The comma between PS2 and XBOX and between TV and LCDin line 6 confuses awk (as indicated by schneidz); it's why I asked if there was a guarantee that there were no commas in the fields :)

Now back to the awk gurus :D

my attempt based on awk -F, '$29 = sprintf("%.2f", gensub(/[.]/,",","1",$29) * 1,56)' file

Code:

awk -F "\"*,\"*" '$29 = sprintf("%.2f", gensub(/[.]/,",","1",$29) * 1,56)' file
Not tested, but it's supposed to split when it finds commas between double quotes

Source: http://www.joeldare.com/wiki/using_awk_on_csv_files


All times are GMT -5. The time now is 02:34 PM.