Tag Archives: logging

SOTA logs using CSV upload

Background

From a few questions received it appears some SOTA participants are unclear as to how to use the Excel template for SOTA uploads.

The approach I use described here is not the only one possible.  But if you are unsure how to start, this may help.

Steps required

  1. Download the template and save it to your templates directory
  2. Create a new spreadsheet within excel, based on the logsheet template
  3. Type in your contact details.
  4. Save the file as a CSV
  5. Optionally, remove a blank line
  6. Upload to SOTADATA
  7. Check for errors reported on the response page.  If none the job is done.
  8. Fix any errors reported and resubmit the file to sotadata.

Get the template

First download the Excel template found on the sota_Australia group at https://au.groups.yahoo.com/neo/groups/sota_australia/files/Tools/ which is the Tools subfolder of the Files folder.    The Excel template is named Log_template_V2.xltx

This is not an excel spreadsheet, but a template for a spreadsheet.  Templates are used throughout Microsoft Office (Word, Excel, Powerpoint) to define a template for a document, spreadsheet or  presentation, allowing you to create a new document/spreadsheet looking exactly like the template, without risking any changes to the template.

To use the template it is simplest if you save it in your templates directory.  Where that is depends on your operating system version.  For windows 7 it is in C:\users\<your_username>\AppData\Roaming\Microsoft\Templates

Once you have saved the template in whatever directory or folder your templates are in, it will be available for you to use for any new document/spreadsheet you create.

Enter contact details into the spreadsheet

Create your new spreadsheet.  Use menu path File > New> then select your templates and from those available select Log_Template_V2.

template_selection

After you select OK Excel presents the template contents ready to be edited by you.

excel_log_template

Now you overtype the template entries with the ones from your log.  Here is a sample of what your excel page will look like.  Note that the sample line of the template has been removed.  You can optionally leave it there until you are ready to save the file, to remind you of what each column should contain, but don’t forget to remove it as sotadata will reject it.  Note that times are entered as 4 digit numbers like HHMM in 24 hour UTC time format.

Formats: all data is typed in Text format, no date formats or numeric formats, no semicolons in times.  Just plain text.  You will see the template sets up all the columns as text columns, this prevents Excel being clever and helping a bit too much.

sota_log_sample

Once you are finished entering your data, using copy and paste for the repeated data like your callsign, location, Version number, possibly band and mode data too, you can then save the file as a CSV file.  No need or point in saving it as an excel file.

Save file in CSV format

Use menu option File > Save As, then select file type CSV (comma separated values).

Select your directory and enter the file name.  I have a directory called SOTALOG and I use a file naming convention for these files:  the date in YYYYMMDD format.  This automatically sorts the files in date order.

At this point you can close the file in Excel.  You will get some prompts from Excel asking whether you want to leave the file in this CSV format?  Answer yes.  Do you want to save the changes?  (meaning save as excel format?)  answer No.

Remove surplus line

This is optional as SOTADATA accepts a blank final line.  But if you want to use the CSV files for another purpose, say converting them to an ADIF file, you don’t want a blank line at the end of the file.

Edit the file using Notepad or any other plain text editor. Remove the blank line at the end of the file.  I use Notepad++.

vk2uhlog

Delete the extra line (line 5 in the example above), then save the file and exit the text editor.

Upload to SOTADATA

You can now go to sotadata, log on and submit your file as either an activator’s CSV file or a chaser’s CSV file, or both.

Errors Found?

Your contacts must be in date and time order or sotadata will reject them.  Dates must be in dd/mm/yyyy format.  Summit codes must be valid.  Callsigns must be in valid format.   If there are errors in your data, the upload will fail and you will see an error page with errors highlighted.

You can normally fix those errors using Notepad because they are usually minor typos.

Summit codes need to be verified by checking them against your original log, possibly looking at sotawatch to see what summit code was reported by the activator or chasers.  It is possible for spots to be mistyped so look out for those possibilities too.

As the file format CSV uses commas as a field separator, you cannot use commas in any field without confusing the upload program.  The only one where you might consider using commas is the comments field.  Don’t use commas, it’s that simple.

After correcting the errors, just go back to sotadata and resubmit the CSV file.

Caution: re-opening CSV with Excel

If you reopen the CSV with Excel, it will be too clever and will convert the times you typed with leading zeros into plain numbers.  For example a time like 0005 for 5 minutes past UTC midnight would be converted into the number 5 by removing the leading zeros.  If you save that file as a CSV, your times will now not be accepted by SOTADATA because they are no longer 4 character times.   I find it simpler to correct errors in the CSV using Notepad or Notepad++.

Summary

The description above explains how to use the SOTA log template and explains why the data has been formatted in the selected way.  For SOTA users uncertain of Excel, I hope this helps.

Footnote:

The program  SOTA_CSV_editor by G0LGS does a nice job as a SOTA logger and exports a file in a format accepted by SOTADATA. It has summit code checking too, which helps avoid errors.

VHF/UHF Field day log pages and checklists

These two files may be of use to other entrants in the VHF/UHF contests.

The first is an empty log page which you can simply print on your own printer.  These are for handwritten logs.

The second is a spreadsheet template for the Australian VHF/UHF field days.  The first worksheet is the summary sheet and worksheets for each band follow.  There are only a couple of formulas and they are in the scoring table.

The template, if opened in either Excel or OpenOffice will create a new spreadsheet.  When you save it will be to a spreadsheet file.  The template will be unchanged so it can be reused.

Logging for field events – paper logs and spreadsheets

I use spreadsheets to record my field day log.  I don’t need to use a computer in the field – I work with computers all day and the last thing I want on a field day is yet another day of working a computer.  A paper log is quite good enough, and avoids odd things happening due to incorrect program logic, or wrong callsign recording.

In the John Moyle Memorial field day rules you need to record the grid locators of stations worked on vhf/uhf. Then you need a column for distance, then you need to work out the points earned by that contact.

I use Tiny Locator, a nice little application from ON6MU, to calculate distances from grid squares.  There are spreadsheet add-ins available for Excel, but I decided to just use an external tool and copy/paste its results into my spreadsheet. By doing the calculations only once for each callsign, the process is cut down to the minimum effort required.

I copy all the callsigns worked, with their locator codes as recorded during the contest, into a new worksheet called Grids.  Then I sort by callsign and remove the duplicates.  Then I do all the distance calculations with the help of Tiny Logger and record the distance for each station worked.

Back in the log page itself I then insert formulas into the Grid locator, distance and points earned cells of each row.

The grid locator can either be the original locator recorded during the contest, or the data copied from the Grids worksheet for that callsign.  Similarly for the distance and points data.

The formula to copy the grid locator for any callsign in the Grids sheet is =VLOOKUP($E2;$GRIDS.$A$1:$C$28;2) for OpenOffice sheet users, or =VLOOKUP($E2,$GRIDS!$A$1:$C$28,2) for Excel.

Similarly you can get the distance using the next column of the Grids sheet.

The points are calculated using a similar technique.  The score table is set up in another sheet, in the following format.

0 2
49.9 5
99.9 10
149.9 20
299.9 30
9999 50

The formula to look up the points for a distance is: =VLOOKUP(K2;$Scoretable.$A$1:$B$6;2) for Openoffice.org Spreadsheets.
Here $K2 is the cell containing the distance for the contact made. Scoretable is the sheet containing the table above in cells A1 to B6. Column 2 in the scoretable is the column containing the points to be scored. VLOOKUP returns the row containing the distance that is equal to the specified search value.  If that exact value cannot be found in column 1 of the table, it matches against the first row found that is greater than the value sought.  Thus a distance of 80 is greater than 49.9 but less than 99.9, so the row chosen is the one with a distance of 99.9, allocating 5 points to the contact.

You could continue to refine the formulas and the fine details, such as dealing with decimal points of distance correctly.  eg. what is the score for a distance of 99.999 km?  The simple table and formulas used would give the wrong result and strictly speaking the scoring table specified in the rules does not state the score for distances between 49 and 50 km, etc.  However as a way of saving typing and avoiding keying errors in distances, this approach works well enough.  Rounding the distances appropriately is also something to consider.

Summary

Using a combination of plain spreadsheets and formulas, you can ensure the grid locator, distance and point score for each contact is consistently recorded in your log sheet.