LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 07-24-2012, 09:55 AM   #1
knilux
Member
 
Registered: Mar 2012
Location: The Netherlands
Distribution: OpenSuSE
Posts: 42

Rep: Reputation: Disabled
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
 
Old 07-24-2012, 10:08 AM   #2
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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?

Last edited by Wim Sturkenboom; 07-24-2012 at 10:13 AM.
 
Old 07-24-2012, 10:28 AM   #3
schneidz
Senior Member
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-19-live-usb/ aix
Posts: 3,813

Rep: Reputation: 586Reputation: 586Reputation: 586Reputation: 586Reputation: 586Reputation: 586
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

Last edited by schneidz; 07-24-2012 at 10:34 AM.
 
Old 07-24-2012, 10:59 AM   #4
knilux
Member
 
Registered: Mar 2012
Location: The Netherlands
Distribution: OpenSuSE
Posts: 42

Original Poster
Rep: Reputation: Disabled
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
 
Old 07-24-2012, 11:58 AM   #5
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,411

Rep: Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873
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

Last edited by grail; 07-24-2012 at 11:59 AM.
 
1 members found this post helpful.
Old 07-24-2012, 01:19 PM   #6
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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.

Last edited by Wim Sturkenboom; 07-24-2012 at 01:21 PM.
 
Old 07-24-2012, 01:25 PM   #7
schneidz
Senior Member
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-19-live-usb/ aix
Posts: 3,813

Rep: Reputation: 586Reputation: 586Reputation: 586Reputation: 586Reputation: 586Reputation: 586
why is dutch, holland and the netherlands the same thing ?
 
Old 07-24-2012, 01:49 PM   #8
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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.
 
Old 07-24-2012, 02:16 PM   #9
whizje
Member
 
Registered: Sep 2008
Location: The Netherlands
Distribution: Slackware64 current
Posts: 582

Rep: Reputation: 129Reputation: 129
Quote:
Originally Posted by Wim Sturkenboom View Post
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.
 
Old 07-24-2012, 02:56 PM   #10
schneidz
Senior Member
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-19-live-usb/ aix
Posts: 3,813

Rep: Reputation: 586Reputation: 586Reputation: 586Reputation: 586Reputation: 586Reputation: 586
also, england, great britian and the united kingdom.
 
Old 07-24-2012, 03:02 PM   #11
knilux
Member
 
Registered: Mar 2012
Location: The Netherlands
Distribution: OpenSuSE
Posts: 42

Original Poster
Rep: Reputation: Disabled
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 View Post
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.
 
Old 07-24-2012, 08:52 PM   #12
grail
Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 7,411

Rep: Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873Reputation: 1873
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
 
Old 07-25-2012, 04:46 AM   #13
knilux
Member
 
Registered: Mar 2012
Location: The Netherlands
Distribution: OpenSuSE
Posts: 42

Original Poster
Rep: Reputation: Disabled
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.
Attached Files
File Type: txt Samplefile.csv.txt (16.1 KB, 13 views)

Last edited by knilux; 07-25-2012 at 04:48 AM.
 
Old 07-25-2012, 06:48 AM   #14
schneidz
Senior Member
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-19-live-usb/ aix
Posts: 3,813

Rep: Reputation: 586Reputation: 586Reputation: 586Reputation: 586Reputation: 586Reputation: 586
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.
 
Old 07-25-2012, 07:27 AM   #15
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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
 
  


Reply

Tags
csv, script


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace a field in quotes within a csv file hattori.hanzo Programming 3 05-29-2012 01:10 AM
Bash or PHP: Split csv file based on field value? guest Programming 4 02-06-2009 12:57 AM
AWK: change a particular field in a csv file help help help!!!! haydar68 Programming 20 08-03-2008 01:10 AM
Split CSV, field as filename richmur Programming 2 10-24-2006 08:39 AM


All times are GMT -5. The time now is 04:53 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration