ADIF SOTA and v2 Import


Update 112013

Logging software now supports my_sota and sota fields.


#SQL to update my_sota infer form comments field.

UPDATE qso_table_v005 SET my_sota=trim(substr(comments,5,11)) WHERE (comments LIKE ‘SOTA%’);


#SQL to create files for import into SotaWatch

.mode csv

.headers OFF

.output 11—2013-activate.csv
select “VK3HRA/p”,strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),trim(my_sota),rx_frequency,mode,call,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v005 where my_sota <>”” and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’11-2013′;

.mode csv
.headers OFF
.output 11—2013_chase.csv
select “v2″,”VK3HRA/p”,trim(substr(my_sota,1,10)),strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),rx_frequency,mode,call,trim(substr(sota,1,10)),’s’ ||rst_sent ||’ r’||rst_received from qso_table_v005 where (my_sota <>”” OR sota <> “”) and (strftime(“%m-%Y”,qso_start,’unixepoch’) = ’11-2013′);
.output stdout


Creates two files. One for chaser and and for activation.




ADIF – Amateur Data Interchange Format – is an open standard for exchange of data between amateur radio software, services and websites.

ADIF specifications are prepared by the ADIF Developers Group.

Current Released VersionVersion 3.0.4 

ADIF spec contains SOTA information:

MY_SOTA_REF the logging station’s International SOTA Reference.

SOTA_REF the contacted station’s International SOTA Reference.

Both are of type “SOTARef” which is defined as:

a sequence of Characters representing an International SOTA Reference. The sequence comprises:

an ITU prefix
if applicable, a SOTA subdivision
a / Character
a SOTA Reference Number


The latest beta of MacLoggerDX is supports  a new field named SOTA. (Don, VE3VRW)

The intent is to create files in a format to upload-file to SotaWatch

SOTA-Style comma-separated CSV-file:


date-format: dd/mm/yyyy or dd/mm/yy
time-format: hh:mm or hhmm

Recording SOTA activation summit in Comments fields and using SOTA field to store worked summit.

Still need to work out how to extract only data from selected date so still using month.

The following script extracts three files from MacLoggerDX v4 database for import to Sota Database,

.headers ON
.mode column
.output stdout
select strftime(“%d/%m/%Y” ,qso_start,’unixepoch’), strftime( “%H:%M”,qso_start,’unixepoch’),sota,trim(substr(comments,5,11)),rx_frequency,mode,call,first_name,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v004 where (comments LIKE ‘SOTA%’ or sota <>””) and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’09-2013′;

.mode csv
.headers OFF
.output activate-output.csv
select “VK3HRA/p”,strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),trim(substr(comments,5,11)),rx_frequency,mode,call,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v004 where comments LIKE ‘SOTA%’ and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’09-2013′;

.output chase-output.csv
select “VK3HRA”,strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),trim(substr(sota,1,10)),rx_frequency,mode,call,’s’ ||rst_sent ||’ r’||rst_received from qso_table_v004 where sota <> “” and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’09-2013′;

.output S2S-output.csv
select “v2″,”VK3HRA/p”,trim(substr(comments,5,11)),strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H:%M”,qso_start,’unixepoch’),rx_frequency,mode,call,trim(substr(sota,1,10)),’s’ ||rst_sent ||’ r’||rst_received from qso_table_v004 where comments LIKE ‘SOTA%’ and sota <> “” and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’09-2013′;

.output stdout


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: