Problem

You download your Databricks SQL query results as a .csv file. When you open the file in Excel, you notice numbers containing more than 15 digits are rounded or displayed as scientific notation, and any digits beyond the 15th are replaced with zeros. The Databricks UI displays the correct output.

Cause

This behavior is specific to Microsoft Excel. Excel automatically converts numeric values with greater than 15 digits when opening the file. (To compare, if you open the exported file in a text editor, it contains the correct values.) 

For more information, review the Microsoft Last digits are changed to zeros when you type long numbers in cells of Excel article.

Solution

1. Cast numeric columns with more than 15 digits to STRING in the query.

%sql
SELECT
 CAST(<your-column-name> AS STRING) AS <your-column-name>,
FROM <your-catalog>.<your-schema>.<your-table>;

2. Download results in Excel (.xlsx) format from the Databricks query editor instead of .csv. Click the chevron next to the download button and select Download Excel > All rows.