Ruby spreadsheet formatting string

Question

In my xls file i have such row:

8630025500  8630025500      MOBIS/KIA/HYUNDAI   ACCENT (ТаГаз)  ЭМБЛЕМА "HYUNDAI"   2   2       1   106

In my code i fetch second row (8630025500) so:

row[1].present? ? oem = row[1] : oem = nil

But in db i see 863002550.0, but why? Why it is formatting my data? How to set data as-in-file-so-in-db? Also i tried:

row[1].present? ? oem = row[1].to_s : oem = nil

In xls this row format is digit (also if i try to set it to common, i get the same result)...

here is example of file, if you need: xls file

So how to import in db data, such as xls excel output do?

In db my field is varchar!

Is your DB column type a float or decimal, instead of an integer type? That would explain it. — user684934
– user684934, Commented Jan 22, 2013 at 9:22
@bdares it is string, becouse there i could have also string! — byCoder
– byCoder, Commented Jan 22, 2013 at 13:27

BenMorel · Accepted Answer · 2013-12-09 00:25:36Z

3

I played a bit with SpreadSheet library and your file and checked the class of row[1]:

irb> sheet.each {|row| print row[1], ' ', row[1].class; puts }
866202F501 String
866201F000 String
866201G600 String
866201G800 String
8662026800.0 Float
866202H010 String
...

Gotcha! It chages! Some of values has type of Float, so when you call to_s on them (or ActiveRecord, if you don't), it adds .0. That is simply the behaviour of to_s method of Float: it always adds fractional part, even if it is zero. Database is not related here.

Probably there might be more elegant solution (somehow configure Spreadsheet lib, modify xls) but the fastest hack is: sees Float -> converts to int before converting to string.

Compare:

irb> sheet.each {|row| puts row[1].is_a?(Numeric) ? row[1].to_i.to_s : row[1]  }
866202F501
866201F000
866201G600
866201G800
8662026800
866202H010

~~So you can just add this check to your check of .present?, probably wrapping this into a function in order not to look so ugly.~~ Here I use Numeric instead of Float: it is base class for all numbers so in case you somehow get decimal or something else, it is also caught.

UPD: Here comes the code sample: you can replace your line with the following:

oem = row[1].is_a?(Numeric) ? row[1].to_i.to_s : row[1] if row[1].present?

edited Dec 9, 2013 at 0:25

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Jan 22, 2013 at 14:34

NIA

2,55123 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

byCoder Over a year ago

hm... to complicated for me, how to add this to my code? something like: row[1].present?.is_a?(Numeric) ? oem = row[1].to_i.to_s : oem = nil or what? Becouse if to_i and then to_s for example for LOL132, i will get 123, not?

NIA Over a year ago

Well, I mean, you have to nest your conditions.If you stick to ?: operator it will look like this:

byCoder Over a year ago

like what? ) didnt't understand

NIA Over a year ago

Sorry my internet went down :( Well, I mean, ?: operator inside another ?: operator looks like mess, so you can make use of tail condition: oem = row[1].is_a?(Numeric) ? row[1].to_i.to_s : row[1] if row[1].present?. If row[1] not present, oem will be equal to nil because if body will not be executed. In case of String like "LOL32" .is_a?(Number) will return false so .to_i.to_s will not be executed, row[1] will. Here is the detailed version: gist.github.com/4596482 HTH :)

NIA Over a year ago

* also added snippet to the post body.

Babasaheb Gosavi · Accepted Answer · 2013-01-22 09:14:00Z

1

you can save your data as varchar or string in database so that it will not change your data. when you will fetch it you can apply conversion over there

answered Jan 22, 2013 at 9:14

Babasaheb Gosavi

8,0631 gold badge16 silver badges14 bronze badges

1 Comment

byCoder Over a year ago

it is string, becouse there i could have also string! In db is string type! that's the magic

Collectives™ on Stack Overflow

Ruby spreadsheet formatting string

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related