Address SQL Data Sources Displaying Date and Time Fields Incorrectly in Excel
This behavior is not a bug or an issue with Zoomdata but actually a result of how Microsoft Excel tries to read the date/time data stored in the generated CSV file. CSV files don't have a specific format, they just contain data. When Excel tries to examine this data, it tries to set an appropriate format for it. If you open the CSV file using a text editor (for example, Sublime Text), you will notice that the date/time fields and corresponding values display correctly as a string value within quotation marks. Excel can recognize a string containing a human-readable date/time value, but it will convert this value to its own internal timestamp value represented as a floating point number. Excel then applies an appropriate (or default) date/time style to these cells, but the format displayed may not necessarily use or match the string representation shown in the CSV file.
When you initially open the CSV file in Excel and click on the actual date/time field, you might notice that the correct value and actual value of the cell is displayed in the "function" section even if the value shown in the specific cell is different.
Opening the CSV file with Excel and saving it might cause the file to overwrite the date/time data with the incorrect format instead. Please check the initial CSV file generated by Zoomdata during export.
To ensure Excel displays the date/time data correctly, use one of the following methods:
- Prefix the formatted date strings in the CSV file by adding an "=" symbol before the date/time values in quotes for each individual row (e.g. "2013-09-17 04:00:00.0","Alabama" becomes ="2013-09-17 04:00:00.0","Alabama")
- Format the column in Excel so that the cells use the correct date/time format by right-clicking the column (or cells) and selecting the Format Cells option.
For more information about making formatting changes (trying to set a different default date/time format) or general formatting behavior in Excel, please refer to Microsoft Excel's documentation.
Was this topic helpful?