(v23) Address SQL Data Sources Displaying Date and Time Fields Incorrectly in Excel

Address SQL Data Sources Displaying Date and Time Fields Incorrectly in Excel

This applies to: Visual Data Discovery

This behavior is not a bug or an issue with Symphony but actually a result of how Microsoft Excel tries to read the date or 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 or time fields and corresponding values display correctly as a string value within quotation marks. Excel can recognize a string containing a human-readable date or time value, but it will convert this value to its own internal time stamp value represented as a floating point number. Excel then applies an appropriate (or default) date or 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 select the actual date or 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 or time data with the incorrect format instead. Please check the initial CSV file generated by Symphony during export.

To ensure Excel displays the date or time data correctly, use one of the following methods:

1. Prefix the formatted date strings in the CSV file by adding an "=" symbol before the date or 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")
2. Format the column in Excel so that the cells use the correct date or 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 or time format) or general formatting behavior in Excel, please refer to Microsoft Excel's documentation.