Cabrillo export while we wait for CQRTest

1 post / 0 new
Cabrillo export while we wait for CQRTest

If you're like me, you use CQRLog daily and while you might enter a contest you're not usually getting into the thousands of contacts.  If you are, you should be using a real contest logger.
However you can use CQRLog relatively efficiently if you have hamlib set up to get frequency information and especially if you're doing a "search and pounce" strategy where you have time to set up the fields before you make the QSO.
A one step export to Cabrillo is not possible, but you can export all of the QSO's in one step.  Only one sed command (scroll all the way to the bottom!) required.
As an example, the recent ARRL 10 meter contest exchange was, for amateurs in Canada, Mexico or the US:  signal report and State/Province.  For "DX" amateurs the exchange was signal report and sequence number.
Both exchanges can be generated with SQL syntax.

1) Turn off QRZ or HamQTH lookups.  This is probably cheating unless you're entering assisted, and it may mess up your "remarks" field which we will need.
2) Use the "comment to QSO" field to enter the incoming exchange only.  You can use two strategies:
a) enter the exchange (state or sequence number) separated by spaces in "comment to QSO."  If you do this, YOU MUST LEAVE THE STATE BLANK.  This method is fastest.
b) enter the RST in the proper box, and the State/Province in the proper box.  This would only work for stations in Canda, Mexico or the US and is the slowest form.

At the end of the contest you can export your contacts to the QSO: section of a cabrillo export with the following SQL commands:
For an amateur in Canada/Mexico/US:

SELECT "QSO:", LPAD(round(freq * 1000),5,0) AS Freq, IF(mode='SSB',"PH","CW") AS mode, qsodate AS QSODate, REPLACE(time_off,":","") AS UTC, "W1AW" AS MyCall, rst_s AS RSTOut, "CT" AS ExchangeOut, callsign, rst_r AS RSTIn, IF(state IS NULL,remarks,state)AS ExchangeIn FROM view_cqrlog_main_by_qsodate WHERE ( ( qsodate = '2013-12-14' OR qsodate = '2013-12-15') AND ( mode = 'SSB' OR mode = 'CW') ) ORDER BY qsodate, time_off

Breaking out the SQL command:
"QSO:" returns exactly that string, required for each QSO in a Cabrillo log.
LPAD(round(freq * 1000),5,0) returns the frequency.  CQRLog stores the frequency in megahertz, while Cabrillo requires the frequency be logged in hertz.  Therefore we multiply by 1000, and then cut off any remaining decimals. (There shouldn't be any, but just in case).  LPAD pads the value with any zeroes that might be required (required for contacts on 160, 80 or 40m bands). NOTE: If you use this query for VHF/UHF/Microwave, adjust the "5".  You will need six digits for 144MHz - 902MHz, seven digits for 1.2GHz to 5.6GHz, and eight digits for 10GHz and up.
The first IF statement checks the value of mode.  If it is SSB, then it writes out PH for phone.  Otherwise it writes out CW. Note that this statement works for the ARRL 10 meter contest, as you can work SSB or CW or both.  This statement has to be changed if you are working a RTTY or data contest.
qsodate is simply printed out as is.
Cabrillo format time uses just four digits, without the colon used by MySQL.  So we use REPLACE(time_off,":","") to replace the colon with nothing, leaving just the four digits of the time.
Replace W1AW with your callsign, in quotes.
If you set your default RST out value to be 59 or 599, this should fill out automatically for you when you move to a new QSO.
For amateurs in Canada, Mexico or the US replace CT with your state or province.  For DX stations, see below.
callsign prints out the worked station's callsign without modification.
rst_r prints out the worked stations rst without modification.  For contests, this is almost always "59" or "599" so you can just leave the default.
The second IF statement does some interesting things.  If the state has a value, it will print that value.  If the state is null, it will print that value.  For example if you work a station from Colorado and enter CO in the state box, it will print CO.  If you work a station from Colorado and enter nothing in the state box, but you type CO in the comment box, it will print the value from the comment box.  Obviously if you work a "DX" station and get a sequence number, enter the sequence number in the comment box.  Do not put anything else in the sequence box, or it will be added to the QSO entry! 
(This is why is is so important to not press the F11 key with QRZ or HamQTH integration.  CQRLog will populate the comment box with QSL information, which you do not want to edit manually.  It can also mess up a log entry for stations that show one state in the QRZ database, but are operating portable for a contest and report another state for that contest.  There was at least one station in the 2013 10 meter contest doing exactly that - home was Mississippi, but he was reporting Louisiana for the contest.)
The FROM statement simply lists the main table used by CQRLog.
The WHILE clause requires you to edit the date range for the contest, and the modes.  I have used a pair of OR statements here because most contests are only one or two days long, and you are only using one or two modes.  There is no provision in the WHERE clause for you to take a break from a contest.  Using our example of the ARRL 10m contest you may have SSB or CW contacts on other bands at night, when the 10m band is dead.  In this case you probably want to add an additional statement such as AND freq BETWEEN '28.000' AND '29.000'
For a DX station, you cannot use your state/province for your outbound exchange.  You use a sequence number that increments with each QSO starting with "1".  Unfortunately, while the SQL query is relatively simple to achieve this, there is no method to automatically fill out your numbers in the New QSO window.  So for this first version you have to be very certain you do not make a mistake.  If you skip a sequence number on air (e.g. report 001, 002, 003, 005, 006, 007) you may end up with an off-by-one error in your log.  Use this query with caution!!
Here is the modified query:

SELECT "QSO:", LPAD(round(freq * 1000),5,0) AS Freq, IF(mode='SSB',"PH","CW") AS mode, qsodate AS QSODate, REPLACE(time_off,":","") AS UTC, "JA1RL" AS MyCall, rst_s AS RSTOut, ( @qso:=@qso+1 ) AS ExchangeOut, callsign, rst_r AS RSTIn, IF(state IS NULL,remarks,state)AS ExchangeIn FROM view_cqrlog_main_by_qsodate, ( SELECT @qso:=0) AS qso  WHERE ( ( qsodate = '2013-12-14' OR qsodate = '2013-12-15') AND ( mode = 'SSB' OR mode = 'CW') ) ORDER BY qsodate, time_off

There are two important changes:

1) Instead of simply typing in a text value for a state or province, we calculate the sequence number: 
( @qso:=@qso+1 ) generates a number, starting with 1 using a temporary table.
2) The FROM statement is also modified by creating a temporary table with the sole purpose of setting the qsl variable to 0.  (MySQL users will see that you could also do this with a SET statement, but CQRLog won't let you run a SET statement before the SELECT statement.):
( SELECT @qso:=0) AS qso

Otherwise, all remains the same.
A safety measure to help DX stations avoid an off-by-one error in the generated log would be to enter your sequence number and the worked station in the comment box, and then modify the exchange out and exchange in.  You must be sure to always use the same syntax in your comment box though.
For example, you could do this:

1) Do away with the temporary table
2) Enter "your sequence number" "one space" "the worked station exchange" in the comment box. (e.g. '2014 AK' or '2015 199')
3) Change the ExchangeOut value to

   SUBSTRING_INDEX(remarks,' ','1') AS ExchangeOut

     This selects everything in the remarks field to the left of the leftmost space.
4) Change the ExchangeIn value to

   SUBSTRING_INDEX(remarks,' ','-1') AS ExchangeIn

     This selects everything in the remarks field to the right of the leftmost space.

Again, ensure that nothing extra gets into your "comments about this QSO" box! More than one space in the comments box will ruin your results!
The resulting SQL query then becomes:

SELECT "QSO:", LPAD(round(freq * 1000),5,0) AS Freq, IF(mode='SSB',"PH","CW") AS mode, qsodate AS QSODate, REPLACE(time_off,":","") AS UTC, "JA1RL" AS MyCall, rst_s AS RSTOut, SUBSTRING_INDEX(remarks,' ','1') AS ExchangeOut, callsign, rst_r AS RSTIn, SUBSTRING_INDEX(remarks,' ','-1') AS ExchangeIn FROM view_cqrlog_main_by_qsodate  WHERE ( ( qsodate = '2013-12-14' OR qsodate = '2013-12-15') AND ( mode = 'SSB' OR mode = 'CW') ) ORDER BY qsodate, time_off

Once you have your SQL query tailored and run, you can export the results to a csv file.  CQRLog will create a semi-colon delimited file of QSO: lines that most contest robots cannot parse. My output looks like this:
QSO:;Freq;mode;QSODate;UTC;MyCall;RSTOut;ExchangeOut;callsign;RSTIn;ExchangeIn; QSO:;28312;PH;2013-12-14;1901;KL2ZZ;59;AK;W6AFA;59;CA; QSO:;28301;PH;2013-12-14;2020;KL2ZZ;59;AK;WH7Z;59;HI; QSO:;28434;PH;2013-12-14;2043;KL2ZZ;59;AK;VK2GGC;59;598; QSO:;28406;PH;2013-12-14;2048;KL2ZZ;59;AK;KE6QR;59;CA;
At that point, find your log and issue one sed command to replace the semicolons with tabs:
sed -e 's/;/\t/g' example.csv > output.csv
After this command, your output.csv file should look like this:
QSO: Freq mode QSODate UTC MyCall RSTOut ExchangeOut callsign RSTIn ExchangeIn
QSO: 28312 PH 2013-12-14 1901 KL2ZZ 59 AK W6AFA 59 CA
QSO: 28301 PH 2013-12-14 2020 KL2ZZ 59 AK WH7Z 59 HI
QSO: 28434 PH 2013-12-14 2043 KL2ZZ 59 AK VK2GGC 59 598
QSO: 28406 PH 2013-12-14 2048 KL2ZZ 59 AK KE6QR 59 CA
(Note that your web browser will probably turn the tabs into spaces)
General notes:
While these examples are tailored for the ARRL 10 meter contest, it should not be difficult for the reader to modify the queries for whichever contest you are logging.  The third SQL query in particular allows for arbitrary exchanges as long as there is only one space, and you can easily modify the SUBSTRING_INDEX if you need to have spaces.
Also left to the reader is adding the Cabrillo header for your particular contest to the top of the resulting text file, and the all important "END-OF-LOG:" to the end.