Manual for ADIF conversion from Excel Log
Here is a comprehensive manual to convert Excel Log into ADIF.
You can convert your original Excel Log into ADIF format
and upload to eQSL log or further convert to ARRL LoTW Log.
For LoTW log conversion please check here: http://www.arrl.org/lotw/
0. Log Backup
Please backup you important log file before starting.
It is strongly recommended to copy your log sheet to modify as below.
1. Excel Format
Please arrange your log into following format with exact cell # location.
You can add as many rows as you have QSO data.
Freq is optional and you can do without this data.
Rename this sheet 1 as "Log".
A B C D E F G H 2 My call AB2RF 3 4 Call Date Time Band Mode RST rcvd RST sent Freq 5 P5DX 01-Apr-04 12:12 40 SSB 59 59 7050 6 VK0H 01-Apr-04 13:13 20 CW 599 599 14010 7 BS7K 01-Apr-04 14:14 15 SSB 59 59 21290 8 7O1Z 01-Apr-04 15:15 10 CW 599 599 28020
2. Data Conversion to ADIF Format
Please go to blank sheet 2.
1) Call
Copy the next line exactly into Cell A1 as text and find output in A1 like below.
="<call:"&LEN(Log!A5)&">"&Log!A5
<call:4>P5DX
2) Date
Copy the next line exactly into Cell B1 as text and find output in B1 like below.
="<qso_date:8>"&YEAR(Log!B5)&IF(MONTH(Log!B5)<10,"0","")&MONTH(Log!B5)&IF(DAY(Log!B5)<10,"0","")&DAY(Log!B5)
<qso_date:8>20040401
If you are using character (not date) nature please input below instead.
This may be only OK with "20040401" style input same as required output.
="<qso_date:"&LEN(Log2!B5)&">"&Log2!B5
3) Time
Copy the next line exactly into Cell C1 as text and find output in C1 like below.
="<time_on:4>"&IF(HOUR(Log!C5)<10,"0"&HOUR(Log!C5),HOUR(Log!C5))&IF(MINUTE(Log!C5)<10,"0"&MINUTE(Log!C5),MINUTE(Log!C5))
<time_on:4>1212
If you are using character (not time) nature like 1212 please input below instead.
="<time_on:4>"&IF(Log2!C5<10,"0","")&IF(Log2!C5<100,"0","")&IF(Log2!C5<1000,"0","")&Log2!C5
4) Band
Copy the next line exactly into Cell D1 as text and find output in D1 like below.
="<band:"&LEN(Log!D5) + 1&">"&Log!D5&"M"
<band:3>40M
If you are using 40M with "M" as band data please input below instead.
="<band:"&LEN(Log2!D5)&">"&Log2!D5
5) Mode
Copy the next line exactly into Cell E1 as text and find output in E1 like below.
="<mode:"&LEN(Log!E5)&">"&Log!E5
<mode:3>SSB
6) RST rcvd
Copy the next line exactly into Cell F1 as text and find output in F1 like below.
="<rst_rcvd:"&LEN(Log!F5)&">"&Log!F5
<rst_rcvd:2>59
7)RST sent
Copy the next line exactly into Cell G1 as text and find output in G1 like below.
="<rst_sent:"&LEN(Log!G5)&">"&Log!G5
<rst_sent:2>59
8) Freq
Copy the next line exactly into Cell H1 as text and find output in H1 like below.
="<freq:"&LEN(Log!H5)&">"&Log!H5
<freq:4>7050
If you don't have Freq data just skip this and input <eor> in H1 in the next step.
9) End of record
Copy the next line exactly into Cell I1 as text and find output in I1 like below.
<eor>
<eor>
Now you can see the sheet 2 row 1 as below and if successful please copy row 1 to all rows with QSO data. Congraturations !
<call:4>P5DX <qso_date:8>20040401 <time_on:4>1212 <band:3>40M <mode:3>SSB <rst_rcvd:2>59 <rst_sent:2>59 <freq:4>7050 <eor>
3. ADIF generation(revised)
Please set the width of each cell to 24(200pixel) and this will avoid data loss in long cell.
Save this sheet as "AB2RF-log.ADI" for example with file type TEXT(tab separation).
Ignore 2 error notices while saving..
4. QSL SEND/RCVD data addition(added)
To add QSL SEND & RCVD data, please try following procedure.
Please extend your Excel sheet named "Log" to include column "I" for QSL_SEND and
column "J" for QSL_RCVD.
Then please change to "Sheet2" and add following formula to each cell.
"I5" in Sheet 2
="<QSL_SEND:"&LEN(Log!I5)&">"&Log!I5
"J5" in Sheet 2
="<QSL_RCVD:"&LEN(Log!J5)&">"&Log!J5
5. Advanced course
If you input A1 with all of above formula combined with & in between and only initial =,
you can have compressed ADIF format data as output in A1.
="<call:"&LEN(Log!A5)&">"&Log!A5&"<qso_date:8>"&YEAR(Log!B5)&IF(MONTH(Log!B5)<10,"0","")&MONTH(Log!B5)&IF(DAY(Log!B5)<10,"0","")&DAY(Log!B5)&"<time_on:4>"&IF(HOUR(Log!C5)<10,"0"&HOUR(Log!C5),HOUR(Log!C5))&IF(MINUTE(Log!C5)<10,"0"&MINUTE(Log!C5),MINUTE(Log!C5))&"<band:"&LEN(Log!D5)+1&">"&Log!D5&"M"&"<mode:"&LEN(Log!E5)&">"&Log!E5&"<rst_rcvd:"&LEN(Log!F5)&">"&Log!F5&"<rst_sent:"&LEN(Log!G5)&">"&Log!G5&"<freq:"&LEN(Log!H5)&">"&Log!H5&"<eor>"
<call:4>P5DX<qso_date:8>20040401<time_on:4>1212<band:3>40M<mode:3>SSB<rst_rcvd:2>59<rst_sent:2>59<freq:4>7050<eor>
Then you can copy A1 to B1, C1..... all the data rows.
In this case you can save this sheet directly as ADI file.
Please save this file as "AB2RF.ADI"(" " need to be included) with file type TEXT(tab separation).
6. ADIF feasibility check
You can check these files with logging/QSL software like Logger or BV7.