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>AppDataRoamingMicrosoftTemplates

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.

Mt Majura SOTA activation on 13 April 14

I was late arriving at the summit of Mt Majura partly due to forgetting what a long walk it is.  For some reason I had 30 mins in my mind but obviously I was mixing it up with other summits in vk1 because it took more like 50 mins this time.

I made most of the contacts on 40m plus a few on 20 and 12m.

There were quite a number of family groups, walkers, runners and cyclists on the summit during the 2 hours I was there.  I wonder if one of them picked up my reading glasses without realising they were mine.

I was also puzzled by a comment from some walkers I passed on the way up.  They suggested I should have identification to prove I was doing something legal and legitimate.  Perhaps reflecting their past employment they seemed to think someone with a radio was rather suspicious.

I pointed out that mobile phones are radios and have access to anywhere in the world, with GPS and other functions that are far more powerful than a simple voice and morse radio.  We chatted amicably for a few minutes and departed in our separate directions afterwards.

16 contacts made.

Some photos taken on the return trip.

image001

image013

image002image003image006image007

 

 

Meeting old friends on the radio

Recently I heard a signal on the radio that brought back many memories of past contacts and and QSL cards and photos exchanged.  It was Shin JA1NUT, who I have worked a number of times over the past 40 or more years.  He is a fine CW operator and is often heard on the 40m band.

I called him and he turned his directional antenna southwards from the USA direction and beamed his signal my way.  A directional antenna (aka Beam) on that band can be a very large antenna, with elements from 12 to 20m long on a boom of perhaps 10m.  I do not know what his current beam is for that band, but his equipment and antennas are working very well.  His signal was very strong here.  Our contact was made at about 22wpm on CW.

We exchanged signal reports, I told him we had last contacted under my VK1DA callsign and prior to that, under my V85DA callsign.  He immediately mentioned that he had my QSL cards, one of which had a photo of me when I first got my licence, at age 16.  He said he had a birthday approaching in May, for a somewhat higher number, and I told him I would be the same age in June.  This was very heartening – I have always known his signal and operating methods and it is rather nice to know we are so close in age as well as sharing an interest in amateur radio and in operating on CW (morse code).  He also mentioned his blog, nuttycellist, which I have added to my blogroll here.  Some insightful and reflective posts on his blog – far more than my own, I think.  Also his command of English is superb.  Many complex subjects are discussed there and explained in great detail.  My Japanese capability by comparison is very weak, I doubt if I can count up to 10!

It is a great pleasure and very reassuring to make contact with a distant friend after so long, and find that you both recall your past contacts.

This social aspect of ham radio is something that is very special.

Extract our contact
Extract from our contact, written on the left side of my log book