Parsing Dates in Excel Import

0
Hi all – When importing dates, we have a variety of formats that are being used, so we are trying to maximize for flexibility. When stored as text, 01/01/2023 comes through with that value as expected. However, when stored as a date, 1/1/2023 comes through as 1/1/23 which is parsed as January 1, 0023. Has anyone experienced something similar / have any tricks to allow for either type of value?   We would just use date, but 01/01/2023 stored as text in excel errors out during import when the attribute is a dateTime. 
asked
2 answers
0

Hi Jon,

 

I would be carefull with providing this kind of flexibility and rather go to a single input format. Because it will be hard to identify all possible use cases. Have you thought about people using excel in dd/MM/yyyy or MM/dd/yyyy format in this sense as well?

However if that is your case you could do the following:

remove all special characters with a replaceAll() function. Then count the length of the string using the length() function. If it is 6 characters use parsing for ddMMyy, if it is 8 characters you can parse using ddMMyyyy,

https://docs.mendix.com/refguide/string-function-calls/#13-replaceall

https://docs.mendix.com/refguide/string-function-calls/#4-length

https://docs.mendix.com/refguide/parse-and-format-date-function-calls/ 

answered
0

Hi Jon, did you find a solution to this by changing the reader or excelvalueparse classes ?

answered