Unwanted rounding of large number
 

I run a query against DB2 and receive the complete number (a number feild).
But when i cut and paste into Excel the values change. Why?
Excel is rounding the ID number:
6506508818150199801 to 6506508818150200000
6506508804141199801 to 6506508804141200000

my work around was a modified statement in the SQL:
'`' || cast (ACCT.CUST_ACCT_ID as char(20)) CUST_ACCT_CHAR_ID which results
in `6506508818150199801. This pastes into Excel without rounding, but the "`"
is still there.

Don't change your query, but before pasting format the cells in Excel as text

"Candyman" wrote:

I run a query against DB2 and receive the complete number (a number feild).
But when i cut and paste into Excel the values change. Why?
Excel is rounding the ID number:
6506508818150199801 to 6506508818150200000
6506508804141199801 to 6506508804141200000

my work around was a modified statement in the SQL:
'`' || cast (ACCT.CUST_ACCT_ID as char(20)) CUST_ACCT_CHAR_ID which results
in `6506508818150199801. This pastes into Excel without rounding, but the "`"
is still there.

When I do that the number comes out as an exponential. It is 19 digits long.
To get rid of the exponential I have to convert to a number... Then it shows
as rounded again! Hmmm

"Duke Carey" wrote:

Don't change your query, but before pasting format the cells in Excel as text

"Candyman" wrote:

I run a query against DB2 and receive the complete number (a number feild).
But when i cut and paste into Excel the values change. Why?
Excel is rounding the ID number:
6506508818150199801 to 6506508818150200000
6506508804141199801 to 6506508804141200000

my work around was a modified statement in the SQL:
'`' || cast (ACCT.CUST_ACCT_ID as char(20)) CUST_ACCT_CHAR_ID which results
in `6506508818150199801. This pastes into Excel without rounding, but the "`"
is still there.

If I run this query in SQL Server's Query Analyzer

select 6506508818150199801 as Answer

and copy the result into Excel *AFTER* first formatting the target cell as
Text, Excel accepts it as text & does not change it.

How are you pasting it?


"Candyman" wrote:

When I do that the number comes out as an exponential. It is 19 digits long.
To get rid of the exponential I have to convert to a number... Then it shows
as rounded again! Hmmm

"Duke Carey" wrote:

Don't change your query, but before pasting format the cells in Excel as text

"Candyman" wrote:

I run a query against DB2 and receive the complete number (a number feild).
But when i cut and paste into Excel the values change. Why?
Excel is rounding the ID number:
6506508818150199801 to 6506508818150200000
6506508804141199801 to 6506508804141200000

my work around was a modified statement in the SQL:
'`' || cast (ACCT.CUST_ACCT_ID as char(20)) CUST_ACCT_CHAR_ID which results
in `6506508818150199801. This pastes into Excel without rounding, but the "`"
is still there.

Cut and Paste. However, I actually have this working in many different xls
applications that the sql resides on one worksheet then generates the SQL
string and connection, is passed to the server, and the results are brought
back to the data page. I can bring back numbers, character strings, whatever
and it never rounds the numbers like this. It may drop some leading zeros,
which is where the hyphen comes in handy if the zeros are needed. But I have
never had this massive rounding before as shown inthe first example. We just
changed to XP and i did not know if this could have any affect of the
situation. I have tested this with the column formated both ways. The data
feild is originally a number. So even to get the hyphen to work I have to
convert to a CHAR data type, then bring it over. I have never seen this
before.

"Duke Carey" wrote:

If I run this query in SQL Server's Query Analyzer

select 6506508818150199801 as Answer

and copy the result into Excel *AFTER* first formatting the target cell as
Text, Excel accepts it as text & does not change it.

How are you pasting it?


"Candyman" wrote:

When I do that the number comes out as an exponential. It is 19 digits long.
To get rid of the exponential I have to convert to a number... Then it shows
as rounded again! Hmmm

"Duke Carey" wrote:

Don't change your query, but before pasting format the cells in Excel as text

"Candyman" wrote:

I run a query against DB2 and receive the complete number (a number feild).
But when i cut and paste into Excel the values change. Why?
Excel is rounding the ID number:
6506508818150199801 to 6506508818150200000
6506508804141199801 to 6506508804141200000

my work around was a modified statement in the SQL:
'`' || cast (ACCT.CUST_ACCT_ID as char(20)) CUST_ACCT_CHAR_ID which results
in `6506508818150199801. This pastes into Excel without rounding, but the "`"
is still there.

I humbly bow to your suggestion. I tried this again today and it worked.
(No shock to you I am sure.) Not sure what happened yesterday. Anyway thanks
for the tip. I will remember that. ;)

"Duke Carey" wrote:

If I run this query in SQL Server's Query Analyzer

select 6506508818150199801 as Answer

and copy the result into Excel *AFTER* first formatting the target cell as
Text, Excel accepts it as text & does not change it.

How are you pasting it?


"Candyman" wrote:

When I do that the number comes out as an exponential. It is 19 digits long.
To get rid of the exponential I have to convert to a number... Then it shows
as rounded again! Hmmm

"Duke Carey" wrote:

Don't change your query, but before pasting format the cells in Excel as text

"Candyman" wrote:

I run a query against DB2 and receive the complete number (a number feild).
But when i cut and paste into Excel the values change. Why?
Excel is rounding the ID number:
6506508818150199801 to 6506508818150200000
6506508804141199801 to 6506508804141200000

my work around was a modified statement in the SQL:
'`' || cast (ACCT.CUST_ACCT_ID as char(20)) CUST_ACCT_CHAR_ID which results
in `6506508818150199801. This pastes into Excel without rounding, but the "`"
is still there.