home
image

Preparation

Specifications for a data file.
Data file format
patient ID timestamp glucose
patient ID timestamp glucose
... ... ...

example

Here are two example data files named test1 and test2 which will help guide your data preparation; they may be used to try out the application. Save the files to your desktop as test1.txt and test2.txt; then open them with an application like Notepad.

For your own glucose data, first, make the data anonymous by changing all medical record numbers. Please ensure that any patient-data submission is in compliance with institutional policies.

The program generating metrics expects a tab delimited text file where each line, or record, consists of three numbers separated by tabs. You may use a program like Microsoft Excel to format the data as in the directions below. However, when you save this file, use 'Save As' and under 'Format:' choose 'Text (tab delimited)' as your option.

The first number is an anonymized medical record number. It must be an integer with no dashes, dots, letters, spaces, or other symbols. The Microsoft Excel format would be 'number' with zero decimal places.

Timestamp format
ddddd.ttttt
where d is the number of days since 1/1/1900
and t is time expressed as a decimal fraction of the day, that is, 12 noon is 0.5
So 11/23/05 7:01 AM becomes 38679.29304

The second number is a timestamp: date and time as a single decimal number formatted in the way Microsoft Excel stores dates. More on this below.

The third number is the glucose result in mg/dl and must be an integer value (round all decimal values before upload). Errors in this column are the most common reason for failed uploads to this site. For example, glucometers may insert values such as "<20" or ">600", "ID Error", or "HI" in this field. Please open your data file in a spreadsheet and glance through this column. One can also do a search/find for the "<" or ">" characters, though this will not catch other text in this column. When you find such errors, correct them: e.g. change "<20" to 20 and ">600" to 600. You may wish to delete rows containing other text such as "ID Error." Check the third column of the file carefully; glucose values stored as numbers and spaces will cause errors. For example: "29  " instead of "29"

Anonymizing MRNs

An example formula for anonymizing MRNs
=CEILING(((SUM(A1,-5000))/2),1)

Here's one way to anonymize medical record numbers (MRNs) using Excel. If the data are in columns A (MRN), B (date-time), and C (glucose), then create a new column D that takes the MRN in A and transforms it. For example, pasting the function at right into cell D1 would take the value in A1, subtract 5000, divide by 2, and round up to the nearest integer. Do not use this exact formula as it would defeat anonymity! Autofill to populate the rest of the cells in column D. Then, copy column D and using "Paste Special..." paste only the values into column A. Delete column D.

Timestamps

If you use Excel for the data file, note that it will display date-time in this form: 11/23/05 7:01 AM. If so, the entire date-time column should be selected and formatted as 'General.'

©2006 Prem Thomas, MD