Address SQL datasources apparently displaying date and time fields incorrectly when exported into Excel
This behavior is not a bug or an issue with Zoomdata but actually a result of how Microsoft Excel tries to read the datetime data stored in the CSV file generated. CSV files don't have a specific format, they just contain data. When Excel tries to examine this data, it will try to set an appropriate format for it. If the user opens the CSV file using a text editor (e.g. Sublime Text), they should notice that the datetime fields and corresponding values display correctly as a string value within quotation marks. Excel is able to recognize a string containing a human-readable datetime value, but it will convert this value to its own internal timestamp value represented as a floating point number. Excel will then apply an appropriate (or default) datetime style to these cells, but the format displayed may not necessarily use or match the string representation shown in the CSV file.
When the user initially opens the CSV file in Excel and clicks on the actual datetime field, they 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:
To make sure Excel displays the datetime field/columns correctly, use one of the following two methods:
- Prefix the formatted date strings in the CSV file by adding an "=" symbol before the datetime 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 datetime format by right-clicking the column (or cells) and selecting the Format Cells option. Please refer to following screenshots:
Was this topic helpful?