Setting the Date and Time Format Used in the CSV File
You can specify the format of values containing both date and time parts in your CSV file. Integration Manager supports different formats for date and time information in your CSV file, and converts OpenAir date and time values into the format you specify when exporting OpenAir information to a CSV file, or converts date and time values from the format you specify to the format used by the OpenAir database, when you import information a CSV file into OpenAir.
The date and time information in your import CSV file must be the same as the format specified in the Integration Manager Formatting options. Otherwise, the Integration Manager log shows the error "Error: Non valid string specified as DATE".
To set the date and time format used in the CSV file:
-
In Integration Manager, locate the row corresponding to the record type and the direction required from the table listing the record types available for import
from and export
to a CSV file.
-
Do one of the following:
-
Click to select the row, then go to Options > Formatting.
-
Right-click the row, then click Formatting from the context menu.
The Formatting window appears. The date and time formatting options are slightly different for export and import.
-
-
Select the date format from the dropdown options and specify the separator between date components (year, month, and day).
Note:The Current date/time format box shows a preview of the date and time format as you change the settings.
-
Select the time format from the dropdown options and specify the separator between time components (hours, minutes, and seconds).
-
If the date and time values in the CSV file show, or should show the time before the date, check the Time is set first box.
-
If the CSV file includes, or should include date information only without information about time, check the Export date only box (Export) or Date/Time contains date only box (Import).
-
(Export only) If the CSV file should include date information and time information in separate columns, check the Split date and time into separate columns. The two columns will have headers including the CSV column name as listed on the Field Mapping screen followed by
(date)
and(time)
. For example if the OpenAir fieldcreated
is mapped to a CSV columndateCreated
, the two separate columns will havedateCreated(date)
anddateCreated(time)
for headers. -
(Export only) To exclude column headers from the exported CSV file, check the Do not export the header box. All column headers will be excluded, not only the date and time column headers.
-
Click OK to save the date and time formatting settings and return to the main Integration Manager window.