Date Format - update Aris via Excel file

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

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

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

Thank you

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?

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;

}