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
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
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;
}