NOTE: This article currently only applies when importing data that contains a date field. At the moment, this only applies to importing Schedules and TODOs.
Expected Date Format
When importing data from Excel .CSV (Comma Separated Values) files into ClearEvent, it's important to format dates correctly in your .CSV import file to avoid data import errors. If you don't provide the expected date format, invalid dates may be imported and will later need to be manually fixed in ClearEvent, or data may fail to import completely.
The date/time column formats required by ClearEvent are as follows:
Supported Date Formats
Date Type | Date Format |
Simple Date Only | "yyyy-MM-DD" |
Date and Time | "yyyy-MM-DD hh:mm:ss AM/PM" |
Excel & Date Formatting Challenges
Excel often uses your computer's regional date/time settings to format dates when it saves the .CSV file. This means the date format you see in Excel is often not what gets saved to the .CSV file. Because ClearEvent requires a consistent date format to accurately import your data, we need to make sure all dates are in one of the two date formats specified above.
Fear not! We've outlined a few simple steps below to explain how to do this.
Formatting Date Columns In Excel
Please follow these steps each time a .CSV file is opened and edited in Excel prior to saving your changes to .CSV. This must be done each time you edit a .CSV file because column formatting settings are not saved in .CSV files.
Using your spreadsheet editor (e.g., Microsoft Excel), open the .CSV file.
Edit or paste in the data you wish to import.
Before you save the .CSV file, locate each date column.
Right-click each date column header to select the entire column and choose Format Cells...
From the Format Cells dialog, click the Number tab and choose Custom. Copy the appropriate date format (see above) into the Type input field and click the OK button.
Once all date column formats are specified, save your changes to the .CSV file.
At this point, the .CSV file should contain the correct date format and is ready to import.
Verifying Date Formats In Your CSV File:
Excel can be deceiving when you are trying to inspect date formats. The best way to verify that your dates are in the correct format is to open the .CSV file using a plain text editor like Notepad, TextPad, TextEdit, etc...
Once you've opened the file in your favorite editor, look for the date column and verify the date is in one of the two formats outlined above.
Here are a few example of .CSV import files that contain a valid date formats:
TODO .CSV Import File
Schedule .CSV Import File
Still have questions?
Chat with us.