How to correctly round-trip dates in Views - ERM 7.0.2
When I export a View in ERM (DNG) v7.0.2 to Excel the date fields look like:
June 2, 2022
Attempting to import the spreadsheet, every date is rejected as being invalid,
what is worse, I cannot even find an Excel conversion function that recognises these 'dates' in text format.
I cannot be the only person to suffer this....
2 answers
Davyd Norris commented:
Excel is the culprit here - ELM uses several date formats that are typically defined by various standards, such as ANSIC, XSD, RFC822, and RFC3339. The default date format in Excel doesn't format into a standard, so you have to create a custom date format in Excel that maps to any one of these. For example: yyyy-mm-ddThh:mmZ will give you something that will import correctly.
But that misses the point:
my 'problem' starts with DOORS View with a date attribute, which gets exported to Excel as a Text Field.
Then if I try to round-trip this spreadsheet by making some changes, then re-importing it, DOORS rejects the dates as invalid.
Comments
In spreadsheet exports, Date and Datetime values are exported as strings like:
- "November 1, 2022"
- "October 2, 2022 at 2:30:00 AM PDT"
For example if I create attributes aDate and aDateTime, and add those to artifact type System Requirement, I can put values into these in a view, like this:
Then when I export this view of the artifact (which is in module 2400) to CSV I get:
id,Primary Text,aDate,aDateTime,isHeading,parentBinding,module,Artifact Type
2472,The control computer shall be capable of operating in a normal office environment.,"November 1, 2022","October 2, 2022 at 2:30:00 AM PDT",false,2646,2400,System Requirement
This will reimport unmodified, in 6.0.6.1 and 7.0.2SR1.
NOTE there's some metadata (which I haven't shown) in the export in a few rows below the data rows - don't modify/remove/overwrite this because when reimporting this tells DN where the data came from, to assist the import process.
The content looks the same if you export to XLS/XLSX, i.e. the Date and DateTime values are strings.
If I need to modify the dates I have to be sympathetic to the format used for Date and DateTime. So if I modify these to perhaps
id,Primary Text,aDate,aDateTime,isHeading,parentBinding,module,Artifact Type
2472,The control computer shall be capable of operating in a normal office environment.,"April 1, 2023","November 5, 2022 at 11:33:29 AM PDT",false,2646,2400,System Requirement
When I import this (without any error) the artifact attributes are updated:
To convert a normal Excel date (which is just a presentation of a floating point value) to a suitable string for import use a formula like this for a Datetime =TEXT(realdates!D2,"MMMM D, YYYY at H:MM AM/PM") or this for Date =TEXT(realdates!D2,"MMMM D, YYYY") where realdates!D2 is a cell with a floating point number DAYS.PARTOFDAY - you may/may not have formatted this to be shown as a date/datetime), most likely on a different worksheet. I haven't experimented with timezone added to the string, I guess you'd have to add that with the formula.
Comments
Comments
Ian Barnard
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER Nov 03 '22, 1:55 p.m.Davyd Norris
Nov 03 '22, 6:35 p.m.Ian Barnard
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER Nov 09 '22, 4:31 a.m.