Converting a Spreadsheet or Column-oriented Text Log (like Cabrillo) to ADIF

If you have a spreadsheet program, you can use it to convert most text logs, as well as spreadsheet logs, to ADIF. Instructions are given here for Microsoft Excel, OpenOffice.org's Calc, and Microsoft Works.

Before you start, make a backup copy of your log file and store it in a safe place (another folder, for example), so that when the inevitable happens and the file you are working on gets overwritten by accident, you can restore your data.

Importing a text (or Cabrillo) file

If your log is already in a spreadsheet format, you can skip to the next section.

If your log is a text file, such as a Cabrillo or other column-oriented log, you need to get it into a spreadsheet program, with each column of the text in a separate column of the spreadsheet. There are detailed step-by-step instructions in this section on how to do this with Excel and OpenOffice.org's Calc program. I haven't figured out how to do this easily using the Works spreadsheet program, as it always insists on putting all of the data in column A. You could try using a text editor to change all sequences of more than one space to a single space, then change all spaces to commas or tabs, and finally rename the file to have a .csv extension before opening it with the Works spreadsheet.

Excel: Open the Excel program. Select the File -> Open menu item. At the bottom of the dialog box, beside "Files of type:", choose "All Files (*.*)". Navigate through your file system until you find the text file you want to convert to ADIF. When you click on "Open" you should see a dialog box for the Text Import Wizard.

The first thing to do is scroll down in the file preview until you find the first actual data line. In a Cabrillo file, scroll past the header until you reach the first QSO: line. Make sure the number in the "Start import at row:" box points to this line.

Next you choose between Delimited and Fixed Width formats. If your file uses commas or semicolons to separate fields (e.g. a CSV file), select the Delimited format. The Fixed Width format is intended for files that are organized into fixed-position columns. If the widths of some of the columns vary, the Delimited format using space characters as the delimiters may work better. If some of the fields in your file can contain spaces (for example, a QTH like "New York" has a space in it), or if some fields can be left blank in some lines, you must select the Fixed Width option. Some files (e.g. most Cabrillo files) can be done either way, using spaces as the delimiters. Try it one way, and if that doesn't work well, try the other.

Fixed Width: When you click on Next, you will see a preview with vertical break lines between some of the columns. You will probably have to add, delete or move break lines to get them where you want them. For left-justified fields, you will want to put the break immediately to the left of the column; for right-justified fields, the break works best immediately to the right of the column. You want to set things up so that the columns are cleanly separated by break lines, and none of the fields are split up by a break line in the middle of the field.

Delimited: When you click on Next, you will be asked to select the delimiter character(s) your file uses. If the fields are separated by commas or semicolons, check the appropriate box. If they are separated by spaces, as in a Cabrillo file, check "Space" and make sure that the "Treat consecutive delimiters as one" box is also checked (note that for a comma- or semicolon-delimited file, this box should not be checked).

With either format, after clicking on Next you are able to select the data format for each field or column. You can choose to ignore, i.e. not to import, a column (e.g. the "QSO:" at the beginning of each Cabrillo line). If a column contains dates, it is best to choose "Date" with the correct order (YMD for a Cabrillo file) - it's a lot easier to handle dates if they are imported as dates rather than text or numbers.

When you click on Finish, the log will be imported. The date column format may be different from the way it was in the log - that's OK, we will fix up the date format later. Down at the bottom of the spreadsheet you will see a tab with the file name (not including the .txt or .log or .cbr extension). You will be using this name later, so take note of it.

If your log file contains extra lines, such as for example summary lines at the beginning and/or end of each page, you should delete them from the spreadsheet. If you imported a Cabrillo file and there is an END-OF-LOG: line (or a remnant of it) at the end of the file, you should delete that line from the spreadsheet.

Calc: You start things off differently with this program. If you try to open a .txt file with Calc, it will outsmart you and open it with Writer instead, which is not what you want. Rename the file so that its filename ends in .csv (you remembered to make a backup first, didn't you?). Calc uses .csv for all text-format spreadsheets, even in fixed column formats. Now start the Calc program, select File -> Open, if necessary set "Files of type:" to "All Files (*.*)", find the newly renamed .csv file and open it. This will open the Text import dialog box.

Unlike Excel, everything is done in the one dialog box (no "Next" steps). In the preview box, scroll down to the first data line and set the starting row number in the "From row:" box. Choose either "Fixed width" or "Separated by", depending on whether the file is in fixed columns or uses separators between fields. Some files (e.g. many Cabrillo files) will work either way, using spaces as the delimiters. If you choose Fixed width, you will have to set all of the column separators in the column numbering bar above the preview of the file. If you choose "Separated by", you can choose the delimiter character; if it is 'Space", remember to check the "Merge delimiters" box as well. In either case, once you have got the columns set up, then above each field you can choose "Standard", "Hide" (for fields you don't want to import, like the "QSO:" in a Cabrillo file), or "Date (YMD)" for the date field (assuming it is in the yyyy-mm-dd order).

When you click on "OK", the file will be imported. The tab at the bottom will probably read "Sheet1". That's fine; you don't need to change it. Remember to delete extraneous rows from the spreadsheet (e.g. page summary lines, the END-OF-LOG: line at the end of a Cabrillo file).

Converting to ADIF

Now that you have your log imported into a spreadsheet, you can use spreadsheet text manipulation formulas to create a new worksheet with the data in ADIF format. In the examples that follow, I will assume that you have imported a "standard" Cabrillo log with frequency in column A, mode in column B, date in column C, time in column D (don't worry if 0001 shows up as just 1; we will fix that), your call sign in column E, two sent exchange fields in columns F and G, the other station's call sign in column H, and two received exchange fields in columns I and J. You should be able to figure out how to handle other log formats based on the description below.

First, create a new worksheet to hold the ADIF file. In Excel, you use the Insert -> Worksheet menu item to do this. The tab for the new (empty) worksheet will be named "Sheet1". Below, I am going to assume that the original worksheet was called "Mylog"; you will have to change the formulas in the instructions below to use the actual name. In Calc, you use the Insert -> Sheet menu item. The new sheet will probably be named "Sheet2". The Works spreadsheet program does not support worksheets, so you will have to use a separate set of columns in the main spreadsheet instead. I will assume you are using columns AA, AB, ... to hold the ADIF fields.

In cell A1 of the new sheet (cell AA1 in the Works spreadsheet), enter the following formula:

Excel:   
= "<freq:" & LEN(Mylog!A1) + 1 & ">" & TEXT(Mylog!A1/1000, "0.000")
Calc:    = "<freq:" & LEN(Sheet1.A1) + 1 & ">" & TEXT(Sheet1.A1/1000; "0.000")
Works:    = "<freq:" & STRING(LENGTH(STRING(A1, 0)) + 1, 0) & ">" & STRING(A1/1000, 3)
If the entry in cell A1 of the original log was 14070, the entry in the new cell should look like <freq:6>14.070

In cell B1 of the new sheet (cell AB1 in the Works spreadsheet), enter the following formula:

Excel:   
= IF (Mylog!B1 = "CW", "<mode:2>CW", IF (Mylog!B1 = "PH", "<mode:3>SSB", IF (Mylog!B1 = "RY", "<mode:4>RTTY", "<mode:0>")))
Calc:   
= IF (Sheet1.B1 = "CW"; "<mode:2>CW"; IF (Sheet1.B1 = "PH"; "<mode:3>SSB"; IF (Sheet1.B1 = "RY"; "<mode:4>RTTY"; "<mode:0>")))
Works:   
= IF (B1 = "CW", "<mode:2>CW", IF (B1 = "PH", "<mode:3>SSB", IF (B1 = "RY", "<mode:4>RTTY", "<mode:0>")))
If the entry in cell B1 of the original log was RY, the entry in the new cell should look like <mode:4>RTTY

In cell C1 of the new sheet (cell AC1 in the Works spreadsheet), enter the following formula:

Excel:   
= "<qso_date:8>" & YEAR(Mylog!C1) & TEXT(MONTH(Mylog!C1), "00") & TEXT(DAY(Mylog!C1), "00")
Calc:   
= "<qso_date:8>" & YEAR(Sheet1.C1) & TEXT(MONTH(Sheet1.C1); "00") & TEXT(DAY(Sheet1.C1); "00")
Works:   
= "<qso_date:8>" & STRING(YEAR(C1, 0) + 1900, 0) & IF (MONTH(C1) < 10, "0" & STRING(MONTH(C1), 0), STRING(MONTH(C1), 0)) & IF (DAY(C1) < 10, "0" & STRING(DAY(C1), 0), STRING(DAY(C1), 0))
If the entry in cell C1 of the original log was 2000-01-01, the entry in the new cell should look like <qso_date:8:d>20000101

In cell D1 of the new sheet (cell AD1 in the Works spreadsheet), enter the following formula:

Excel:   
= "<time_on:4>" & TEXT(Mylog!D1, "0000")
Calc:   
= "<time_on:4>" & TEXT(Sheet1.D1; "0000")
Works:   
= "<time_on:4>" & IF (D1>999, STRING(D1, 0), IF (D1>99, "0" & STRING(D1, 0), IF (D1>9, "00" & STRING(D1, 0), "000" & STRING(D1,0))))
If the entry in cell D1 of the original log was 0123, the entry in the new cell should look like <time_on:4>0123

In cell E1 of the new sheet (cell AE1 in the Works spreadsheet), enter the following formula:

Excel:   
= "<station_callsign:" & LEN(Mylog!E1) & ">" & Mylog!E1
Calc:   
= "<station_callsign:" & LEN(Sheet1.E1) & ">" & Sheet1.E1
Works:   
= "<station_callsign:" & STRING(LENGTH(E1), 0) & ">" & E1
If the entry in cell E1 of the original log was VE3KI, the entry in the new cell should look like <station_callsign:5>VE3KI

NOTE: If the program the ADIF file is to be imported into supports only the older ADIF 1.0 specification, then you may want to change "station_callsign" in the above formulas to "operator". Optionally, you could choose not to include this field at all, simply leaving this cell empty, but in what follows I am going to use this cell as the basis for the remaining formulas.

Copy the formula from cell E1 of the new sheet and paste it into cell F1 (from AE1 to AF1 in the Works spreadsheet). Select cell F1 (AF1 in Works), put the cursor in the formula entry box above the sheet and change "station_callsign" to one of the following, depending on the contest exchange (use the one appropriate to the first exchange element for the contest):
For your sent RST, use "rst_sent"
For your sent serial number, use "stx"
For your transmitted power, use use "tx_pwr"
For your name, use "my_name" (ADIF 2 only)
For your state/province, use "my_state" (ADIF 2 only)
For your CQ zone, use "my_cq_zone" (ADIF 2 only)
For your ITU zone, use "my_itu_zone" (ADIF 2 only)
For your grid square, use use "my_gridsquare" (ADIF 2 only)
For your IOTA designator, use use "my_IOTA" (ADIF 2 only)
If your contest exchange includes a field that is not supported by ADIF (e.g. county abbreviations that do not match the ADIF format for counties), you can use "stx_string" as the field name in ADIF 2, or "comment" in ADIF 1.

Repeat the above for cell G1, except that the identifier will be the one appropriate to the second exchange element.

Repeat again for cell H1. This time change the identifier to "call". If the entry in cell H1 of the original log was AA6YQ/1, the entry in the new cell should look like <call:7>AA6YQ/1

Repeat again for cell I1. This time the possible identifiers to use include:
For the received RST, use "rst_rcvd"
For the received serial number, use "srx"
For the received power, use "rx_pwr"
For the received name, use "name"
For the received state/province, use "state"
For the received CQ zone, use "cqz"
For the received ITU zone, use "ituz"
For the received grid square, use "gridsquare"
For the received IOTA designator, use "IOTA"
For the received ARRL section, use "ARRL_sect"
For the received age, use "age"
For contest exchange fields that are not supported by ADIF, you can use "srx_string" as the field name in ADIF 2, or "comment" in ADIF 1.

Repeat for cell J1.

Assuming you have now converted all of the fields in the first line of the original log, enter the following into cell K1 (AK1 in Works):
"<eor>"

Now select cells A1 to K1 (AA1 to AK1 in Works) and press Ctrl-C (for Copy). Select cells from A2 down far enough to cover the entire log (e.g. if there are 975 lines in the log in the first sheet, down as far as K975 - AA2 down to AK975 in Works) and press Ctrl-V (for Paste). The entire log should now be in the new sheet in ADIF format, in cells A1 to K975 in Excel or Calc, and in cells AA1 to AK975 in Works (for our example with 975 entries in the log).

Before exporting the ADIF file, you might want to save all those formulas you typed in so that you can use them again another time. Choose the File -> Save As menu item, choose a new file name (e.g. ADIF_conversion.xls), select the file type (choose a spreadsheet format that supports the spreadsheet features and formulas you have used; do not use a text format or you will lose all the formulas), and save the file. You can reopen this file later to recover the formulas for use with another log file. After importing another file into the spreadsheet program and creating a new sheet, you can select the formulas in A1 to K1 (AA1 to AK1 in Works) in your saved spreadsheet file, copy them, and paste them into the new sheet. You will probably have to edit the formulas in columns F, G, I and J for each new contest, but that's a lot easier than retyping all of the formulas every time you want to convert another log.

Exporting the ADIF file

To export the log from Excel, select the File -> Save As menu item. In the "Save as type:" box, select "Text (Tab delimited) (*.txt)". Choose the output file name (e.g. file.adi) and enter it in the "File name:" box. Click on "Save". Excel will complain twice, first about saving only the current sheet from a multiple-sheet workbook (click "OK"), and then about losing advanced features in text format (click "Yes"). You should now have a valid ADIF file containing your log data, which you can import into a logging or QSLing program, upload to eQSL, sign with tQSL, etc.

To export the log from Calc, select the File -> Save As menu item. Enter the output file name (e.g. file.adi) in the "File name:" box. In the "Save as type:" box, select "Text CSV (.csv)". Uncheck the "Automatic file extension" box, and be sure to check the "Edit filter settings" box (important!). Click on Save. The program may complain about losing advanced features in text format - click "Yes". You should now see a dialog box for export of text files; if you don't, you forgot to check the "Edit filter settings" box, in which case you should cancel out and try again.. In the export text dialog box, he "Field delimiter" should read either {Tab} or {space}. The "Text delimiter" box should be completely empty (Important: do NOT leave the default " in this box!). The "Save cell content as shown" box should be checked, and "Fixed column width" should not be checked. Click on "OK" to save the file. There will be a warning about saving only the current sheet, just click "OK".

To export the log from Works, you will first have to delete all of the original log data. In the example, select columns A to Z and then select the Insert -> Delete Column menu item (do not use the Delete key; that only clears the columns, it does not delete them). The ADIF fields that were originally in columns AA to AK should now be in columns A to K. Now select the File -> Save As menu item. In the "Save as type:" box, select "Text & tabs (*.txt)". Enter the output file name (e.g. file.adi) in the "File name:" box. Click on "Save", and when it asks "OK to save without formatting?", click on "OK".

Thanks to Gary Danaher for the suggestion that led to the procedure on this page.

Top of page

Back to ADIF page