Sparx Excel API converts dates incorrectly (If the day is 12 or less)

[BruceTOGAF2]

My Jscript imports a CSV file with dates into Sparx EA and stores the dates in a tagged value. The CSV file holds each date in the format “dd/mm/yyyy” e.g. "14/09/2017" = 14th September 2017.

If the day is greater than 12, then the Sparx import stores the date correctly, e.g. "28/09/2017" = 28th September 2017.

If the day is greater than 12, then the API assumes format “dd/mm/yyyy”.

If the day is 12 or less, then the Sparx import stores the date incorrectly. The API assumes the date format is “mm/dd/yyyy”

If the day is 12 or less, then the Excel.Application API converts the first 2 numbers to the month e.g. "11/09/2017" = 9th November 2017 (should be 11th September 2017).

Here is some sample code to demonstrate the process.

// Open CSV file as a worksheet
var xlApp = new ActiveXObject( "Excel.Application");
var xlBook = xlApp.Workbooks.Open (inputFileName);
var xlSheet = xlBook.Worksheets(worksheetNo);
var wsSource = xlSheet   

var my11thDateCell = wsSource.Cells(11, ;  // CSV Sample "11/09/2017"
var my12thDateCell = wsSource.Cells(12, ; // CSV Sample "12/10/2017"
var my13thDateCell = wsSource.Cells(13, ; // CSV Sample "13/09/2017"
var my14thDateCell = wsSource.Cells(14, ; // CSV Sample "14/09/2017"

// A snippet from the tagged value update function which receives parameter last_Modified_Date
if (foundTag.Name == "08-Last_Modified_On") {
foundTag.Value = last_Modified_Date;
   myPTag7Found = true;
   foundTag.Update();

I have tried string conversions and parsing, but the problem still persists. Is there a more fundamental way of configuring the Sparx API to read the CSV dates as format “dd/mm/yyyy” ?

[qwerty]

American style. 12 is a valid month, 13 isn't. Localization is the hell. Especially when working with Mickeysoft products.

q.

[Geert Bellekens]

The question is whether it is EA or Excel who converts the date.

I would suspect Excel rather then EA.

Things I would try:
- re-format dates in the format "yyyy-mm-dd" This could never lead to wrong interpretations.
- read the CSV file directly instead of going through Excel.

example below:

Code: [Select]

'[path=\Projects\Project AC]
'[group=Acerta Scripts]

!INC Local Scripts.EAConstants-VBScript
!INC Wrappers.Include

'
' Script Name: Import Identifiers
' Author: Geert Bellekens
' Purpose: Import the identifiers exported from MEGA's Candidate key members
' Date: 2016-07-14
'

const outPutName = "Import Identifiers"


sub main
dim mappingFile
set mappingFile = New TextFile
'select source logical
dim logicalPackage as EA.Package
msgbox "select the logical package root (S-OAA-...)"
set logicalPackage = selectPackage()
'first select the mapping file
if mappingFile.UserSelect("","CSV Files (*.csv)|*.csv") _
   AND not logicalPackage is nothing then
   'create output tab
Repository.CreateOutputTab outPutName
Repository.ClearOutput outPutName
Repository.EnsureOutputVisible outPutName
'set timestamp
Repository.WriteOutput outPutName, "Starting import identifiers " & now(), 0
'split into lines
dim lines
lines = Split(mappingFile.Contents, vbCrLf)
dim line
for each line in lines
'replace any "." with "::"
line = Replace(line,".","::")
'split into logical and physical part
dim parts
parts = Split(line,";")
'there should be 4 parts in the csv file: Identifier - Attribute or Role Name - AttributePath - RoleName + [ID]
'we should have enough info from the name, and the fact that we know whether it is an attribut or a RoleName
if Ubound(parts) = 3 then
dim IdentifierFQN, idName, isAttribute
IdentifierFQN = parts(0)
'check if the IdentifierFQN is not empty and is a valid FQN
if len(IdentifierFQN) > 0 AND instrRev(IdentifierFQN,"::") > 1 then
idName = parts(1)
if len(parts(2)) > 0 then
isAttribute = true
else
isAttribute = false
end if
dim classFQN
'remove the last part of of the IdentifierFQN in order to get the class name
classFQN = mid(IdentifierFQN , 1 , instrRev(IdentifierFQN,"::") - 1)
if isAttribute then
'set identifier on attribute
setIdentifierAttribute logicalPackage,classFQN,idName
else
'set identifier on association end
setIdenfifierAssociation logicalPackage,classFQN,idName
end if
end if
end if
next
'set timestamp
Repository.WriteOutput outPutName, "End import identifiers " & now(), 0
end if
end sub

function setIdentifierAttribute(logicalPackage,classFQN,idName)
dim attribute as EA.Attribute
set attribute = selectObjectFromQualifiedName(logicalPackage,nothing, classFQN & "::" & idName , "::")
if not attribute is nothing then
'set isID property on attribute
'log progress
Repository.WriteOutput outPutName, "setting {id} on attribute " & classFQN & "." & attribute.Name,0
attribute.IsID = true
attribute.Update
else
'log the fact that we didn't find it
Repository.WriteOutput outPutName, "ERROR: could not find attribute for " & classFQN & "." & idName,0
end if

end function

function setIdenfifierAssociation(logicalPackage,classFQN,idName)
dim classElement as EA.Element
set classElement = selectObjectFromQualifiedName(logicalPackage,nothing, classFQN, "::")
if not classElement is nothing then
'find the associationEnd
dim association as EA.Connector
'register the fact that we found it or not
dim foundIt
foundIt = false
for each association in classElement.Connectors
if association.Type = "Association" or association.Type = "Aggregation" then
dim associationEnd as EA.ConnectorEnd
set associationEnd = nothing 'initialize to be sure
if association.ClientID = classElement.ElementID then
set associationEnd = association.SupplierEnd
else
set associationEnd = association.ClientEnd
end if
if not associationEnd is nothing then

if associationEnd.Role = idName _
AND left(associationEnd.Cardinality,1) = "1" then 'only for obligatory associations
if not foundIt then
'log progress
Repository.WriteOutput outPutName, "setting {id} on association " & classFQN & "." & idName,0
'found the correct one
associationEnd.Constraint = "id"
associationEnd.Update
'register that we found one
foundIt = true
else
Repository.WriteOutput outPutName, "ERROR: found duplicate rolename for " & classFQN & "." & idName,0
end if
end if
end if
end if
next
if not foundIt then
'log the fact that we didn't find it
Repository.WriteOutput outPutName, "ERROR: could not find association role for " & classFQN & "." & idName,0
end if
end if
end function

mainThis script and helper supporting scripts can be found at https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library

Geert

[BruceTOGAF2]

Hi Geert,

I am trying to replicate your code in Jscript.

The following code produces a runtime error: 'TextFile' is undefined.
var mappingFile = new TextFile;

The following code produces a runtime error: ';' expected.
var mappingFile as TextFile;

Any ideas?

[Geert Bellekens]

To be able to completely run this script you need the supporting framework scripts

See this for more info: https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Framework/Wrappers/Include.vbs

Some more info on how to load all the scripts here: https://bellekens.com/2015/12/27/how-to-use-the-enterprise-architect-vbscript-library/

Geert

[qwerty]

IIRC it should be enough to pass the date as DD-MM-YYYY while the slash-format (MM/DD/YYYY) is always interpreted as American.

q.

[Eve]

In the EA API and database schema, all tagged values are a string. The different types determine what editor the gui uses and how they are displayed in the gui.

I just checked the format that EA uses to save the date... And it's pretty much the worst possible case. It depends on the locale settings for the machine where the date is saved. I'm not sure that can be practically changed now.

Standard Windows functions are being used to read the dates, which is why the behavior changes when the string isn't a valid date in the current locale.