Search code examples
teradatateradata-sql-assistant

Pasting SQL decimal columns into Excel

I have a issue with data formats of Excel and SQL.

I have a column in SQL which is of datatype DECIMAL(18,0) and when I am trying to paste the result in SQL..the last 3 digits of the sql result gets replaced by 0 in Excel.

Example:

In SQL the result set has a column called session id and has decimal numbers like

119,597,417,242,309,670        
329,621,151,415,350,454       
134,460,940,261,658,890

but when I paste it in Excel the numbers look like:

enter image description here

I tried changing the format in EXCEL to paste as text however, the whole format of the result set gets distorted (and only the first column gets pasted properly without the 0's)

I can't keep casting all columns in SQL from decimal to int as there are way too many columns.

Can you please guide me as to what I can do?

Solution

  • Numeric fields in Excel are limited to 15 digits precision.

    In SQL Assistant under Tools / Options / Data Format you can ask to have large Decimal (and BIGINT) fields displayed as text for just this sort of copy / paste. Or you can tell SQL Assistant to Save As or Export to Excel format.

    For other tools you can explicitly FORMAT and CAST the data to VARCHAR in your SELECT so it is retrieved as text.