Mobile database import file support

It's not always possible to have your Mac with you when travelling so there is support for data files created by mobile database applications.

Import data format

The data file format is a {comma} or {tab} delimited text file: the first line always lists the columns. An example follows after the format description (the lines have been split to fit on the page).

The import file can be used with the "Diabetes Logbook X - Export format" import option.

Data column name Can a different column name be used in the file header? Data content

PFlag No This column must be called 'PFlag'.

No value = not flagged

0 = not flagged

The string 'false' = not flagged

Any other value = flaged

Date Yes The date format can be day/month/year or month/day/year: this is a selectable option during data export/import.
The supported date formats [from Palm OS System Prefs] are:

D/M/Y

M/D/Y

D.M.Y

M.D.Y

D-M-Y

M-D-Y

The date's year may be either 2 or 4 digits long, for example 07 or 2007.

Time Yes The supported time formats [from Palm OS System Prefs] are:

HH:MM

HH:MM am/pm

HH.MM

HH.MM am/pm

Time formats with {comma} separation are only supported when {tab} delimiting is used:

HH,MM

HH,MM am/pm

24hr time is 00:00 to 23:59.
See note 5.

Period Yes The time periods are recalculated during import. The English options are:

Before Breakfast

After Breakfast

Before Lunch

After Lunch

Before Dinner

After Dinner

Before Bedtime

Night

Glucose Yes Floating point number.
See note 2.

GlucoseUnits Yes Must be either:

the text string: mmol/L

the text string: mg/dL

See note 3.

InsulinA Yes Floating point number.
See note 2.

InsulinAName Yes List field.
The list is user-defined: no {comma} or {tab} characters.

See note 3.

Carbs Yes Floating point number.
See note 2.

InsulinB Yes Floating point number.
See note 2.

InsulinBName Yes List field.
The list is user-defined: no {comma} or {tab} characters.

See note 3.

Medicine Yes Floating point number.
See note 2.

MedicineName Yes List field.
The list is user-defined: no {comma} or {tab} characters.

See note 3.

KetonesTested Yes Must be either:

Empty

the text string: -ve

the text string: +ve

See note 3.

KetonesValue Yes Floating point number.
See note 2.

Weight Yes Floating point number.
See note 2.

Height Yes Floating point number.
See note 2.

HbA1c Yes Floating point number.
See note 2.

EventName Yes List field.
The list is a user-defined with the following (English) preset:

Clinic

Staring item

No {comma} or {tab} characters.

Notes Yes Text field.
The Notes column uses '{lf}' to signify multiple lines in the notes text.

The Notes field can have {commas} and {tabs} in its text.

Notes:

The header row must be present.
All number fields must use a floating point format with a period, ".", or comma, ",", as the decimal mark. Examples of permitted values: 12.5 ; 0.05 ; 1,0 .
The time period, glucose units, insulins and medicines names columns must be present in the file but they can be empty and the preset default values from with in the logbook.
Any or all data fields can be enclosed in double-quotes, these will be stripped off during data import.
If the Time data includes seconds these will be stripped during import.
Any List field will be assumed to be empty if the field's value is "NO VALUE" or starts with "--".

First row, with column names:

PFlag,Date,Time,Period,Glucose,GlucoseUnits,InsulinA,
          InsulinAName,Carbohydrates,InsulinB,InsulinBName,Medicine,
          MedicineName,KetonesTested,KetonesValue,Weight,Height,HbA1c,
          EventName,Notes

Subsequent event rows, with day/month/year date format:

YES,27/07/06,10:51
          pm,Before Bedtime,12.0,mmol/L,12.0,NovoRapid,
          12.0,12.0,Levemir,12.0,GlucoGel,+ve,12.0,22.3,119.0,7.0,Clinic,
          

          HbA1c = 7.0%{lf}Weight = 22.3kg{lf}Height =
          119cm

iPhone/iPod touch Databases

There are relatively few database applications for the iPhone/iPod touch. So far only one has been tested, but it worked.

If anyone has used another iPhone application and have got it to generate CSV files that can be imported, please let me know by using the Support form.

HanDBase Database Manager v4.x

HanDBase is available as a paid application from the Apple iTunes App Store [where else] and currently priced at $9.99. A companion Mac Desktop application is also available direct from DDHSoftware, also priced at $9.99: this is currently a fairly limited application but is can export data in a number of different formatted delimited text files. DDHSoftware say that they working on improving the functionality to become more like the Windows Desktop. In normal use only the mobile application is needed.

HanDBase databases support many data types, I used these to create a working example: date, time, checkbox, float, integer, text [which support pop-up lists of pre-defined values]. It supports having multiple views of the database so that data columns can be included or excluded from the view of the list of records and/or when adding/editing a record. So I've got a Full Logbook view with all data columns; Standard Logbook view which hides some data columns that can have their values filled in with the Mac Logbook's default settings; Clinic view which restricts the data to those fields that may be recorded at clinics; Ketones view which focuses on ketone tests.

Databases can be created directly on the iPhone and it supports export of CSV files using a built-in web server: navigate to the web server view the CSV file and save it to disk. It really is that easy. Also, database files can be uploaded/downloaded using the web server, so I can provide you with a working example database that will produce the correct export file.

Go to the Download area to find the example database files. This time there are English, French, German, Italian and Spanish versions: thanks to DDHSoftware providing a Windows Desktop licence, while my first database was created on my iPod touch I really didn't want to have to localise it too.

Oh, any users that have HanDBase v4 on other mobile devices, this database file can be used on those too: I've confirmed this on a Palm TX, at least.

Palm Databases

There are many different Palm based database applications, most with Windows PC import/export applications. On the Mac there are more limited options, but there is the unsupported MPC from Möötjelitt that aids in extracting data from Palm built-in and 3rd party databases.

Two applications are discussed below, but there are plenty of others such as HandBase and Pilot-DB. An example of how to use Pilot-DB is provided in the Download area.

MobileDB v4.x

MobileDB is a shareware application. There is a time limited demo mode.

A database can be created that has each data field and supports the necessary data formats. To extract the data, first backup your Palm and then use MPC v1 to create a text file of the database..

Tips:

Create an empty pop-up list entry by creating a new entry with its name set to a {space}. Save the entry then click Rename, delete the space, and then save the entry.

MobileDB number format does not support {comma}-based floating point notation, so a compliant number is guaranteed in {comma} delimited files.

In a Mac 'Find/Replace' window use {option}-{tab} to enter a {tab} character in the search field.

JFile v5.x

JFile is a shareware application. In evaluation mode, it is limited to 1 database.

A database can be created that has each data field and supports the necessary data formats. To extract the data, first backup your Palm and then use MPC v1 to create a text file of the database.

An alternative is to export database records to a Palm memo: this is limited to 4KB, the old size limit of Palm Memopad memos. The memo is in {comma}-separated format and using MPC v2(beta) a suitable text file will be created.

Tips:

Use filters to select the needed events before exporting to Palm memo.

Many of the data formats do not seem to enforce compliance to their designated type - be careful to observe number formatting rules: integer numbers and floating point numbers with {period} decimal marks.

Diabetes Logbook X