Integer field after extraction from MySQL DB shows E+ exponential value, HOW-to fix?

[#1]

Hi folks,

Please have a look at the image below:

After trying to to extract a table from MySQL db, one of the data fields got corrupted. Its an ID field, tried several methods to fix it but failed to solve the issue --> Num(), floor(), int() functions where used. Please advise. Thanks in advance.

Best Regards,

R

[anbu1984]

Data fields are not corrupted. Large numbers are shown in exponential form. To convert to decimal numbers, first cast it to Decimal long enough to hold your integer and then cast it to character

SELECT CAST(CAST(b AS DECIMAL(30)) AS CHAR) FROM abcd;

[anil2185]

Hi,

You can use round() function.

Syntax :- Select round(field_name) as ID from table;

Regards,

Anil

[#4]

Hi Anbu,

Apologize for the misinterpretation, I don't want to convert it to decimal number - I want to extract them as it is as a long integer. Any clue how to do so?

Best Regards,

R

[#5]

Hi Anil,

Doesn't seem to work, any other idea on how to go bout doing it?

Regards,

R

[#6]

I think the column didn't get corrupted. Just because your number is too big, it just show like that. It's still integer. Please note, whether the text align right (int) or left (text).

If you insist to display it like integer, you can go to properties->number->integer->'###########################'

[jagan]

Hi Ram,

How many digits are there in this number field.  Try like this

Data:

LOAD

*,

Num(Value)  AS New Value;

SELECT

*

FROM Table;

If it not works then divide the number by Millions / Thousands.

Regards,

Jagan.

[#8]

Hi Jagan,

21388881293400499 - sample value from the field. If am not mistaken 17-digits integer. Am going to try out the script which you have posted, see if it works out.

Thanks a bunch.

Best Regards,

R

[#9]

Hi Wenkun,

Can't.

[jagan]

Hi,

For 14 digits the number is displaying as such, but when it exceeds the it is showing e.

=Num(21388881293400499/1000, '###.#0')

Try this.

Regards,

Jagan.