Parsing Data from Excel File Uploaded

0
I'm making a module where it reads data from an Excel File I upload on its page. The Excel file is a raw output of another app, so nothing has changed, including the format of each column. The data in the Excel file has a Transaction Date column that is formatted "General". I used Data Importer to make the table based on this Excel file, and the attributes generated are just the same as all the columns of the file. The plan is, this Excel file is going to be uploaded raw through the module page, but the transaction date column is saved in an attribute with Date and Time type. Here's the microflow I made when the "Save" button is clicked: How do I convert the Transaction Date from "General" into String through this microflow?
asked
2 answers
1

UPDATE!

I've solved this problem. Hope this will solve yours too.

 

The plan is, nothing on the Excel file is going to be modified by the users of the app before uploading. Here's what I finally did :

  1. Using Data Importer, I uploaded the Excel file to make the entity with attributes from that file.
  2. The entity generated was persistable by default.
  3. I made a copy of that entity, but made it non-persistable. This entity is related to a different System.FileDocument entity.image.png
  4. The trick is the use of Iterator.image.png
  5. On the Create Disbursement activity, set the Transaction_Date value as  
    parseDateTime($IteratorDisbursement_XLS/Transaction_Date, 'dd/MM/yyyy')
  6. Don't forget to set the value of the other attributes. For example :  
    $IteratorDisbursement_XLS/Reference_No
  7. The value of the relation between the persistent entity & the FileDocument entity must be set too.image.png
answered
0

  • Excel saves dates as a number (serial number) when the cell is General.Example: 17/10/2025 becomes 45301.

  • Import this number into Mendix as Integer/Decimal (e.g., TransactionDateRaw).

  • Create a DateTime attribute (e.g., TransactionDate) and optional String attribute.

  • In a microflow, convert Excel serial → DateTime using:

    addDays(parseDateTime('1899-12-30','yyyy-MM-dd'), floor($Disbursement/TransactionDateRaw))

  • (Optional) Convert to string:

    formatDateTime($Disbursement/TransactionDate, 'dd/MM/yyyy')

  • Commit the object.

answered