ADIF conversion


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
<mode:2>AM
<mode:2>FM
<mode:3>ATV
<mode:3>SSB
<mode:3>PKT
<mode:4>RTTY
<mode:4>SSTV
<mode:3>TOR [use this for AMTOR]
<mode:3>PAC [use this for PACTOR]
<mode:3>CLO [use this for CLOVER]

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>