Product: ImportOmatic
Description: 'Invalid Fuzzy Date' exception when importing from .xls and .xlsx files
Environment: All
Version: ImportOmatic version 3.0 and higher
Answer:
This issue occurs when using ImportOmatic version 3.0 and higher and importing with a .xls or .xlsx file.
When you enter a date in a cell in Excel (8/28 as an example; dd/mm), Excel adds a timestamp to the value (even though you didn't input a timestamp and it isn't visible in the cell). Excel also stores the full date for this cell (8/28/15). Excel adds the year to the fuzzy date (even though you didn't input the year and it doesn't display in the cell).
When you attempt to import the value into a fuzzy date field in The Raiser's Edge, the timestamp will create an exception for that row. If you use a Dictionary to remove the timestamp, the full date will be imported (in this example, 8/28/15). We do not recommend using a Dictionary to remove the timestamp for fuzzy dates since this will incorrectly add the year to your fuzzy dates.
Solution:
Save the .xls or .xlsx file as a .csv and attempt the import again.
OR
Add a leading apostrophe to the start of the date field in Excel and attempt the import again. This will not add the apostrophe to the date field in Raiser's Edge and there is no need to strip it out with a dictionary.
When you enter a date in a cell in Excel (8/28 as an example; dd/mm), Excel adds a timestamp to the value (even though you didn't input a timestamp and it isn't visible in the cell). Excel also stores the full date for this cell (8/28/15). Excel adds the year to the fuzzy date (even though you didn't input the year and it doesn't display in the cell).
When you attempt to import the value into a fuzzy date field in The Raiser's Edge, the timestamp will create an exception for that row. If you use a Dictionary to remove the timestamp, the full date will be imported (in this example, 8/28/15). We do not recommend using a Dictionary to remove the timestamp for fuzzy dates since this will incorrectly add the year to your fuzzy dates.
Solution:
Save the .xls or .xlsx file as a .csv and attempt the import again.
OR
Add a leading apostrophe to the start of the date field in Excel and attempt the import again. This will not add the apostrophe to the date field in Raiser's Edge and there is no need to strip it out with a dictionary.