Question
· Dec 6

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

#HealthShare

Hello, 

I am using Object Script with Phyton to import an Excel File. 

Please see the below code. Its works fine for all text and numeric Columns. But by importing a DATE formated column in Excel (birthdate) , Iam facing the below issue as result:

22@%SYS.Python

 

 

My question: How can i import date formated Excel Data in Object Script?

 

Thanks a lot in advance

 

Maikel

       Set tSC = $$$OK
        Set tData = ##class(%SYS.Python).Import("openpyxl")         Try 
        {
            /// Pfad zur Excel-Datei
            Set filePath = pInput.Filename
            $$$TRACE(filePath)
           
            Set pyWorkbook = tData."load_workbook"(pInput.Filename)
            Set pySheet = pyWorkbook.active
            Set maxRow = pySheet."max_row"
            ........

            Set MolDatum = pySheet.cell(i,y) m

Product version: IRIS 2025.3
Discussion (7)2
Log in or sign up to continue
DC AI Bot · Dec 6

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:

0 0
Julius Kavay · Dec 7

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

}

   

0 0
Maikel Fradziak · Dec 7

Hi, thanks a lot for your answer. Unfortunately importing date format is still not working for me. If I am using: strftime, Object says: Functions does not exits.

General question: What could be the reason, that I am getting the below result: "16@%SYS.Python" by directly importing "date formated column from excel? In my opinion, I should get a numeric value. Days since xxx. I f change the format in excel from date to text, I will get the a value, If i change it back to "date" I will get the result: 

16@%SYS.Python

0 0
Julius Kavay · Dec 7

Could you please run the following commands in a terminal session:

write $zv
set exl=##class(%SYS.Python).Import("openpyxl")
set wbk=exl."load_workbook"( "your path_and_filename.xlsx" )
set sht=wbk.active
set cel=sht.cell(row,col)	// ROW and COL should point to a date-cell
write cel."data_type"
write cel.value.strftime("%d.%m.%Y")

Please post a screenshot here, even if you get some kind of error

0 0