 |
If you need to move your logbook between applications, chances are you will do it using the
"Amateur Data Interchange Format", or ADIF for short. These text files are usually found with an .adi type file name extension.
Although I use SD Logger for contests, my day-to-day logbook was originally entered onto a spreadsheet. Needless to say, this is not able to produce .adi format files automatically. What follows is how I make the conversion from spreadsheet .xls to .adi, suitable for uploading to eQSL and importing in to DXKeeper.
For the original conversion method and for details on the ADIF format standards, see the
VE3IAY website on
converting spreadsheets to
ADIF.
The following conversions are based upon two excel spreadsheet pages,
one called "144MHz log", containing my original logbook:
|
and one called "144MHz ADIF", containing the macros doing the conversion to ADIF format:
 |
What follows is the procedure to take each column of the logbook in turn, and convert it into an ADIF text file. It does this by taking the source data field and adding additional text [the ADIF field name], and a width delimiter number [the length of the data field]. Here is an example:
Spreadsheet logbook value of:
S59X
is converted via the macro:
="<call:"&LEN('144MHz log'!F2)&">"&'144MHz log'!F2
to give the ADIF field and value of:
<call:4>S59X
NOTE: where you see references in the macros to "144MHz log", this means that the macro is acting upon a cell in the "144MHz log" page of the spreadsheet logbook. You need to change this name to whatever you have called your spreadsheet logbook.
| Step 1 - the date fields | |
| "144MHz log"
has the following data in column A:
|
"144MHz
ADIF" has the following output in column A:
|
| The
macro used in cell A2 of "144MHz ADIF" is as follows:
="<qso_date:8:d>"&YEAR('144MHz log'!A2)&IF(MONTH('144MHz log'!A2)<10, "0"&MONTH('144MHz log'!A2), MONTH('144MHz log'!A2))&IF(DAY('144MHz log'!A2)<10, "0"&DAY('144MHz log'!A2), DAY('144MHz log'!A2)) which means that the data from "144MHz log" cell A2 is converted from DD/MM/YYYY format into YYYYMMDD format with a preceding ADIF field of <qso_date:8:d> added. You should then replicate the A2 cell macro downwards on the "144MHz ADIF" page. This means that "144MHz ADIF" cell A3 would have the following macro: ="<qso_date:8:d>"&YEAR('144MHz log'!A3)&IF(MONTH('144MHz log'!A3)<10, "0"&MONTH('144MHz log'!A3), MONTH('144MHz log'!A3))&IF(DAY('144MHz log'!A3)<10, "0"&DAY('144MHz log'!A3), DAY('144MHz log'!A3)) NOTE: I have done it this way so that the cell numbers agree between the two spreadsheet pages. This means that cell K37 on the "144MHz ADIF" page will use the data from the K37 cell on the "144MHz log" page. |
|
n
| Step 2 - the time fields | |
| "144MHz log"
has the following data in column B:
|
"144MHz
ADIF" has the following output in column B:
|
| The
macro used in cell B2 of "144MHz ADIF" is as follows:
="<time_on:4>"&IF('144MHz log'!B2>999, '144MHz log'!B2, IF('144MHz log'!B2>99, "0"&'144MHz log'!B2, IF('144MHz log'!B2>9, "00"&'144MHz log'!B2, "000"&'144MHz log'!B2))) which means that the data from "144MHz log" cell B2 is kept in HHMM format but with a preceding ADIF field of <time_on:4> added. You should then replicate the B2 cell macro downwards on the "144MHz ADIF" page. NOTE: If you log seconds as well [HHMMSS], then the ADIF field should be amended to <time_on:6>. |
|
n
| Step 3 - the frequency fields | |
| "144MHz log"
has the following data in column C:
|
"144MHz
ADIF" has the following output in column C:
|
| The
macro used in cell C2 of "144MHz ADIF" is as follows:
="<freq:"&LEN('144MHz log'!C2)&">"&'144MHz log'!C2 which means that the data from "144MHz log" cell C2 is kept in MHz format but with a preceding ADIF field of <freq> added. You should then replicate the C2 cell macro downwards on the "144MHz ADIF" page. NOTE: If you log in KHz [14097.5], then you should divide the KHz frequency value by 1000 to get it into MHz [14.0975]. |
|
n
| Step 4 - the mode fields | |
| "144MHz log"
has the following data in column D:
|
"144MHz
ADIF" has the following output in column D:
|
| The
macro used in cell D2 of "144MHz ADIF" is as follows:
="<mode:"&LEN('144MHz log'!D2)&">"&'144MHz log'!D2 which means that the data from "144MHz log" cell D2 is kept in the same format but with a preceding ADIF field of <mode> added. You should then replicate the D2 cell macro downwards on the "144MHz ADIF" page. NOTE: You need to ensure that you use the correct ADIF value and field length for the mode logged: <mode:2>CW |
|
n
| Step 5 - the power fields | |
| "144MHz log"
has the following data in column E:
|
"144MHz
ADIF" has the following output in column E:
|
| The
macro used in cell E2 of "144MHz ADIF" is as follows:
="<tx_power:"&LEN('144MHz log'!E2)&">"&'144MHz log'!E2 which means that the data from "144MHz log" cell E2 is kept in the same format but with a preceding ADIF field of <tx_power> added. You should then replicate the E2 cell macro downwards on the "144MHz ADIF" page. NOTE: If the power was 100W then the ADIF field would be <tx_power:3> etc. |
|
n
| Step 6 - the worked fields | |
| "144MHz log"
has the following data in column F:
|
"144MHz
ADIF" has the following output in column F:
|
| The
macro used in cell F2 of "144MHz ADIF" is as follows:
="<call:"&LEN('144MHz log'!F2)&">"&'144MHz log'!F2 which means that the data from "144MHz log" cell F2 is kept in the same format but with a preceding ADIF field of <call> added. You should then replicate the F2 cell macro downwards on the "144MHz ADIF" page. NOTE: If the callsign was VK7KLX/P/5 then the ADIF field would be <call:10> etc. |
|
n
| Step 7 - the R/S [sent] fields | |
| "144MHz log"
has the following data in column G:
|
"144MHz
ADIF" has the following output in column G:
|
| The
macro used in cell G2 of "144MHz ADIF" is as follows:
="<rst_sent:"&LEN('144MHz log'!G2)&">"&'144MHz log'!G2 which means that the data from "144MHz log" cell G2 is kept in the same format but with a preceding ADIF field of <rst_sent> added. You should then replicate the G2 cell macro downwards on the "144MHz ADIF" page. NOTE: If you sent only RS rather than RST, then the ADIF field would be <rst_sent:2>. |
|
n
| Step 8 - the Sent [serial number] fields | |
| "144MHz log"
has the following data in column H:
|
"144MHz
ADIF" has the following output in column H:
|
| The
macro used in cell H2 of "144MHz ADIF" is as follows:
="<stx:"&LEN('144MHz log'!H2)&">"&'144MHz log'!H2 which means that the data from "144MHz log" cell H2 is almost kept in the same format [preceding 0's are dropped], but with a preceding ADIF field of <stx> added. You should then replicate the H2 cell macro downwards on the "144MHz ADIF" page. NOTE: If you sent a serial number of 1445, then the ADIF field would be <rst_sent:4>. |
|
n
| Step 9 - the R/S [received] fields | |
| "144MHz log"
has the following data in column I:
|
"144MHz
ADIF" has the following output in column I:
|
| The
macro used in cell I2 of "144MHz ADIF" is as follows:
="<rst_rcvd:"&LEN('144MHz log'!I2)&">"&'144MHz log'!I2 which means that the data from "144MHz log" cell I2 is kept in the same format but with a preceding ADIF field of <rst_rcvd> added. You should then replicate the I2 cell macro downwards on the "144MHz ADIF" page. NOTE: If you received only RS rather than RST, then the ADIF field would be <rst_rcvd:2>. |
|
n
| Step 10 - the Received [serial number] fields | |
| "144MHz log"
has the following data in column J:
|
"144MHz
ADIF" has the following output in column J:
|
| The
macro used in cell J2 of "144MHz ADIF" is as follows:
="<srx:"&LEN('144MHz log'!J2)&">"&'144MHz log'!J2 which means that the data from "144MHz log" cell J2 is almost kept in the same format [preceding 0's are dropped], but with a preceding ADIF field of <srx> added. You should then replicate the J2 cell macro downwards on the "144MHz ADIF" page. NOTE: If you received a serial number of 337, then the ADIF field would be <srx:3>. |
|
n
| Step 11 - the Grid fields | |
| "144MHz log"
has the following data in column K:
|
"144MHz
ADIF" has the following output in column K:
|
| The
macro used in cell K2 of "144MHz ADIF" is as follows:
="<gridsquare:"&LEN('144MHz log'!K2)&">"&'144MHz log'!K2 which means that the data from "144MHz log" cell K2 is kept in the same format but with a preceding ADIF field of <gridsquare> added. You should then replicate the K2 cell macro downwards on the "144MHz ADIF" page. NOTE: If you received only a 4-character gridsquare, e.g. JO01, then the ADIF field would be <gridsquare:4>. |
|
n
| Step 12 - the QTH fields | |
| "144MHz log"
has the following data in column L:
|
"144MHz
ADIF" has the following output in column L:
|
| The
macro used in cell L2 of "144MHz ADIF" is as follows:
="<QTH:"&LEN('144MHz log'!L2)&">"&'144MHz log'!L2 which means that the data from "144MHz log" cell L2 is kept in the same format but with a preceding ADIF field of <qth> added. You should then replicate the L2 cell macro downwards on the "144MHz ADIF" page. |
|
n
| Step 13 - the Name fields | |
| "144MHz log"
has the following data in column M:
|
"144MHz
ADIF" has the following output in column M:
|
| The
macro used in cell M2 of "144MHz ADIF" is as follows:
="<name:"&LEN('144MHz log'!M2)&">"&'144MHz log'!M2 which means that the data from "144MHz log" cell M2 is kept in the same format but with a preceding ADIF field of <name> added. You should then replicate the M2 cell macro downwards on the "144MHz ADIF" page. |
|
n
| Step 14 - the EOR fields | |
| "144MHz log"
does not have this field.
|
"144MHz
ADIF" has the following default in column N:
|
| There
is no macro used in cell N2 of "144MHz ADIF" as it is set to a
default as follows:
<eor> which means that the ADIF field of <eor> is inserted. You should then replicate the N2 cell macro downwards on the "144MHz ADIF" page. NOTE: ADIF uses the <eor> field to indicate the "End Of Record" for that row of data. |
|
n
NOTE: At this point I have converted the spreadsheet logbook into ADIF format, based upon the information contained in my logbook. It may be that you have other information that you also want to include in the ADIF file. A full list of the ADIF field specifications can be found at http://www.hosenose.com/adif/adif.html. Some of the more common fields include:
<BAND>
<CNTY> [US state,county e.g. CA,LOS ANGELES]
<COMMENT>
<IOTA> [e.g. EU-005]
<QSL_SENT>
We now have a spreadsheet page [144MHz ADIF] full of ADIF fields and logbook data:
 |
This needs to be saved as a "tab-delimited text file". This will save the spreadsheet page [144MHz ADIF] with a .txt file type extension:
 |
Check the file by opening it with "Notepad" or other simple word processor:
 |
All you have to do now is delete the top line as that was only in the spreadsheet as a guide, and then rename the file with a .adi file type extension.
"144MHz ADIF.txt" becomes "144MHz ADIF.adi"
It may be that you want to add new ADIF fields to the spreadsheet conversion, or to the converted text file. Below is a compiled list of some of the ADIF fields and conversion code used, was well as some other codes and codes that may be of use. Note that these were on an Excel spreadsheet!
| Data source | ADIF Code | Spreadsheet macro |
| The date of the QSO | QSO_DATE | ="<qso_date:8:d>"&YEAR('160-80-40-20-15-10m'!A2)&IF(MONTH('160-80-40-20-15-10m'!A2)<10, "0"&MONTH('160-80-40-20-15-10m'!A2), MONTH('160-80-40-20-15-10m'!A2))&IF(DAY('160-80-40-20-15-10m'!A2)<10, "0"&DAY('160-80-40-20-15-10m'!A2), DAY('160-80-40-20-15-10m'!A2)) |
| The start
time of the QSO Assumption: the end time equals the start time |
TIME_ON | ="<time_on:4>"&IF('160-80-40-20-15-10m'!B2>999, '160-80-40-20-15-10m'!B2, IF('160-80-40-20-15-10m'!B2>99, "0"&'160-80-40-20-15-10m'!B2, IF('160-80-40-20-15-10m'!B2>9, "00"&'160-80-40-20-15-10m'!B2, "000"&'160-80-40-20-15-10m'!B2))) |
| The band used | BAND | ="<band:"&LEN('160-80-40-20-15-10m'!C2)&">"&'160-80-40-20-15-10m'!C2 |
|
The mode used Can be preset if all QSO's are the same mode |
MODE | ="<mode:"&LEN('160-80-40-20-15-10m'!D2)&">"&'160-80-40-20-15-10m'!D2 |
| <mode:3>SSB | ||
| <mode:2>CW | ||
|
The power used Note that milliwatts have to be entered as 0.5 not 500 Can be preset if all QSO's are made using the same power |
TX_POWER | ="<tx_power:"&LEN('160-80-40-20-15-10m'!E2)&">"&'160-80-40-20-15-10m'!E2 |
| <tx_power:3>100 | ||
| The callsign worked | CALL | ="<call:"&LEN('160-80-40-20-15-10m'!F2)&">"&'160-80-40-20-15-10m'!F2 |
|
The R/S/T report sent Can be preset if all QSO's are made using the report |
RST_SENT | ="<rst_sent:"&LEN('160-80-40-20-15-10m'!G2)&">"&'160-80-40-20-15-10m'!G2 |
| <rst_sent:2>59 | ||
|
The serial number or exchange
sent Can be preset if all QSO's are made using a fixed exchange such as ITU or CQ zone |
STX | ="<stx:"&LEN('160-80-40-20-15-10m'!H2)&">"&'160-80-40-20-15-10m'!H2 |
| <stx:2>27 | ||
|
The R/S/T report received Can be preset if all QSO's are made using the report |
RST_RCVD | ="<rst_rcvd:"&LEN('160-80-40-20-15-10m'!I2)&">"&'160-80-40-20-15-10m'!I2 |
| <rst_rcvd:2>59 | ||
| The serial number or exchange received | SRX | ="<srx:"&LEN('160-80-40-20-15-10m'!J2)&">"&'160-80-40-20-15-10m'!J2 |
| The gridsquare of the station worked | GRIDSQUARE | ="<gridsquare:"&LEN('160-80-40-20-15-10m'!K2)&">"&'160-80-40-20-15-10m'!K2 |
| Has a QSL
card been sent via the bureau If set to R=Requested then DXK can print out the cards for you |
QSL_SENT | ="<QSL_SENT:"&LEN('160-80-40-20-15-10m'!L2)&">"&'160-80-40-20-15-10m'!L2 |
|
The date the QSL card was sent
via the bureau Can be set automatically by DXK when printing the cards Can be set using IF/THEN logic based on QSL_SENT setting and the QSO date |
QSLSDATE | ="<qslsdate:8:d>"&YEAR('160-80-40-20-15-10m'!A2)&IF(MONTH('160-80-40-20-15-10m'!A2)<10, "0"&MONTH('160-80-40-20-15-10m'!A2), MONTH('160-80-40-20-15-10m'!A2))&IF(DAY('160-80-40-20-15-10m'!A2)<10, "0"&DAY('160-80-40-20-15-10m'!A2), DAY('160-80-40-20-15-10m'!A2)) |
| =IF(('160-80-40-20-15-10m'!L2)="Y","<qslsdate:8:d>"&YEAR('160-80-40-20-15-10m'!A2)&IF(MONTH('160-80-40-20-15-10m'!A2)<10, "0"&MONTH('160-80-40-20-15-10m'!A2), MONTH('160-80-40-20-15-10m'!A2))&IF(DAY('160-80-40-20-15-10m'!A2)<10, "0"&DAY('160-80-40-20-15-10m'!A2), DAY('160-80-40-20-15-10m'!A2)),"<qslsdate:0>") | ||
| The QSL manager | QSL_VIA | ="<QSL_VIA:"&LEN('160-80-40-20-15-10m'!N2)&">"&'160-80-40-20-15-10m'!N2 |
| Has a QSL
been received via the bureau Manually changed to Y in DXK once QSL card received. Don't set to V unless verified by LOTW |
QSL_RCVD | ="<QSL_RCVD:"&LEN('160-80-40-20-15-10m'!O2)&">"&'160-80-40-20-15-10m'!O2 |
| eQSL card
sent If set to R=Requested then DXK can upload the contact to eQSL for you |
APP_DXKeeper_EQSL_QSL_SENT | ="<APP_DXKeeper_EQSL_QSL_sent:"&LEN('160-80-40-20-15-10m'!P2)&">"&'160-80-40-20-15-10m'!P2 |
| The date
the eQSL card was sent Set to QSO date if uploading manually to eQSL. DXK can set this automatically when it uploads to eQSL |
APP_DXKeeper_EQSL_QSL_SENT | ="<APP_DXKeeper_EQSL_QSLSdate:10:d>"&YEAR('160-80-40-20-15-10m'!A2)&"-"&IF(MONTH('160-80-40-20-15-10m'!A2)<10, "0"&MONTH('160-80-40-20-15-10m'!A2),MONTH('160-80-40-20-15-10m'!A2))&"-"&IF(DAY('160-80-40-20-15-10m'!A2)<10,"0"&DAY('160-80-40-20-15-10m'!A2),DAY('160-80-40-20-15-10m'!A2)) |
| The date the eQSL card was received | APP_DXKeeper_EQSL_QSL_RCVD | ="<APP_DXKeeper_EQSL_QSL_rcvd:"&LEN('160-80-40-20-15-10m'!R2)&">"&'160-80-40-20-15-10m'!R2 |
| Contacts name | NAME | ="<name:"&LEN('160-80-40-20-15-10m'!S2)&">"&'160-80-40-20-15-10m'!S2 |
| QTH of contact | QTH | ="<QTH:"&LEN('160-80-40-20-15-10m'!T2)&">"&'160-80-40-20-15-10m'!T2 |
| Any notes | NOTES | ="<notes:"&LEN('160-80-40-20-15-10m'!U2)&">"&'160-80-40-20-15-10m'!U2 |
| Frequency used | FREQ | ="<freq:"&LEN('160-80-40-20-15-10m'!V2)&">"&'160-80-40-20-15-10m'!V2 |
| IOTA | IOTA | ="<IOTA:"&LEN('160-80-40-20-15-10m'!AA2)&">"&'160-80-40-20-15-10m'!AA2 |
| eQSL card comment | QSL_COMMENT | <qsl_comment:28>Please QSL via BURO for DXCC |
| End of record | EOR | <eor> |
