www.muttznutz.net

Underwater photography by Andy Kirkland

Integrating SDM 1.6

Well, it’s fairly straightforward in principle, if you’ve played with relational databases .
Export the data from SDM, load it into an ODBC / JDBC data source, then access it from JAlbum.
The Dive number is used as the key; this is attached to the photos as a structured keyword in Elements (I’ll explain that in another item).
The database doesn’t have to be included on the web server – the data is incorporated into the web pages when you build the JAlbum album pages.

Exporting

This is the easy bit.
Select File/Export from the menu. Pick “ASCII in CSV format” and select a directory. The default is Suunto default – “C:\Program Files\SDM”. I prefer to store it somewhere else, so I created another folder in “My Documents\My DataFiles”. Enter a file root name (We’ll assume it’s “DiveLogs”)
This should create four files :

Divelogs.csv The main logbook file
Divelogs$NOT.csv Free-format Notes of the dive
Divelogs$PRO.csv Dive profile
Divelogs$DGE.csv Dive Gear

So far, I’ve only bothered integrating the first of these files.

Create an ODBC / JDBC datasource

There is a Windows driver for .CSV files, but I found this a bit unreliable.
The MS Excel driver was more stable, and MS Access would probably provide a stable platform. The SQL Server Express Edition may also be suitable as a low cost solution.
I preferred to load the data into MySQL – partly because of the ease of loading from .csv files.
However, please don’t ask me for support on these products – I’m not that much of an expert.

With Excel, you’ll need to insert field names in the top line of the spreadsheet, when save as an Excel file.
I used the following headings when using Excel:
[code lang=”dos”]Sequence DiveNo Dive_date Time_Down Series Dive_in_series Dive_time Surface_Interval Maximum_depth Average_depth Sample_rate_ 1 Computer_ID Personal_Information Sample_Rate_2 Gas_Model Altitude Personal_Mode Profile Location Dive_Site Weather Visibility Air_temperature Water_temperature Water_temperature_at_max Buddy1 Dive_leader Boat Tank Capacity Unknown1 Working_pressure Start_pressure End_pressure SLE_Consumption Unknown2 Dive_type Dive_school GPS Buddy Custom5 Weight NitroxO2Pct OLF OTU1CNS2[/code]

Note that the fields “Dive_type”,” Dive_school”,”GPS”,”Buddy” and “Custom5” are the 5 custom fields on the Dive Tab.
It looks like there’s just the one format in SDM 1.6, but some of the “Unknown” fields may be used by other computer models. If you have input on this, please email me.

I use the following script to build the table (called “divelog”) in MySQL (having set up a database called “divelogs”):
[code lang=”sql”]
DROP TABLE IF EXISTS `divelogs`.`DiveLog`;
CREATE TABLE `divelogs`.`DiveLog` (
Sequence varchar(200),
DiveNo varchar(200),
Dive_date date,
Time_Down varchar(200),
Series varchar(200),
Dive_in_series varchar(200),
Dive_time_Seconds float,
Surface_Interval_Seconds float,
Maximum_depth varchar(200),
Average_depth varchar(200),
Sample_rate_1 varchar(200),
Computer_ID varchar(200),
Personal_Information varchar(200),
Sample_Rate_2 varchar(200),
Gas_Model varchar(200),
Altitude varchar(200),
Personal_Mode varchar(200),
Profile varchar(200),
Location varchar(200),
Dive_Site varchar(200),
Weather varchar(200),
Visibility varchar(200),
Air_temperature varchar(200),
Water_temperature varchar(200),
Water_temperature_at_max varchar(200),
Buddy1 varchar(200),
Dive_leader varchar(200),
Boat varchar(200),
Tank varchar(200),
Capacity varchar(200),
Unknown1 varchar(200),
Working_pressure varchar(200),
Start_pressure varchar(200),
End_pressure varchar(200),
SLE_Consumption varchar(200),
Unknown2 varchar(200),
Dive_type varchar(200),
Dive_school varchar(200),
GPS varchar(200),
Buddy varchar(200),
Custom5 varchar(200),
Weight varchar(200),
NitroxO2Pct varchar(200),
OLF varchar(200),
OTU1CNS2 varchar(200)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[/code]

… then load the data through this script :
[code lang=”sql”]
delete from divelogs.divelog;
LOAD DATA INFILE ‘[put your full path in here]My Documents/My DataFiles/Divelogs.csv’
REPLACE
INTO TABLE divelogs.divelog
FIELDS
TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
ESCAPED BY ‘|’
LINES
TERMINATED BY ‘\n’;
ALTER TABLE `divelogs`.`divelog`
ADD COLUMN `Dive_Time` TIME AFTER `OTU1CNS2`;
ALTER TABLE `divelogs`.`divelog`
ADD COLUMN `Surface_Interval` TIME
AFTER `OTU1CNS2`;
update divelogs.`DiveLog`
set Dive_Time =
cast(
(cast((Dive_Time_Seconds / 3600) as UNSIGNED) * 10000) +
(cast((mod(Dive_time_Seconds , 3600) / 60) as UNSIGNED) * 100 +
mod(Dive_time_Seconds, 60)
) as TIME) ,
Surface_Interval =
cast(
(cast((Surface_Interval_Seconds / 3600) as UNSIGNED) * 10000) +
(cast((mod(Surface_Interval_Seconds , 3600) / 60) as UNSIGNED) * 100 +
mod(Surface_Interval_Seconds, 60)
) as TIME)
[/code]
… The last two segments convert SDM’s “seconds” based fields into TIME type fields

Don’t forget to test the connection.

The URI to TrackBack this entry is: https://muttznutz.net/muttzblog/suunto-dive-manager/integrating-sdm-16/trackback

RSS feed for comments on this post.


Valid XHTML 1.0 Strict