LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mlam

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
Attachments:
excel_numberformatexample.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

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.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

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.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awilliams1024

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