Import from Excel using Artsman's xcomp -- numeric precision checking is the responsibility of the programmer
Nick Renders
omnis1 at arcict.com
Fri Nov 3 09:34:29 UTC 2023
Hi Graham, Doug,
Did you find a solution to this issue?
I seem to be encountering the exact same problem: decimal numbers are rounded to an integer.
The difference is that my Excel file uses a comma as a decimal separator, while Omnis uses a point.
I have tried playing around with the $root.$prefs.$separators() settings, but the result is always the same.
I am using ExcelFormat v3.94 in Omnis Studio 10.22 on macOS 10.14.
Best regards,
Nick Renders
On 23 May 2018, at 20:00, Graham Stevens wrote:
> Hi Doug,
>
> Thanks for your feedback.
> To further define, the spreadsheet contains financial data, ie. numbers with 2 decimal precision. I have used Book.$extractlist() with the different kXLExtract... parameters with and without the optional second parameter of a predefined list and the values always come back rounded (not truncated) to an integer value, eg. 120.23 -> 120, 15.65 -> 16, -10.70 -> -11. This is happening with a list column predefined as floating point number, 2dp number, kCharacter, or not passing a predefined list and letting the xcomp decide.
>
> Rgds,
> Graham
>
>
>> On 23 May 2018, at 17:32, Doug Easterbrook <doug at artsman.com> wrote:
>>
>> hi Graham.
>>
>> when you say rounded … please define.
>>
>> if you are importing into a two decimal place field (eg #1D2 and the source is
>>
>> 0.125 — does it become 0.12 -and-
>> 0.124 — does it remain 0.12
>>
>> if so, then one might argue that the import is doing the right thing since there is more precision in the source data than the imported field and there really are four choices:
>>
>> 1) truncate
>> 2) round
>> 3) crap the entire import saying there are too many digits for the field
>> 4) you (the programmer) import to an extra decimal place of precision and do what you want (ie. round, truncate, throw an error, average previous entries — what have you)
>>
>>
>> can you please verify that importing data where the precision matches the omnis variable — that it is the same both in the spreadsheet and in the list
>>
>>
>>
>>
>> here’s something else to consider.
>>
>>
>> I suspect your issue is sytemic in omnis. you can try this in a method and see what I mean
>>
>> define myVar as number 2dp
>>
>> run this line of code
>> Calculate myVar as 0.128
>>
>> myvar will be 0.13
>>
>>
>> All omnis numeric calculations ROUND on you, they don’t truncate and if we are using omnis functions to do the work within the external … you get what omnis has always done.
>>
>>
>>
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> mailto:doug at artsman.com
>> http://www.artsman.com
>> Phone (403) 650-1978
>>
>>> On May 23, 2018, at 7:16 AM, Graham Stevens <graham.stevens at gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> I am using the Artsman xcomp to import Excel files (both xls and xlsx) into Omnis and have hit a problem with the method Workbook.$extractlist(). All the numeric values in the spreadsheets are coming in to the Omnis list as rounded values. I believe I have tried all the various permutations, predefining the list, etc. but to no avail. As a workaround, I can read the $value from each cell but it is a time consuming process with a large spreadsheet.
>>>
>>> Has anyone else encountered this issue and found a workaround other than reading each cell individually?
>>>
>>> TIA,
>>> Regards,
>>> Graham
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
More information about the omnisdev-en
mailing list