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.

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

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.

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++.

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.