Add US States to existing records

3 posts / 0 new
Last post
km6cg
Add US States to existing records

I am using 2.4.0 as packaged by Fedora 31.

I have a few logbook databases that were accumulated before I configured callbook support. These do not have a state filled into the state field. Is there any way to re-tag them now that I have callbook support enabled?

We're probably talking under 100 contacts, but more than 50.

Thanks,
Ed Greenberg
AE2Z

oh1kh
Add US States to existing records

Hi Ed!

Not directly as edit of qsos does not re-fetch data from qrz/hamqth.

But because qsos are in mysql database everything (almost) is possible using mysql commands.
How ever there are not ready made instructions to do that.

What I have in mind is that in my alpha test version https://github.com/OH1KH/cqrlog/tree/loc_testing/compiled
It has state info built in to wsjtx-monitor. It fetches data from FCC and creates callsing=state list to ctyfiles folder (.config/cqrlog/ctyfiles/fcc_states.tab)

Modifying it with awk at command console could produce a set of mysql commands that would set state to every US calls in log.

.config/cqrlog/ctyfiles/fcc_states.tab includes:

call=state
AA0A=MO
AA0AA=NE
AA0AB=CO
AA0AC=MN
AA0AD=MN
AA0AE=MO
AA0AF=CO
AA0AG=SD
AA0AH=MO

etc...

Produce commands with awk at command command console:

cat ~/.config/cqrlog/ctyfiles/fcc_states.tab | awk -F"=" '{print "update cqrlog_main set state=\x22"$2"\x22 where callsign=\x22"$1"\x22"}' > /tmp/mycmds.sql

Will make file /tmp/mycmds.sql to have commands:

update cqrlog_main set state="state" where callsign="call"
update cqrlog_main set state="MO" where callsign="AA0A"
update cqrlog_main set state="NE" where callsign="AA0AA"
update cqrlog_main set state="CO" where callsign="AA0AB"
update cqrlog_main set state="MN" where callsign="AA0AC"

...etc ...
where 1st line is bogus but others work.
then just open cqrlog so that database engine will start.
Then type command:
mysql --socket=/home/saku/.config/cqrlog/database/sock cqrlog001 < /tmp/mycmds.sql

Replace "saku" with your username and "001" with your log number in use with this faulty log.

NOTE!
This is untested! But something like that could do it.

--
Saku
OH1KH

oh1kh
Add US States to existing records

HI!

After a while of thinking you maybe do not want to change all US qsos on log.
As the FCC data holds the latest state information a callsign may lived in different state previously.
Then it is not good to mix up whole log.

In that case you may limit the change to begin from certain date by fixing the end of awk command line from:
callsign=\x22"$1"\x22"}'
to:
callsign=\x22"$1"\x22 and qsodate > \x22YYYY-MM-DD\x22"}'

Then change happens only if date is bigger than stated by replacing needed date to YYYY-MM-DD

--
Saku
OH1KH