Skip to Content
avatar image
Former Member

Issue with characters in excel file ?

i am using cr developer version 14.0.04.738 RTM

I am using an ODBC connection to a .xlsx datasource.

All the imported database fields appears as String[255]

The field in excel is 16 characters 5000097208370110

However when displayed in my report in crystal there is an extra character 5.0000972084e+015

Looks to be a datatype mismatch ? and crystal is trying to use data as a numeric/float ?

Any ideas what's causing this ? workaround ?

Thanks, John

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jun 06, 2017 at 08:23 PM

    Hi John,

    If a number doesn't fit in a column, Excel will shorten it to scientific notation. Usually widening the column will get Excel to show the real value.

    This is an Excel feature. Unless you have a template in Excel with the column wide enough to view all 16 digits, Excel will always do this.

    Brian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 07, 2017 at 07:32 PM

    Thanks Brian, when i open the excel file the data fits in column just fine, and is not converted to scientific notation.

    I checked all records in the column, all looks good.

    I may add that this has only started happening recently with this file, so my temp fix is to ignore if field starts with "5."

    excel-data.jpg

    just-data-equals-5000097208370110.jpg

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 07, 2017 at 08:04 PM

    Brian, ok you are correct. So i did a "length" in excel of the wwn column. All should be 16. But some are at 18 and 19.

    When i press/select the data i see the scientific notation or overflow.

    So the creator of the excel file has fat fingered a few extra zero's Thank you again. John

    overflow.jpg

    Add comment
    10|10000 characters needed characters exceeded