As I fully expected, my post about Logging Satellite Contacts to LOTW generated a fair number of questions and got some interest. In particular, how I log EVERYTHING into the different logging systems came up a couple times. So strap in, here we go...
As said before, I operate primarily Satellites, with some portable terrestrial mixed in, and I also activate Summits on the Air. I use LOTW as my primary QSL path, and I use the QRZ logbook for an online logbook database. LOTW and QRZ both accept ADIF uploads, though they are slightly different in how they do it. I also have to upload my SOTA activations to the SOTA database to get credit there. What this comes out too is there are 5 different types of files I typically have to generate if I've had an all band SOTA activation weekend. That's a ton of different data to work with, but it's doable. I'm also a spreadsheet fiend - in my day job tons and tons of data is moved about via spreadsheets, and they lend themselves to script friendly formats (like CSV - comma separated value) well. So the idea I had was take a spreadsheet, be able to toss in the minimal amount of information required about my contacts, and then have a script generate everything based on that input. Challenging, but not impossible.
So, here's how it works, I've got 3 main files that do the work. The first is the Logger Spreadsheet. Looks something like this:
As you can see, there's a little bit of everything going on here. In places where you make a satellite contact, just mark which Satellite it was. The script will fill the mode/frequency and special propagation values for LOTW and QRZ. Where you make a SOTA activation, it will automatically create a SOTA file as well for upload to the database. Anything that isn't a Sat contact, will be considered an 'HF' contact (even if it's something like a contact over 2 meter simplex) and a different file will be made for QRZ/LOTW upload there. Note: You have to either put in a bird name, or fill out the frequencies... if you do both - the script will overwrite your submitted frequency values, and if you do neither the script will error out. If you put in a bird name the script doesn't recognize (more on that in a second) it will also error out. If in doubt, look at the examples in the picture above, which is also the sample file that I included with the download.
So, where does the bird data come from? Glad you asked! There's a special Text file that contains all that in (drumroll please) CSV formatted reference file name appropriate sat-data.txt. It looks something like this:
Every bird that I might make a QSO on, I keep listed in this file. The breakdown should be easy enough to understand, but in case it isn't.. Here's what each field means.
Cool? Good. When new birds got chucked into orbit, instead of having to piss with the entire script, just add a new line entry here and you're good to go. Try and make sure the name is in the same format as LOTW accepts though, cause if it isn't exactly right, it will error out when you try to get TQSL to upload it, which is something that has caught me before.
The final file in the bundle is the Perl Script itself. There's really nothing here you need to mess with.. just make sure it's in the same directory (or folder) as your sat-data.txt and your "Logger-Spreadsheet.csv" and you're good to go. One caveat to the "nothing to mess with rule" is that if you want to be fancy and set your own home grid, or change the names of some of the variables, at the very top of the script you can do just that. Fire up your favorite text editor, and the open 25 or so lines of the perl script look like this:
Home Grid, Input Spreadsheet Name, and Sat Data file name are basically the only variable's that should really matter. Just keep em the way you want, and you're good to go. Don't mess with any of the code below the "DON'T MESS WITH ANY OF THE CODE BELOW THIS" banner. That is unless you want to. It's open source. Have at it.
Once the script is ran, in the directory where you launched it you'll get any number of 5 different files. They're named pretty self-explanatory, but for verbosity I'll cover em all.
satqrzimport.adi = Upload this to QRZ for you Satellite Contacts
satlotwimport.adi = Upload This to LOTW for your Satellite Contacts
hfqrzimport.adi = Upload this to QRZ for HF
hflotwimport.adi = Upload this to LOTW for HF
sotaimport.csv = Upload this to the SOTA database
Each time you run the script it will delete these files (if they're in the directory where it's being run) and build them from scratch, if they are needed. You can keep uploading the same filenames over and over again to all the respective systems (LOTW, QRZ, SOTA) and they don't care.. it's all about what have you done lately with them.
So still Interested? Download the files Here and make your logging life lots simpler.
Anything else that might come up, hit me up on Twitter @KG5CCI.