1

I'm trying to replace the dots with commas in the entire Price-column.

enter image description here

This doesn't work.

#replace dots with commas in price column
@price_column = sheet.column(15)
@price_column.each do |c|
    c.to_s.gsub(".",",").to_f
end

What am I doing wrong?

sawa
  • 165,429
  • 45
  • 277
  • 381
Edito
  • 3,030
  • 13
  • 35
  • 67
  • You're converting it back to a float with `to_f`. Also, `gsub` is a non-destructive method, so it will return the changes, but not actually change the column itself. I'm not too sure in your case, but you may need to set the column to the return of your `gsub` – philip yoo Apr 18 '16 at 07:01
  • I don't quite understand what you mean, I tried this `c = c.to_s.gsub(".",",")` but it's not working either. – Edito Apr 18 '16 at 07:05
  • What does `c` equal? And is the type an integer? Also, what is your current return? – philip yoo Apr 18 '16 at 07:07
  • Check the code snippet, c is the cell in the column, it returns `20.0` and `5123.0` before and after the gsub action https://i.imgur.com/yTOW3Ob.png – Edito Apr 18 '16 at 07:12
  • Ok, instead try using `map`. `@price_column.map { |c| c.to_s.gsub(".", ",") }` .. – philip yoo Apr 18 '16 at 07:15
  • doesn't change anything either – Edito Apr 18 '16 at 07:18
  • I'm going to need more details. I'm testing based on the fact you have an array of `[20.0, 5123.0]`, and `map` works in converting the period to a comma, but also converts to a string type. If this isn't working, your data type is different – philip yoo Apr 18 '16 at 07:21

3 Answers3

5

The issue is that you are using to_f on a string having invalid format for a float. The ruby to_f only take the digits before , and returns the float. You should not be worried about converting the string into float.

The correct code will be:

@price_column = sheet.column(15)
@price_column.each do |c|
   c.to_s.gsub!(/\./,",")
end
Zain Zafar
  • 1,607
  • 4
  • 14
  • 23
  • This worked, it was the exclamation mark that did the trick, thanks! – Edito Apr 18 '16 at 07:23
  • I don't get why it works with the `!` http://apidock.com/ruby/v1_9_3_392/String/gsub%21 could you explain? thanks – Edito Apr 18 '16 at 07:25
  • 1
    Here is a nice thread on it http://stackoverflow.com/questions/612189/why-are-exclamation-marks-used-in-ruby-methods – Zain Zafar Apr 18 '16 at 07:27
1

Depending on your excel settings you have:

The comma can be the sepator for decimal or it can be de dot in my settings the comma is the separator, the dot is not a number so it is a string:

price dot               price comma     
2.4                       2,4       
2.5                       2,5       

sum price dot   #VALUE!         sum price comma 4,9

son in my case when I read it with ruby:

require 'spreadsheet'

book = Spreadsheet.open './Workbook1.xls'

sheet1 = book.worksheet 0

column_A = sheet1.column(0)

puts "column A"

column_A.each do |cell|
  puts cell.class # this a string
  puts cell.gsub(".",",")
end

puts "Colum E"

column_E = sheet1.column(4)

column_E.each do |cell|
  puts cell.class #this is a float
  cells.to_s.gsub(".",",")
end

the result is:

irb(main):007:0> column A
String
String
String
NilClass
NilClass
Colum E
String
Float
Float
NilClass
NilClass
=> true

In this step you have a string, then if you convert to float .t_f, the excel will show your default format for float. In ruby is always the dot

Community
  • 1
  • 1
anquegi
  • 11,125
  • 4
  • 51
  • 67
0

You are actually converting a float into string and making changes on the string and again trying to convert to float type. below is the example

  1. initial_value => 2.1
  2. converted this to string => "2.1"
  3. now changing . to , => "2,1"
  4. and then you change it to float value => 2.0

so the value after the point value is lost and you get 2.0 again. try avoiding .to_f to solve your case.

HEraju
  • 77
  • 3