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
I do not have special experience with DOORS Next. My experience with date export and import in Jazz products so far has been as follows: 1. Some dates can not be modified at all. As an example the Modified date for things such as work items.
The format is important, there are certain expectation on how the date is formatted.
For Excel, I do not think that the format of the cell is important.
Having said that, if you have a custom property with a date/time, I would expect that you can import the values you exported and the updated values in a correct string format. If this is not the case I would open a case with support to be able to better share the data.
That would be why I deleted that response David - I reread your post and realised you're going the other way.
> if I try to round-trip this spreadsheet by making some changes, then re-importing it, DOORS rejects the dates as invalid
I can't reproduce this in 6.0.6.1 and 7.0.2SR1 - as long as you're modifying the string consistent with the existing format of an export then it should reimport.
Can you give an example of a modified Date/Datetime value (consistent with the export) that won't import?
If you can reproduce the problem you're having, and the text does appear consistent with the exported format, you should probably create a support case.
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
Thanks Ian,
Perhaps I should have been clearer:
I exported the View containing Date values to .XLSX rather than .CSV and modified other attributes, not the date fields.
I always remove the METADATA rows - perhaps that is the real issue..
Title of your question is "How to correctly round-trip dates"
> to .XLSX rather than .CSV
XLS/XLSX doesn't change what I said in my answer - the dates are still strings. Unmodified XLS/XLSX reimports without error, and as long as you respect the date/datetime format it will reimport after modifications.
> modified other attributes, not the date fields
So the problem is nothing to do with date/datetime values, and your title is completely misleading. You can edit your question, or even remove it.
> I always remove the METADATA rows
Err, well, don't. At a simplistic/precise/pedantic level if you've exported, deleted the metadata then imported you're not reimporting an unmodified file, are you.
Comments
Ian Barnard
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER Nov 03 '22, 1:55 p.m.So you're saying you can't reimport an unmodified CSV (i.e. as exported) without errors? I can't reproduce this on 7.0.2SR1. What iFix are you using?
Davyd Norris
Nov 03 '22, 6:35 p.m.Ian Barnard
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER Nov 09 '22, 4:31 a.m.Well yes it's exported as a string and not a standard date format that I know of, but it does roundtrip, at least with 6.0.6.1 and 7.0.2SR1 that I tried. And I can modify the CSV/XLS/XLSX consistent with the exported Date or Datetime format and then reimport.