I’ve been encountering a peculiar problem while trying to process an Excel file using the Excel reader node in Knime. One of the columns in the Excel file represents dates, but when I read the file into Knime, the column’s type is recognized as String. To convert these strings into actual date types, I’ve been trying to use the “String to Date&Time” node. However, I run into the following error message:
“Execute failed: Failed to parse date in row ‘Row542’: Text ‘44949’ could not be parsed at index 0”
Upon inspecting the data, I realized that a few cells in the Excel file have dates represented as numbers (for example, ‘44949’). As I understand, Excel represents dates as sequential serial numbers so that they can be used in calculations. In this format, ‘1’ corresponds to January 1, 1900, and each subsequent number represents the next day. So, ‘44949’ would represent some specific date.
Given the large number of Excel files I have, manually correcting the Excel files isn’t a feasible solution. Does anyone have suggestions on how to handle and convert these numeric representations of dates directly in Knime?
Any help or guidance would be much appreciated. Thank you in advance!