How to merge new ADIF fields into current Log [new DOK feature]

7 posts / 0 new
Last post
DL3LAR
How to merge new ADIF fields into current Log [new DOK feature]

Hi,
just upgraded to cqrlog 2.5.0 (Kubuntu 20.04 LTS) today - it works fine. Thanks for many new features, especially DOK field.

As there is no database or service from DARC available, where one could look up DOK information, I'm looking for a way to add it to my QSOs in my log.
I'm using DCL (DARC Community Logbook) for application of some awards, e.g. WAE or DLD. My QSO's got uploaded to DCL through LoTW, contest logs and ADIF file upload.

From DCL, I made an export to an ADIF and got these tags:


<"CALL:5">DM5EE<"BAND:3">10m<"MODE:3">SSB<"QSO_DATE:8">20180114<"TIME_ON:4">1012<"RST_SENT:3">599<"RST_RCVD:3">599<"DXCC:3">230<"DARC_DOK:3">H46<"APP_DCL_STATUS:1">c<"EOR">

[remark: this editor hides somehow ADIF tags, so I manually added 2x " to show the labels. They are not in the original ADIF export]

All of those QSOs are already in my cqrlog Database, so my question is: is there a way to merge the additional ADIF tag "DARC_DOK" with my current Database ? I think, just importing adds new log entries ...

CALL & TIME_ON should be good indicators to find the correct log entry. There are more than 1.200 QSOs with DOKs, so I'm looking for a automated/semi-automated way, to add this content.
Is there a way how to do this in cqrlog or it's SQL console ?

73 de Rolf, DL3LAR

oh1kh
How to merge new ADIF fields into current Log [new DOK feature]

Hi Rolf!

If you can produce TIME_ON ,CALL,DOK list text file you can use it to generate sql command file.

I have test log with fake qsos:

+---------+----------+------+
| time_on | callsign | dok |
+---------+----------+------+
| 09:02 | TE1ST | |
| 17:09 | KK8MM | |

I created text file about time_on,callsign,dok:
----------list.txt-------
09:02,TE1ST,A00
17:09,KK8MM,B01
------------------------
From that file I created sql command file in console with:
cat list.txt | awk -F"," '{print "update cqrlog_main set dok=\""$3"\" where (time_on=\""$1"\" and callsign=\""$2"\");"}' > dok.sql

Then file has:
-----------dok.sql------------
update cqrlog_main set dok="A00" where (time_on="09:02" and callsign="TE1ST");
update cqrlog_main set dok="B01" where (time_on="17:09" and callsign="KK8MM");
-------------------------------

Then I start cqrlog (Save data to local machine is checked and my log is number 4)
When cqrlog is runing (log 4 need not to be open) I do console command:
mysql --socket /home/saku/.config/cqrlog/database/sock cqrlog004 < dok.sql

After that my log 4 has qsos like:

+---------+----------+------+
| time_on | callsign | dok |
+---------+----------+------+
| 09:02 | TE1ST | A00 |
| 17:09 | KK8MM | B01 |

--
Saku
OH1KH

DL3LAR
How to merge new ADIF fields into current Log [new DOK feature]

Hi Saku,

thanks a lot for your solution. I've converted ADIF2CSV and had to do some reformatting of time & date strings.
For reformatting I used LibreOffice Calc, but some special DOK in my log needed extra attention: e.g. 25E37 or 40E20 ;-)

I was not sure, if time_on and call is clearly enough, so I included qsodate as well.

This is how my CSV-list dok3.csv looks like:

2020-05-03,09:48,DJ3AK,H24
2020-05-03,10:55,DL5SE,S54

And console command line for preparing the sql command file:

cat test_dok3.csv | awk -F"," '{print "update cqrlog_main set dok=\""$4"\" where (qsodate=\""$1"\" and time_on=\""$2"\" and callsign=\""$3"\");"}' > dok.sql

vy 73 de Rolf, DL3LAR

DL3LAR
How to merge new ADIF fields into current Log [new DOK feature]

Hi,
some additional information: I've found two Call History files with DOK information for DL calls.

DK9TN
http://dk9tn.de/downloads/WAG_call_history.txt

DL6ER
http://www.dl6er.de/DOK-List/

The latter one has validity time period infomation.
One could combine it with OK1KH's description to update existing logs ...

73 de Rolf, DL3LAR

oh1kh
How to merge new ADIF fields into current Log [new DOK feature]

Yesterday I played a little more with this subject.
I made a script that first picks up DL callsigns without dok (select qsodate,callsign,dok from cqrlog_main where (adif=230 and dok is NULL) )
When it is called from command line with "mysql -Bne" optons it gives directly comma separated text list that can be directed to file.

Then it modifies sdock.csv so that it has lines with startdate,enddate(in epoch unix format, secs since 1970) callsign and dok. if no start date it is 0. If not end date it is 9999999999

Then it takes one callsign from mysql list and greps it from modified sdok list. If found, converts callsign date to unix time and then compares if it is between sdok's start and end date. Epoch times are easy to compare with simple arithmetic rules.

That phase is still in progress. It must have exceptions because some calls appear several times with different times.

That finds out just special callsigns (that I think "sdok" means) .
Then another round has to be added with dl6der's dok list.

I do not know if I have enough interest to finish this... ;-[] But that way all past qsos could have updated DOK ok.

--
Saku
OH1KH

DL3LAR
How to merge new ADIF fields into current Log [new DOK feature]

DOK's ("Deutscher Ortskenner") are identifiying local chapters and associations of DARC members.
More than 1.000 DARC local chapters are organized in 24 districts (here's an overview map: https://www.darc.de/der-club/distrikte/)

Beside that, there are some special DOK's ("Sonder-DOK"), which are usually issued only for a period of time or for some special calls only.
Examples:
- CM19 ("Clubmeister 2019", club champion) - CMyy
- 25E37 25th anniversary of DOK E37 - yy usually 25, 40, 50, 60, 70
and so on .. sdok.csv in .config/cqrlog/dok_data gives an overview

Outside this time period, individual callsigns use regular DOK's .. so the best way is your approach with start- and enddate.

Maybe it would be more forward-looking, to integrate some lookup database, like DXCC or QSL-Manager.
Could you recommend a file structure format, maybe CSV, which could be integrated in cqrlog's architecture ?

vy 73 de Rolf, DL3LAR

oh1kh
How to merge new ADIF fields into current Log [new DOK feature]

Hi Rolf!

There is already updating folder ~/.config/cqrlog/dok_data (prefrences/Program to set update)

DOK project is done by Andy DL7OAP and Florian DF2ET. I have very little, if none, role in that property because I am not familiar with DOK.
So it is better to make conversation about cqrlog and DOK with them.

--
Saku
OH1KH