saving result set to Excel -- numeric values converted to text in Excel

General
Enthusiast

saving result set to Excel -- numeric values converted to text in Excel

Hi,

When saving a Teradata resultset to Excel (.xlsx) some numeric fields are converted to text.  Using TSQLA 15.1.  Puzzling, since ALL the fields are numeric but only some columns are converted to text once saved to Excel.  Need all fields to stay numeric, otherwise, have to go into Excel and convert.  Has anyone else run into this?  Is there a known solution?  Appreciate any help. 

 

- alan d.


Accepted Solutions
Enthusiast

Re: saving result set to Excel -- numeric values converted to text in Excel

Solution found.  Problem was a result of the size of the decimal fields.  Redefined the fields in the Teradata table to decimal(17,2) and no longer have the problem with Excel converting to text.  They were defined as decimal (20,2).

 

Alan 

1 ACCEPTED SOLUTION
3 REPLIES
Senior Apprentice

Re: saving result set to Excel -- numeric values converted to text in Excel

This is probably due to Tools -> Options -> Data Format -> Display BigInt and Decimal(16)+ values as strings

But if you got numbers with more than 15 digits you will loose precision, because Excel stores numeric data only in Floats.

Enthusiast

Re: saving result set to Excel -- numeric values converted to text in Excel

Thanks for your reply, dnoeth.  

 

I do not have that option checked in TSQLA (Tools -> Options -> Data Format -> Display BigInt and Decimal(16)+ values as strings)

 

The fields that are converted to text in Excel are defined as decimal (20,2) in Teradata.  Fields that are smaller in size such as decimal(8,3) are not getting converted to text.

Enthusiast

Re: saving result set to Excel -- numeric values converted to text in Excel

Solution found.  Problem was a result of the size of the decimal fields.  Redefined the fields in the Teradata table to decimal(17,2) and no longer have the problem with Excel converting to text.  They were defined as decimal (20,2).

 

Alan