0

I've been tasked with exporting record metadata from a documentum repo using DQMan.

However, I frequently encounter issues with date formats. They output in DQMan in "dd/mm/yyyy hh:ss" format, but when 'Exporting to Excel' the dates seem to output in "mm/dd/yyyy hh:ss", while my Excel is setup for "dd/mm/yyyy", and so the dates are either interpreted incorrectly or as text.

Edit: I have tried to establish the precise series of events, and I reckon the issue must be how DQMan is exporting into Excel. It must be telling Excel to expect a certain type of format but sending it something else.

SYSTEM OPERATION
DATABASE Stores 'DateValue' for Record Date Attributes; displays as local user date format.
DQMAN Queries repo with DQL criteria and reports 'DateValue'; Reports in the default format for the windows user (UK Format for us!).
DQMAN Runs 'Export to Excel' Function.
DQMAN Sends delimited text data to Excel (unsure how this part works or is formatted) .
EXCEL Excel is being told that this is a US DateValue, but raw text sent to Excel in UK format as it appears in DQMan, as per windows settings. (?)
EXCEL Testing Value to determine datatype… "Is this string a DateValue?"
EXCEL DateValue of 2015-12-21: Tests "21/12/2015 05:00:00"… NOT A US DATE
EXCEL DateValue of 2014-08-08, Tests "08/08/2014 05:00:00"… IS A US DATE
EXCEL DateValue of 2014-01-06, Tests "06/01/2014 05:00:00"… IS A US DATE
EXCEL If the value is evaluated as NOT A US DATE , the value is added to Excel as plain text.
EXCEL If the value is evaluated as IS A US DATE , the US format text string is added to Excel as a UK DateValue; this gives the wrong value.
HotSauceCoconuts
  • 301
  • 5
  • 19
  • dqMan is a java program and you can try launching it from a .bat file with a preceding `set "_JAVA_OPTIONS=-Duser.region=...` or even jvm debug options so you can figure out how it converts the dates. – basin Mar 12 '22 at 08:50

1 Answers1

0

I'm not sure if dqMan uses dfc.properties but if it is, you can use dfc.date_format parameter to adjust date to something that suits you more (ref)

However, that won't help you with exported date format. Maybe exporting to CSV as middle step?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Miki
  • 2,493
  • 2
  • 27
  • 39
  • using Export to CSV does solve the problem. However, the repo is full of old records containing special characters that corrupt the reports. Random speech marks and linefeeds for example. PowerQuery for Excel has a function to remove quoted Carriage Returns but I haven't figured out a good way to remove speech marks, and the tabular data output into the CSV is delimited with speech marks followed by a semi-colon... It seems like this option might be doomed. – HotSauceCoconuts Apr 28 '21 at 10:48