Error fix for "Read Range: Not a legal OleAut date." thrown in Excel-based automation.
Error Synopsis: The following error is sometimes thrown while leveraging the Read Range activity in the Excel based automation:
Root Cause and Resolution:
- Scenario #1: Issue with the cell formatting
Solution: Only valid dates should have the formatting of type Date. All the other columns in a file should have the associated data type, for instance Text, Number, General etc.
- Scenario #2: Issue with the Excel file size
Solution: The aforementioned error has also been seen while attempting to read very large sized files. As a general practice, handling memory and working with such large files straight in Excel is not really efficient as it requires parsing of extremely large objects that open the door to a lot of types of failures.
To resolve this, try reading smaller ranges at once. Use "Read Range" activity in batches to read smaller sets of rows (as a suggestion, 10000 rows) and process the entire excel in batches. The number of rows can be further experimented to find an optimal number of rows to read and handle the same in the entire automation, since complex processing will also consume memory.