Object Script - Phyton -> Excel Import Error - Date Format in Excel File

To import date-formatted columns from an Excel file using ObjectScript with Python, you can utilize the Python openpyxl library for handling Excel files. When working with date values from Excel, here are key considerations:

Handling Dates

1. When reading cells in Python using openpyxl, the library retains the Python datetime objects for date-formatted cells. You may need to convert these to an appropriate format in ObjectScript.

2. Example of accessing a date cell:

   Set pyCell = pySheet.cell(row, column)
   If pyCell."is_date"() {
       Set dateValue = pyCell."value"
       // Example conversion using ObjectScript: Format the dateValue appropriately
       Set formattedDate = $ZDATE(dateValue, 3)  // Depending on desired display format
   }
   

3. Check the "is_date" property to identify whether the cell contains a date value.

4. Formatting dates in ObjectScript can be done using $ZDATE or $ZDATEH functions, which allow you to control the format and convert dates from internal date formats to display formats. $ZDATE supports multiple formats listed in the documentation, such as YYYY-MM-DD (format 3) or DD/MM/YYYY (format 4) [1][2].

Example Workflow

• Load the workbook and access the active sheet using load_workbook.
• Iterate through rows and columns, check the cell type, and convert date values using the ObjectScript $ZDATE function.

This should address the issue of correctly processing and importing date-formatted cells from Excel files into your ObjectScript environment [3][4][1].

Sources:

Excel store date values as decimal values (or better, as flotaing poin) as dddd.tttt where ddd is the number of days since a base date (usually 1899-12-30) and tttt is: numberOfSecondsSinceMidnight / 86400, hence the value, you get is an date-object, and you have to format it according to your needs

Class DC.PyExcel Extends%RegisteredObject
{

ClassMethod Test(fn = "/home/kav/test/readtest.xlsx")
{
	set exl=##class(%SYS.Python).Import("openpyxl")
	set wbk=exl."load_workbook"(fn)
	set sht=wbk.active
	
	for row=1,2,3 {
		for col=1:1:3 {
			set cel=sht.cell(row,col)
			set typ=cel."data_type"set val=cel.value
			
			write ?col-1*15,$case(typ, "s":val, "n":$fn(val,",",2), "d":val.strftime("%a, %d.%m.%Y"), :"")
		}
		write !
	}
}

}

For i = 1:1:maxRow {
Set cel = pySheet.cell(i,y)
If cel."is_date" {
  Set val = cel.value.date().isoformat() ; ODBC Date  Set MolDatum = $zdh(val,3) ; LOGICAL Date
  Write $zd(MolDatum),! ; DISPLAY Date
}
}