Formatting Dates for CSV Imports

Some of the CSV imports contain date fields, in particular the capture/schedules import. The required format for date fields is yyyy-mm-dd. This is an international standard date format, designed to reduce confusion.

Users (like you) typically work with CSV files using a spreadsheet program like Microsoft® Excel. The PROBLEM is that Excel may automatically reformat what it recognizes as a date into the default date format configured for your system. Which may not be yyyy-mm-dd.

What this means is that even if your CSV file is correct (open it in Notepad or Wordpad to see the raw data), the reformatted date field will look wrong in Excel. And after you make changes and save the CSV file, the date format showing in Excel will be applied to the fields.

This is bad, because now your CSV import won't work.

DON'T PANIC! Use the instructions below to change the date format in your spreadsheet.

Don't be a slave to date formats! Enter your schedule/capture data into the spreadsheet using whatever date format YOU like. Then use the below procedure to reformat the dates columns into the format required for the CSV import. Be sure to save the file both as a CSV and an Excel file (.xls or .xlst) to retain the formatting change.

To reformat the date fields in an Excel spreadsheet

  1. Open the .csv file in Excel.
  2. RIGHT-CLICK the tile above the Start Date column header. (Press control+click if using a Mac)
    This selects the entire column and opens a shortcut menu as shown below.
    Section of CSV file with date column selected and shortcut menu showing as described
  3. Select Format Cells from the shortcut menu.
  4. If necessary, select the Number tab (it should appear by default).
  5. Select Custom from the number Category list.
  6. In the Type field, replace the text that appears with yyyy-mm-dd.
    format cells dialog box with custom option selected and new date format entered in Type field as described
  7. Click OK.
  8. Repeat these steps for the End Date column.
  9. When finished, Save the file as an Excel file (.xls or .xlst), then Save As a .csv file.

The saved CSV file will have the proper date formatting for importing to Echo360; the Excel file will retain the format changes and can be used again later to generate a new schedule import file.  

If you want to be CERTAIN the CSV file is correct, open the file in Notepad or Wordpad and look for the date; if you changed the format in Excel to yyyy-mm-dd and saved as CSV, the date will appear in this format in the raw csv file. Don't use Excel to check it; Excel will convert the dates back to the system default format.