HK

Hi guys,

I'm writing a script that reads data from an Excel file and updates attributes of different objects accordingly.

Everything works fine except for the date field. When I tried to write the date attribute according to the date I've read from the excel it returned "falls".

What should I do with the date before I try to write it into excel?

 

Thank you in advance,

Herman

 

by Martin Miskovic
Posted on Thu, 06/18/2015 - 07:46

Hi Herman,

This script works for my 7.2 version of Aris. You can update the date attributes easily within Aris:

var sYear =     (aRows[i].getCellAt(j).getDateCellValue().getYear()+1900).toString();
var sMonth =    (aRows[i].getCellAt(j).getDateCellValue().getMonth()+1).toString();
var sDay =       aRows[i].getCellAt(j).getDateCellValue().getDate().toString();
var sNewDate =  (sDay.length<2?"0"+sDay:sDay)+"."+(sMonth.length<2?"0"+sMonth:sMonth)+"."+sYear;

In MS Excel you should have the column formatted as standard date column although this setup does not work with Excel 2007, but works fine with 2013. It may be also because of OS I am not sure, I do not have the resources to test my hypothesis.

Try it out and let me know.

Martin

0
by Herman Kantushov Author
Posted on Mon, 06/22/2015 - 12:37

Hi Martin,

 

I can't use your code the same way you wrote it since in each row I first get all the data from the cells into an array.

But I see that you used a different get command. I used the simple getCellValue function.

Maybe I should add a condition and check getDateCellValue if I'm standing on the date cell before I put it in the array?

Or, could I somehow use the value that is already in the array and just reformat it?

 

Thanks,

Herman

 

0
by Martin Miskovic
Posted on Tue, 06/23/2015 - 10:21

I expect that your script is importing the new attributes based on their TypeNum or GUID. That means you can determine the basic type of an attribute.

To retrieve a base type from an attribute, use ArisData.ActiveFilter().AttrBaseType() function.

The base type of a date is Constants.ABT_DATE (located in Constants/Metamodel constants/AttrBaseTypeTblNum in ARIS Script [F1]). While importing data into array, just check if the value is a date or not. And if yes, try to modify my date function.

Hope this will help.

Martin

0
by Herman Kantushov Author
Posted on Wed, 06/24/2015 - 11:13

Thank you

0
by Yuriy Stolyarov
Posted on Thu, 09/19/2019 - 09:01

Hello, Martin!

I have a version of Excel is 2016 (type of cells is Date). And when I try to use your code I get this:

View in Excel:                  19.12.2014                  13.01.2015

Data in cell:                      1.4189364E12            1.4210964E12

After get it by your code: 16.01.2015                  26.01.2015

Can you help me to get date from Excel correctly please?

0
by Yuriy Stolyarov
Posted on Wed, 10/02/2019 - 07:43

In reply to by Felix101

I solved this problem. I changed cell type from "Date" to "Text", then I used this code (it returns string):

function xlsToDate(xlsData){

   var import_custom = JavaImporter(Packages.java.time); // Java Package Import

      with(import_custom){

               sdate = LocalDate.of(1899, Month.DECEMBER, 30).plusDays(xlsData).toString(); 

      }

   return sdate;

}

0

Featured achievement

Rookie
Say hello to the ARIS Community! Personalize your community experience by following forums or tags, liking a post or uploading a profile picture.
Recent Unlocks

Leaderboard

|
icon-arrow-down icon-arrow-cerulean-left icon-arrow-cerulean-right icon-arrow-down icon-arrow-left icon-arrow-right icon-arrow icon-back icon-close icon-comments icon-correct-answer icon-tick icon-download icon-facebook icon-flag icon-google-plus icon-hamburger icon-in icon-info icon-instagram icon-login-true icon-login icon-mail-notification icon-mail icon-mortarboard icon-newsletter icon-notification icon-pinterest icon-plus icon-rss icon-search icon-share icon-shield icon-snapchat icon-star icon-tutorials icon-twitter icon-universities icon-videos icon-views icon-whatsapp icon-xing icon-youtube icon-jobs icon-heart icon-heart2 aris-express bpm-glossary help-intro help-design Process_Mining_Icon help-publishing help-administration help-dashboarding help-archive help-risk icon-knowledge icon-question icon-events icon-message icon-more icon-pencil forum-icon icon-lock