[SOLVED] Script to multiply 29th field in CSV file by 1,56

knilux · 07-24-2012, 09:55 AM

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

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

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

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

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