Marine Data Literacy 2.0

Providing instruction for managing, converting, analyzing and displaying oceanographic station data, marine meteorological data, GIS-compatible marine and coastal data or model simulations, and mapped remote sensing imagery





Home > 4. Ocean Data View > 4.16 ISO Date/Time

4.16 Importing ODP Spreadsheet with ISO Date & Time into ODV

  • Exercise Title:  Importing Ocean Data Portal (ODP) Spreadsheet with International Organization for Standardization (ISO) Date & Time into an Ocean Data View (ODV) Collection

  • Abstract:  This exercise addresses primarily the methods to import a spreadsheet containing ISO dates and times into an ODV collection.  The ODP is a work in progress, so the current data formats and the catalog access system are expected to evolve.  Writing this exercise is part of that process, because feedback to the system administrators will be offered to assist them in their work.  Essentially this exercise simply drops itself onto the very robust table import functions of Ocean Data View.

  • Preliminary Reading (in OceanTeacher, unless otherwise indicated):

  • Required Software:

  • Other Resources: 

  • Author:  Murray Brown

  • Version:  6-1-2014

PART A:  Importing spreadsheet with completely ISO-compliant date and time (yyyy-mm-ddThh:mm:ss.sss)
1.  Open the main Ocean Data Portal (ODP) page, and familiarize yourself with the project scope and goals.

Click on DATA ACCESS along the top row of commands.  Then in the next window select ODP NETWORK > SEARCH & DOWNLOAD

2.  This separate page opens, showing a browse-type menu of the data resources already in the ODP.
3.  Click on ALL PAGES in the lower right corner, so you can see all 89 items on one page, as you see here.

NOTE:  The list of items has only grown from 65 to 89 in a little over 2 years, so the ODP is not adding categories at a great rate.  The number of individual data objects within these categories is, however, considerable.

4.  Near the middle of the page, find and select the SECCHI DISK DATA RU_NODC_02.
5.  Then find and click DATA ACCESS near the top left corner of the page.
6.  In a short time, these options will appear to the right of the selection.  If the data are too big to display, then only the DOWNLOAD control appears.

NOTE:  Right now, three different types of resource are provided, as indicated by the icons on the far right:  STRUCTURED DATA FILE, DATABASE, and OBJECT FILE.

7.  Right-click on the DISPLAY DATA control, and select OPEN LINK IN NEW TAB (or equivalent in your browser).
8.  This spreadsheet opens.  The variables are:
  • Data unit: index - Appear to be unique station identifiers
  • Data unit: iIdentifier - Lots of repetition, so they might be program, vessel, or system groupings
  • Date and time - Good ISO formats
  • Latitude point
  • Longitude point
  • Water transparency - Secchi disk depth in m
  • Quality check sign - Apparently always zero

Only 50 lines of data are available here, never the whole file, so COPY/PASTE from here is not useful.

9.  Find the download options at the bottom left of the spreadsheet.  Click on CSV (for "comma separated variables").
10.  Navigate to DATA > OCEAN > ODP and save the file as RU_RNODC_02_1-1330940285043_csv.txt
11.  Always check ASCII files with a good editor to see what they contain.  Here are the first few lines of the TXT file.
12.  Note that the DATE AND TIME records are absolutely compliant with the ISO standard, including the "+4:00" offset to indicate that the times are 4 hours ahead of Z time at the Greenwich Meridian.
13.  Run Ocean Data View (ODV)
14.  Select FILE > NEW (to create a new collection) and navigate to the folder PRODUCTS > ODV> COLLECTIONS and enter the name RU_RNODC_02_1-1330940285043_csv.  Then click SAVE.
15.  To define the collection structure, select USE TXT, ODV, VAR, OR OTHER FILE IS TEMPLATE. 

NOTE:  The term "Customized Collection Creation" does not appear in the ODV documentation, but it's a good guess that the following steps are customization, so we check it here.

Then click OK.

16.  This window appears, very similar in function and appearance to the Excel window that helps the user to make incoming spreadsheets intelligible.

The COLUMN LABELS are not separate here. so the COLUMN SEPARATOR must not be correct.  Try all the choices to see what happens.

17.  When the comma is selected, then the COLUMN LABELS appear separately, so this is the correct setting.

The other settings are self-explanatory.

Click OK.

18.  This windows appear, where you set up the structure of the new collection.
  • The META VARIABLES are the fields that ODV "wants" to have.  They are not necessarily available in this dataset, but they are desireable.
  • The initial set of COLLECTION VARIABLES are the variables that the import process had discovered in the spreadsheet.


19.  Of these, DATA UNIT INDEX, DATA UNIT IDENTIFIER and DATA AND TIME all appear to be more appropriate for the metadata section.
20.  Select these 3 variables in the lower section, and use the << control to remove them.

It is also possible to create NEW variables here, with the control on the right side.   For example, according to the ODV guide if you have true time-series data, a collection of sequential data from only one location, then create the variable time_ISO8601 for use as the organizing variable (instead of depth, the usual quantity).

Now click OK.

21.  Due to the very different nature of this dataset and collection, these selections are probably sufficient.

Click OK.

22.  After a few seconds, the collection is created, and this empty map appears.
23.  Select IMPORT > ODV SPREADSHEET, then navigate to the TXT spreadsheet and open it.
24.  This is a repeat of the spreadsheet recognition step above.  Make the same selection, and click OK.
25.  Now we must make the specific match-ups between the incoming data (in the spreadsheet) and the fields in the collection structure.


26.  Select DATA UNIT INDEX and STATION, and click ASSOCIATE.  Asterisks (*) will appear by them to indicate the match-up.
27.  Now, select the DATA AND TIME on the source side, and YEAR on the meta variables side.  Then click CONVERT.
28.  From the conversions that are offered for DATES and DAYS, select the first one.

NOTE:  In the string that defines the format (yyyy-mm-dd hh:mm:ss.sss) there is no "T" in the middle (as required by the ISO standard).  This indicates that ODV doesn't require it.

Then click OK.

29.  Notice how ODV has placed asterisks by all of the date and time elements on the right side.

Now click OK.

30.  The two relevant variables are already marked, so just click OK.
31.  This message, and the appearance of red dots on the map indicate that the import has been successful.

Click OK.

32.  This is the map of the stations, zoomed by you or automatically.
33.  Now we need to see how to use the data and time information in analyses.  This requires Station, Scatter or Surface mode graphics.
34.  Select VIEW > LAYOUT TEMPLATES > 1 SCATTER WINDOW to see this graphic.  The only variables we have are used here is default axes.
35.  The ISO date and time information are in the metadata, but they do not yet constitute a numeric variable that can be used as an axis or surface variable in any analysis.  We must first create a derived variable from these fields.
36.  Select VIEW > DERIVED VARIABLES to see this list.  There are many choices on the right which could be calculated, but nothing yet on the left.
37.  Close the open choices, and open the TIME choices.  Select TIME (STATION DATA & TIME) and click ADD.

Do not click OK.

38.  This window is asking you to define an "epoch" that defines the beginning of the system time for your ODV collection.

"System time is measured by ... the number of ticks that have transpired since some arbitrary starting date, called the epoch. For example, Unix ... systems encode system time ... as the number of seconds elapsed since ... 1 January 1970 00:00:00, with exceptions for leap seconds. Systems that implement the 32-bit and 64-bit versions of the Windows API ... provide the system time as both SYSTEMTIME, represented as a year/month/day/hour/minute/second/milliseconds value, and FILETIME, represented as a count of the number of 100-nanosecond ticks since 1 January 1601 00:00:00 ..."  [From Wikipedia article ]  NetCDF time often observes an epoch of 1900-01-01T00:00:00.  And the "BPE" (before present era) terminology refers to 1950-01-01.  Etc. Etc.

Make whatever choices you want, and click OK.

39.  This new variable appears on the left.  Click OK to close this window.
40.  Now, right-click on the graphic and select X-VARIABLE.
41.  Select the TIME variable, and click OK.
42.  And here you can see the ISO data and time successfully used in ODV analyses.
43.  Select VIEW > SAVE VIEW AS and save this analysis as 1scatter_secchi_time so you can always get back to this figure in this collection.  If you want to close the collection, select FILE > NEW.
PART B:  Importing spreadsheet with nearly ISO-compliant date and time (yyyy-mm-dd hh:mm:ss.sss)
44.  Now, just to make sure that dates and times that do not include the "T" in the middle, as specified by ISO 8601, we'll create a collection with a different spreadsheet that we already know lacks this feature.
45.  Find the dataset SEVASTOPOL BAY SECCHI UA_IBSS_06.  Use the same steps as above to access the spreadsheet.
46.  Save the file in the folder DATA > OCEAN > ODP with the filename UA_IBSS_06_1-1330939175118_csv.txt
47.  Examine the TXT file to see what it contains.  It is very similar to the above file, with some differences
48.  The variables are:
  • Data unit: index - Not unique; only 2 values (1 or 2)
  • Date and time - ISO, but lacks the "T" in the middle
  • Latitude point
  • Longitude point
  • Water transparency
49.  Use the same steps as above to create the collection and import the data.  The DATA UNIT: INDEX can be set to STATION, but this association isn't certain.
50.  This figure shows the collection, visualized in the same way as the previous collection.
51.  Select VIEW > SAVE VIEW AS and save this analysis as 1scatter_secchi_time so you can always get back to this figure in this collection.  If you want to close the collection, select FILE > NEW.  [It is in a different collection from the previous one, so the same view name can be used.]
52.  Inspection of other data choices in the ODP catalog has indicated that there are some very different contents in other items.  These will be treated in one or more separate exercises.