Skip to Content
0

Issue with characters in excel file ?

Jun 05, 2017 at 06:58 PM

54

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Brian Dong Jun 06, 2017 at 08:23 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jun 07, 2017 at 07:32 PM
0

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


Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jun 07, 2017 at 08:04 PM
0

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


overflow.jpg (22.0 kB)
Share
10 |10000 characters needed characters left characters exceeded