It's all about the answers!

Ask a question

How to correctly round-trip dates in Views - ERM 7.0.2


David Sedar (287) | asked Oct 27 '22, 12:19 p.m.

 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.... 


Comments
Ian Barnard commented Nov 03 '22, 1:55 p.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER

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 commented Nov 03 '22, 6:35 p.m.
@Ian - try exporting an artefact that contains a custom attribute in date or datetime format, and then reimporting it. Historically this has been an issue. DNG export does not use any of the standard Date formats - it does its own thing.

Ideally it should use some form of recognised Date format for export or, even better, you should be able to set the format in properties.

Ian Barnard commented Nov 04 '22, 4:12 a.m. | edited Nov 09 '22, 4:31 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER

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.


To convert a native Excel date to a suitable string 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 an Excel date value (which is a floating point value days.partofday), 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.

2 answers



permanent link
Ian Barnard (1.9k613) | answered Nov 09 '22, 4:31 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER
edited Nov 09 '22, 5:32 a.m.

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
David Sedar commented Nov 09 '22, 6:24 a.m.

 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..


Ian Barnard commented Nov 09 '22, 6:30 a.m. | edited Nov 09 '22, 6:36 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER
Title of your question is "How to correctly round-trip dates"

Yes clearer would definitely help :-)


> 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.


permanent link
David Sedar (287) | answered Nov 02 '22, 6:48 a.m.

 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
Ralph Schoon commented Nov 02 '22, 8:40 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER

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.  



Davyd Norris commented Nov 02 '22, 9:18 p.m.

That would be why I deleted that response David - I reread your post and realised you're going the other way.


Ian Barnard commented Nov 04 '22, 4:23 a.m. | edited Nov 04 '22, 5:17 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER

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.

Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.