Results 1 to 4 of 4

Thread: Read in Excel file where numbers have custom formatting

  1. #1
    Lavastorm Employee
    Join Date
    Aug 2011
    Location
    London, UK
    Posts
    32

    Default Read in Excel file where numbers have custom formatting

    Hi all,

    I've come across an unexpected outcome (at least to me) when I'm reading in some data from an Excel file.

    Let's say I have the value -10000.23 and I have it in a column in excel but formatted 3 different ways.
    Formats:
    1) _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)
    2) #,##0.00;[Red]-#,##0.00
    3) 0.00

    Visually, in Excel, the values are presented:
    1) (10,000)
    2) -10,000.23 (in red)
    3) -10000.23
    where the underlying value was entered as -10000.23 for all records.

    When I read this into LAE using the Excel node, visually they are:
    1) * (10000)
    2) (10000.23)
    3) -10000.23
    Writing directly to a new Excel file, the records are written as the BRD viewer presented it above.

    I know I can use BRAINScript to convert these number values to double using some cleansing but it's not really ideal nor easy to explain to a non-technical end-user.

    I tried this in 5.0 and 6.0 with the same result.

    I'm not sure whether this is what is expected to happen (for example, we can't vouch for all variations of custom formatting in Excel) or a bug or something else.
    Any advice/thoughts?


    Example XLS & BRG attached.

    Thanks
    Michelle
    Attached Files Attached Files

  2. #2
    Lavastorm Employee
    Join Date
    Apr 2014
    Location
    Boston, MA
    Posts
    279

    Default

    That is the expected behavior. From what I've learned from Tim M., the data import (done with Apache POI) maintains the formatting instead of getting the "underlying value." This also manifests itself if a user has doubles with more than two decimal place precision, but because the formatting is set to two decimal places, only two decimal places will be read. It would be cool if format preservation were a toggle option, which is something that could be done with POI.

    As a side note, if you save the file as in CSV format within Excel, you get the same (minus the *) thing.

  3. #3
    Lavastorm Employee
    Join Date
    Aug 2009
    Location
    Cologne
    Posts
    513

    Default

    Hi,

    As mentioned by ryeh, this is not currently handled in the Excel File node at the moment, as it retrieves that formatted value rather than the raw value.
    This is necessary for some cell types (e.g. I think dates are actually stored as doubles, so we would want to retrieve a date value in this case), but often, this isn't what is desired for other field types.
    The issue has been raised to allow for this sort of behavior (while still returning dates in their date-format rather than just a numeric value).

    I can't comment as to when this will be done, but it is planned to be introduced in a future LAL release to allow for this behavior.

    Regards,
    Tim.

  4. #4
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    245

    Default

    Hi,

    The Excel File node was enhanced in LAL 6.1.45.0 / LAL 6.0.45.0 to allow the node to optionally import the underlying cell values of numeric fields instead of the formatted value - see the 'IgnoreCellFormatting' property on the Optional tab. By default, the formatted value is imported so you need to set the property to 'true' to get the new behavior.

    Regards,
    Adrian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •