Exporting to CSV from Modern, when you double-click on the exported file Excel changing data to DATE type

Products

• Clarity PPM SaaS

Issue/Introduction

We are exporting a grid view to a CSV file. This particular grid view includes a lookup attribute (Age) that has values like 8-9, 10-14, 15-19, 20+ etc:

Some of those lookup values will remain as regular text, but some will be converted to dates. For example, 10-14 is converted to October 14 of the current year:

I understand that since months stop at 12, something like 15-19 is left alone, but why is the value being converted at all? Shouldn't it simply be treated as text unless we're exporting an actual date attribute?

 

Environment

Release : 15.9.3

Component : CLARITY STUDIO

Resolution

When exporting to Excel, Clarity knows it’s a string. When you double-click on the exported file and Excel is simply going to interpret it as a date even if it’s exported as a string.

Steps to Reproduce:
1) Use the Clarity Export to CSV feature and save CSV file.
2) Open a new Excel workbook
3) Go to Data > Get Data > From Text > Open Export to CSV file from step 1
The Text Import Wizard opens, Select:
4) Delimited, click next
5) Comma, click next
6) In the 'preview of selected data' select the 'Age' column and change the 'Column data format' to Text
7) Click Finish, Click OK

Results: When you check the sheet data the value is not being converted as a date.

- As another test you can convert your Clarity Export to CSV file to a text file, and you will see Clarity is saving the date as a string.

For example in this file you will see "1-2" which gets read as a date in Excel (unless you use the Text Import Wizard).
"Name","Age Test 1","Investment ID","Manager","Start","Finish"
"4G Upgrade Readiness","1-2","PR1001","Reed, Henry",2021-09-01,2021-12-14

- Excel doesn’t know it’s a string, unless you use the Text Import Wizard. For example the string "1-2" will read as a date in Excel (unless you use the Text Import Wizard).