MCM LTER Data File Format Protocols for Database Submission

In order for the database to function properly, many constraints must be put on what type of data can be entered and how those data are entered. Following the guidelines below will ensure all your data are entered correctly and the website will continue to function reliably.

  • Read the submission protocols for your group in
    1. Streams Data 
    2. Met Data
    3. Limno Data
    4. Glaciers Data
  • There is a template for each ‘core data’ table that shows how each file should be formatted (column names and column order are both important). Make sure you keep these column headers. If you did not collect data for a certain parameter in the table that season, simply leave it blank.
  • Excel is generally used to work up and format data. When you’re done, save your data file in comma-delimited format (.csv). Open it back up and make sure everything still looks ok.
  • Do not use commas in any field – use semicolons instead. This is usually a problem in the ‘COMMENTS’ column. We can’t use commas because data are output in commadelimited format on the web – it just makes things easier.
  • Missing Data and out-of-range data; inappropriate values in cells.
    1. If there are no data for a particular sample, do not write “missing” or “no data” or “ND” in the data column - The database will produce an error if text is entered into a field expecting digits. Instead, leave that field blank and if appropriate, write why it is missing in the ‘Comments’ column. Your group may have existing comment codes that apply to this situation.
    2. Also, some instruments are programmed to output a number such as -6999 indicating missing data. This is also unacceptable to the database – there may be rules for checking the data range that won’t allow extremely high or extremely low numbers to be input. Do not include any values that you don’t intend for someone using your dataset to actually use – delete the value and flag it appropriately in the Comments column.
    3. Be careful that your empty data columns don’t include spaces. This is a text character and will not be accepted by the database.
    4. In general, consider the type of data that is supposed to go in that column and the normal range one would expect. Do not enter anything else.
  • Pay special attention to the Date/Time Column
    1. The easiest format to use for the database (and the one we output on the website) is 'MM/DD/YYYY' for date only, or 'MM/DD/YYYY HH:MM’ for date and 24 hour time. However, other formats will also work – contact the data manager to see if your alternate date/time format is acceptable before submitting it.
    2. MM/DD/YYYY' does not correctly transfer out of Excel if you use the default - you must define a custom format:
      • Highlight the date cells and go to Format -> Cells
      • Under the 'Number' tab, go to 'Custom' at the bottom
      • Under the field that says 'Type:' write 'mm/dd/yyyy' (or ‘mm/dd/yyyy hh:mm’)
      • A way you can check to see if it worked is by opening the file in a text editor (e.g. Notepad in Windows) and checking to see if the year is 4 digits. This is important because even though the default Excel date format may display a 4 digit year, it often only saves it as a 2 digit year. When a 2 digit year is loaded into the database, “07” will be interpreted as “0007” instead of “2007.”
    3. Another thing to watch – Dates do not always transfer correctly between PCs and Macs in Excel files, which is another reason to use comma-delimited (.csv) text files. If your dates are off by 4 years and 1 day, that is the issue.
  • All fields are case-sensitive! If there are notes in a template, submission instructions, or constraint table that say a certain field must be in all-caps, make sure they are or the data will not insert.
  • Try to make your data look like the existing data in the database. Each data submission protocol page will have options to download just the column header or the entire dataset. Download the entire dataset and open it in Excel and get acquainted with the format – make your new data format look like the old one.