Explicit "text" columns being imported at :float anyway

[mbhnyc]

Hey guys-

So, I have a spreadsheet a column of unformatted phone numbers (we have international users, so a one-size-fits-all format is impractical) — most of these are simply numbers, but even when the xlsx (or xls) column type is set to "Text", these still come in as Floats and end up imported with a ".0" at the end.

This is obviously incorrect, but I really thought the column type would be respected.

Am I doing anything obviously wrong here, or is there an issue?

[carlost]

I have run into this issue as well. I tried to find implied cell formats that are part of the OOXML format, but apparently I am too dumb to locate them in the massive pile of specification documents (http://www.ecma-international.org/publications/standards/Ecma-376.htm). So what i am about to say is part conjecture and part google:

• http://lateral8.com/articles/2010/6/11/openxml-sdk-20-formatting-excel-values.aspx: that lists the implied cell formats that are part of the OOXML spec. They match the codes in Format::STANDARD_FORMATS.
• http://support.microsoft.com/kb/264372: the section titled "Custom Number Formats" Explains what the various symbols available wen defining a custom format. '@' Is a text placeholder. So for a format of just '@', the value of the cell should be exactly what you typed ... as text.

It appears that when you set a cell to a format of "Text" the underlying number format has a numFmtId of 49 ... which corresponds to '@'. This represents a simple text placeholder.

If this is correct, Format.to_type() should return :string is the format equals '@'. I am not sure if you want to get into the whole rats nest that is possible when using @ in a more complex text placeholder format, such as 'My name is @'.

If this is correct, I would be more than happy to submit a patch to the tests and the lib code for this.

C

[stevendaniels]

Does this issue still exist in the current version of Roo?

I now it's been a while, but can you create a gist that illustrates this issue?

1. Create a gist with code that creates the error.
2. Clone this repo locally, add a stripped down version of the offending spreadsheet to the repo, and push the changes to the gist.
3. Paste the gist url here.

Here's a sample gist

[OmniBus]

From @carlost 's comment, it is pretty safe to make an assumption interpreting format '@' is string (text) type. It solves many long standing issues .

return :string if format == '@' 

Modify file
roo/lib/roo/excelx.rb

      def to_type(format)
        format = format.to_s.downcase
        if (type = EXCEPTIONAL_FORMATS[format])
          type
        elsif format.include?('#')
          :float
        elsif !format.match(/d+(?![\]])/).nil? || format.include?('y')
          if format.include?('h') || format.include?('s')
            :datetime
          else
            :date
          end
        elsif format.include?('h') || format.include?('s')
          :time
        elsif format.include?('%')
          :percentage
        elsif format == '@'
          :string
        else
          :float
        end
      end

[OmniBus]

@stevendaniels
Make float default surprise everyone. It'd better to leave programmer to decide how to interpret the string if there is no cue to interpret it.

[stevendaniels]

I've submitted a request (#240) that proper type support to Excel. It's a big request, so it will probably take some time before it gets pulled into master.

[stevendaniels]

#240 resolves this issue. Add the following to your gemfile to install it.

gem 'roo', git: 'git://github.com/roo-rb/roo.git' 

I will work on publishing a new version of the gem to Rubygems this weekend.

[dkumar431]

@stevendaniels
Is this issue already fixed? I have some numeric values (50961713356) on my excel, but it's reading them as 50961713356.0. Basically i always wants the values to be read as String.

[letiesperon]

Any updates on this? It's adding a '.0' on all my columns like id or zip code 😬

[pbharadiya]

Facing the same issue. Any work around?

[Manthan2211]

Trying to convert that column to column.to_i.
I hope this patch is working for you.

[pathakh]

you can't convert column.to_i as it will get rid of the leading 0s.