[DXBase] LoTW .adi QSL download

[Wes (N7WS) and Linda]

At 06:53 AM 1/19/2004 -0600, FireBrick wrote:
>Stuart.
>I'm answering via the reflector in case I misstate the technique, someone
will correct me.
>I haven't done this myself.
>But
>Create a new database using the default .mdb
>Import the lotwreport.adi into that log.
>Then import your real log into the new one you just created.
>
>Of course you will then have to change the .mdb name to the one you
normally use.
>
>Remember, always make a backup copy of your good log .mdb prior to ANY
type of log
>manipulations.



Here is an excerpt from an email I sent about a month ago to Don Hill,
AA5AU, who posed a similar question:

"At 12:55 AM 12/28/2003 -0600, you wrote:
>I've obtained my certificate and I know how to create the ADIF file with
DXbase
>to upload the LoTW site.
>
>During the beta testing period, I uploaded over 50k QSO's and had several
>thousand QSL's.  I think I understand that if I were to upload all these
QSO's
>again and receive all these QSL's that I would have to manually enter the
QSL's
>received in DXbase. Is this true?

Yes.
>
>I also think I understand the ARRL has created an ADIF reporting file that
can
>be downloaded with the QSL's received information to be used for logging
>programs (such as DXbase) so this manual entry is not required.  Is that
true?

Sort of.  The problem is that if you try to import the .adi file from ARRL,
the import program will check certain parameters (I don't which) and decide
that the records are all dupes and it will not import them.

What I did, and this is slightly (highly?) convoluted so you won't want to
do it on a regular basis, is this:

1)  Backup your log.mdb file someplace safe.

2)  Create a new empty database (dummy.mdb for example) and import the .adi
into it using the non-dxb import program.  

If you use DXBase to open this new database you will find that all of the
records will show CFM = "Card" and QSLS = "No".  This of course is
incorrect.  ***(Update:  Part of this was my problem, see below)***

3)  I used MS Access 2000 to open this database and fix some of the
problem. If you have it, follow along.  Leave DXBase open but minimize it,
open Access and open the .mdb.  Access will tell you that you can't change
this file, but it lies.  (If you have Access 97, I think you can do it
directly)

4)  Double click the QSO-TAB to open it.  Find the column "CFM" and click
on the header to select the entire column.  Press Ctrl+H and type "1"
(without the quotes) in the Find What box.  Press TAB and type "2" in the
Replace With box.  Press Alt+A and hit Enter when the warning box opens.
You will see that the "CFM" field is now populated with "2" in each cell.

If you Alt+Tab back to DXBase, you will find that the "CFM" fields now show
"LoTW".

5)  Alt+Tab back to the Access QSO-TAB.  Close the Find and Replace pane
and select the "QSLS" column. Press Ctrl+H and type "0" (without the
quotes) in the Find What box.  Press TAB and type "2" in the Replace With
box.  Press Alt+A and hit Enter when the warning box opens.  You will see
that the "QSLS" field is now populated with "2" in each cell.

If you Alt+Tab back to DXBase, you will find that the "QSLS" fields now
show "LoTW".

6)  At this point you have a log that contains only QSOs confirmed by LoTW.
 Initialize tables to store the changes, close everything and then do a DXB
import of your regular log into this new log.  You will get an error for
every entry that already exists, i.e. the LoTW confirmed QSOs.  The count
of records not loaded should equal the count of record imported from the
.adi file.

7)  When the import completes, open the dummy.mdb in DXBase and initialize
tables.
 
When I generated my .adi submission, I failed to set the option to have DXB
populate the QSLS record.  I recovered by the following:

8)  Perform step 3 again. Double click the QSO-TAB to open it.  Find the
column "QSLS" and click on the header to select the entire column.  Press
Ctrl+H and type "0" (without the quotes) in the Find What box.  Press TAB
and type "2" in the Replace With box.  Press Alt+A and hit Enter when the
warning box opens.  This updates all of the QSLS from "No" to "LoTW"

Then type "1" in the Find What box.  Press TAB and type "3" in the Replace
With box.  Press Alt+A and hit Enter when the warning box opens.  This
updates all of the QSLS from "Card" to "Both."  Close Access.

Alt+Tab back to DXBase and initialize tables.  Now you have a complete log
with all of the QSL sent data correct.  Where you have *only* an LoTW
confirmation or *only* a card, that is also correct.  What isn't correct
are the cases where you have both a card and an LoTW confirmation.

At this point, I'm out of ideas about how to do this semi-automatically and
I think you'll have to resort to manually fixing this, if you care.  

Of course, if you don't have MS Access, I dunno what to say.

Wes  N7WS

>