Compare Two Files

[johnk1ese]

I would like a program that would compare two lists. 

The first list is a master list of member's radio callsign, member's name, and their member number.  It looks like this in part:
G2HKU,Ted,293
G8VG,Pete,331
SM5CCE,Kjell,477
LA5HE,Rag,494
G3JUL,Geoff,623
ZS1EL,Vidi,658

The second list is only the member's callsign like this -
G8VG
G3JUL
ZS1EL

I want to create a third list.  The item in the second list would be compared to the master list.  If it was found in the first list the member number would be replaced by the word "Worked" and saved to the third list.

The master list contains 3 items per line and can be as long as 1000 lines.  The second list contains one item and can be as long as 700 lines.
  
I am guessing the simplest way is to
  assign a variable to the first item in list two,
  search for that item in the first item of each line of the master file,
  if not found add that line to the third file "Master Revised" as is,
  if found change the third item in that line to "Worked" and save the line to the third file "Master Revised". 

At the end of the file assign the variable to the next item in list two and go through the file again until the end of list two.

That is also the least elegant way to do the job. Is there an easier way?  Should arrays be used?  Suggestions?

Thanks and 73,
John K1ESE

[Rod]

Just have one list. Hold it in an array$(1000,4). array$(1,1) is the first call sign array$(1,2) is first name, array$(1,3) is their number and array$(1,4) is "W" worked or "" not worked. Now just list the details when you find array$(n,4)="" ie not worked.

[tsh73]

That is also the least elegant way to do the job.

make it work - make it work right - make it fast
It might as well be fast enough already
If not - read in array, sort array, use binary search

I think

elegant way

lies outside of word of BASIC nearer to SQL
Like this
create table master(sig varchar, name varchar, num varchar);

insert into  master values('G2HKU','Ted','293');
insert into  master values('G8VG','Pete','331');
insert into  master values('SM5CCE','Kjell','477');
insert into  master values('LA5HE','Rag','494');
insert into  master values('G3JUL','Geoff','623');
insert into  master values('ZS1EL','Vidi','658');

--select * from master;

create table second(sig varchar);

insert into second values('G8VG');
insert into second values('G3JUL');
insert into second values('ZS1EL');

--select * from second;

select master.sig, master.name, 'Worked'
from master, second
where master.sig=second.sig
union all
select master.*
from master
where master.sig not in (select sig from second);

Then run
www.jdoodle.com/execute-sql-online/
produces
G8VG|Pete|Worked
G3JUL|Geoff|Worked
ZS1EL|Vidi|Worked
G2HKU|Ted|293
SM5CCE|Kjell|477
LA5HE|Rag|494


[Brandon Parker]

First, I would want to pull the data from the files into the program and manipulate them from there. File operations are very slow compared to memory operations.

Here is a little snippet showing what I think you want based on data already being in two arrays. I simply pushed the updated data back into the original array, but you could easily change that if you really needed to.

masterList$(0) = "G2HKU,Ted,293"
masterList$(1) = "G8VG,Pete,331"
masterList$(2) = "SM5CCE,Kjell,477"
masterList$(3) = "LA5HE,Rag,494"
masterList$(4) = "G3JUL,Geoff,623"
masterList$(5) = "ZS1EL,Vidi,658"


memberCallsign$(0) = "G8VG"
memberCallsign$(1) = "G3JUL"
memberCallsign$(2) = "ZS1EL"

'Pull the data into your program
'It is much faster than working directly with data in files in most instances

'If the data exists in an array then you can concatenate it using a delimiter
'If you are pulling it into a string already then this is not required
For i = 0 To 5
   masterListUpdated$ = masterListUpdated$;masterList$(i);"|"
Next i

'Print the initial string
Print masterListUpdated$

'Iterate over the array (or through the string - note that you will have to work this out unless you provide
'us with how you have the data stored in your program)
For i = 0 To 2
   'Find out if the call signed that worked is in the master list
   itemExists = Instr(masterListUpdated$, memberCallsign$(i))
   If itemExists Then
       'If it is in the master list then replace the ID with "Worked"
       start$ = Left$(masterListUpdated$, (itemExists - 1))
       record$ = Mid$(masterListUpdated$, itemExists, (Instr(Mid$(masterListUpdated$, itemExists), "|")))
       record$ = Word$(record$, 1, ",");",";Word$(record$, 2, ",");",Worked"
       masterListUpdated$ = start$;record$;Mid$(masterListUpdated$, Instr(masterListUpdated$, "|", itemExists))
       'Print out the updated master list after each iteration to show that we have updated it
       Print : Print masterListUpdated$
   End If
Next i

'Now let's get the new masterList items back into the masterList$() array
For i = 0 To 5
   'Retrieve the first item in the list up to the "|" which is our delimiter
   'You could push the data into a new array, but I cannot see why you would need to
   'unless you still need to do something with the old masterList$() values
   'I like to use Trim$() just in case some errant spaces/non-printables got in there
   masterList$(i) = Trim$(UpTo$(masterListUpdated$, "|"))
   'Remove the first item from the list since we have stored it into the array
   masterListUpdated$ = Trim$(After$(masterListUpdated$, "|"))
   'Print the item stored into the current element of the array
   Print masterList$(i)
   'Print the remaining list to be parsed so that we can see the first item has been removed
   Print masterListUpdated$
   Print
Next i

End


{:0)

Brandon Parker

[Brandon Parker]

And just for fun, let's make 1001 unique Call Signs and work with them.

UpperBound = 1000

Dim masterList$(UpperBound)

'Let's just make up some fake but appropriately sized data for testing
For i = 0 To UpperBound
   newCallSign$ = chr$(Int(rnd(1) * 26) + 65); _
                  chr$(Int(rnd(1) * 26) + 65); _
                  chr$(Int(rnd(1) * 26) + 65); _
                  chr$(Int(rnd(1) * 26) + 65)

   If Not(Instr(usedCallSigns$, newCallSign$;"|")) Then
       tempCallSigns$ = tempCallSigns$; _
                        newCallSign$; _
                        "|"

       masterList$(i) = newCallSign$; _
                        ","; _
                        chr$(Int(rnd(1) * 26) + 65); _
                        chr$(Int(rnd(1) * 26) + 65); _
                        chr$(Int(rnd(1) * 26) + 65); _
                        ","; _
                        Int(rnd(1) * 9); _
                        Int(rnd(1) * 9); _
                        Int(rnd(1) * 9)
   Else
       'We need to make a new one, reduce i by one and do over
       i = (i - 1)
   End If
Next i

'Print out the masterList$() elements we are going to work on
Print masterList$(1)
Print masterList$(223)
Print masterList$(556)
Print masterList$(988)

'Store those element's call signs to work with
memberCallsign$(0) = UpTo$(masterList$(1), ",")
memberCallsign$(1) = UpTo$(masterList$(223), ",")
memberCallsign$(2) = UpTo$(masterList$(556), ",")
memberCallsign$(3) = UpTo$(masterList$(988), ",")

'Pull the data into your program
'It is much faster than working directly with data in files in most instances

'If the data exists in an array then you can concatenate it using a delimiter
'If you are pulling it into a string already then this is not required
For i = 0 To UpperBound
   masterListUpdated$ = masterListUpdated$;masterList$(i);"|"
Next i

'Print the initial string
Print masterListUpdated$

'Iterate over the array (or through the string - note that you will have to work this out unless you provide
'us with how you have the data stored in your program)
For i = 0 To 3
   'Find out if the call signed that worked is in the master list
   itemExists = Instr(masterListUpdated$, memberCallsign$(i))
   If itemExists Then
       'If it is in the master list then replace the ID with "Worked"
       start$ = Left$(masterListUpdated$, (itemExists - 1))
       record$ = Mid$(masterListUpdated$, itemExists, (Instr(Mid$(masterListUpdated$, itemExists), "|")))
       record$ = Word$(record$, 1, ",");",";Word$(record$, 2, ",");",Worked"
       masterListUpdated$ = start$;record$;Mid$(masterListUpdated$, Instr(masterListUpdated$, "|", itemExists))
       'Print out the updated master list after each iteration to show that we have updated it
       Print : Print masterListUpdated$
   End If
Next i

Print masterListUpdated$

'Now let's get the new masterList items back into the masterList$() array
For i = 0 To UpperBound
   'Retrieve the first item in the list up to the "|" which is our delimiter
   'You could push the data into a new array, but I cannot see why you would need to
   'unless you still need to do something with the olde masterList$() values
   'I like to use Trim$() just in case some errant spaces/non-printables got in there
   masterList$(i) = Trim$(UpTo$(masterListUpdated$, "|"))
   'Remove the first item from the list since we have stored it into the array
   masterListUpdated$ = Trim$(After$(masterListUpdated$, "|"))
   'Print the item stored into the current element of the array
   Print i;" ";masterList$(i)
   'Print the remaining list to be parsed so that we can see the first item has been removed
   'Print masterListUpdated$
   'Print
Next i

End


{:0)

Brandon Parker

[Brandon Parker]

Also, you could use this code instead of the other code for replacing the data as well. It just works with slightly different commands...

Options...

   If itemExists Then
       'If it is in the master list then replace the ID with "Worked"
       masterListUpdated$ = UpTo$(masterListUpdated$, memberCallsign$(i));memberCallsign$(i);","; _
                            Word$(UpTo$(After$(masterListUpdated$, memberCallsign$(i);","), "|"), 1, ",");",Worked|"; _
                            After$(Mid$(masterListUpdated$, itemExists), "|")
       'Print out the updated master list after each iteration to show that we have updated it
       Print : Print masterListUpdated$
   End If


{:0)

Brandon Parker

[johnk1ese]

Thank you all for your outstanding help.

However, the answers are beyond my skill level. If I was a good student I would study your answers and learn. Alas, I am not.

Instead, I am just trying to find a simple utility to fix my monthly data rather than doing it by hand. I am going the not elegant route for now.

That said, I can still use some help if you have the patience.

I have two sample files of the data:
the Master file is -
G2HKU,Ted,293
G8VG,Pete,331
SM5CCE,Kjell,477
LA5HE,Rag,494
G3JUL,Geoff,623
ZS1EL,Vidi,658
G3LKZ,Owen,671
K2TT,Bill,675
PA0BW,Hein,676
N9SW,Gene,698

The Worked file is -
SM5CCE
K2TT
PA0BW

My code is -

open "C:\FOC Lists\FOC Worked SAMPLE.txt" for input as #Worked

[main]
if eof(#Worked) < 0 then [skip]
input #Worked, worked$
print worked$

open "C:\FOC Lists\FOC Master SAMPLE.txt" for input as #Master
open "C:\FOC Lists\FOC Revised SAMPLE.txt" for output as #Revised

[agn]
if eof(#Master) < 0 then [skipIt]
input #Master, callsign$
input #Master, memname$
input #Master, memnumber$
if callsign$ = worked$ then
memnumber$ = "Worked"
print #Revised, callsign$+ ","+ memname$+ ","+ memnumber$
'memnumber1$ = ""
else
'if memnumber$
print #Revised, callsign$+ ","+ memname$+ ","+ memnumber$
end if
if eof(#Master) = 0 then [agn]
[skipIt]

close #Master
close #Revised

if eof(#Worked) = 0 then [main]
[skip]
close #Worked
print
print "Done"

end

The problem is that the Revised output file only shows "Worked" for the final callsign. Here's the output file

G2HKU,Ted,293
G8VG,Pete,331
SM5CCE,Kjell,477
LA5HE,Rag,494
G3JUL,Geoff,623
ZS1EL,Vidi,658
G3LKZ,Owen,671
K2TT,Bill,675
PA0BW,Hein,Worked
N9SW,Gene,698

What have I screwed up?

Thanks,
John K1ESE

[tsh73]

What have I screwed up?

Well, now you open worked sample file once
and for each line you open - read through - close master file.
And you open Revised file with master and close it with master.
Now, then you open revised file for output, it starts writing anew
So that's why you have as if only last line was in worked file.

Now, if you do open Revised file once, with Worked sample
then for each line of Worked you will have every lines of master, one of them marked Worked
So now you will have 3x lines. Obviously that's not what you want.

I suggest changing order

open master file once
* for each line, open - search - close working file
** If found, add line with "Worked"
** else add line as it is.

This way, you will save same amount of lines as Master file.

---

EDIT
I have working (?) code and it takes 13 seconds on my old machine for 1000 master and 300 worked.
For now, I will not spoil you fun and post it here - unless you ask.

[Rod]

My solution.

open "worked.txt" for input as #Worked
open "revised.txt" for output as #Revised

while eof(#Worked)=0
   input #Worked, worked$
   open "master.txt" for input as #Master
   while eof(#Master)=0
       input #Master, callsign$
       input #Master, memname$
       input #Master, memnumber$
       if callsign$=worked$ then
           #Revised, callsign$+ ","+ memname$+ ",Worked"
           close #Master
           exit while
       end if
   wend
wend
close #Revised
close #Worked

open "revised.txt" for input as #Revised
while eof(#Revised)=0
   line input #Revised , d$
   print d$
wend
close #Revised

end


[johnk1ese]

Thanks for the help, guys.

Rod, the code only gives me the 'worked' list when I run it here. I need both worked and unworked.

tsh73, yes, I need the same number of lines as the original master file. But if I close the working file each time, how do I keep from searching for the first entry in that file every time it is opened? I need to search the first item, then the next, then the next until the file comes to the end.

73,
John K1ESE

[tsh73]

But if I close the working file each time, how do I keep from searching for the first entry in that file every time it is opened? I need to search the first item, then the next, then the next until the file comes to the end.

I think you really should change search order.
Here I open master file - keep it open - and for each line search through worked file.
we need to know is master file line in worked or not, that's all

See if this result suits you.

goto [skipDataForSpeedTest]
open "master.txt" for output as #1
for i = 1 to 1000
   print #1, "item";i;",name";i;",";i
next
close #1

open "worked.txt" for output  as #2
for i = 1 to 300
   print #2, "item";i
next
close #2
[skipDataForSpeedTest]

t0=time$("ms")

'open "master.txt"  for input as #1
open "C:\FOC Lists\FOC Master SAMPLE.txt" for input as #1
'open "out.txt" for output as #2
open "C:\FOC Lists\FOC Revised SAMPLE.txt" for output as #2
   while not(eof(#1))
       input #1, a1$, a2$, a3$
        found = 0
       'open "worked.txt" for input as #3
       open "C:\FOC Lists\FOC Worked SAMPLE.txt" for input as #3
           while not(eof(#3))
               input #3, b1$
               if a1$=b1$ then found = 1
           wend
        close #3
        if found then
           print #2, a1$;",";a2$;",";"Worked"
           'print  a1$;",";a2$;",";"Worked"
        else
           print #2, a1$;",";a2$;",";a3$
           'print a1$;",";a2$;",";a3$
        end if

   wend
close #1
close #2
t1=time$("ms")
print "Time taken ", t1-t0

[johnk1ese]

tsh73 -

Great! Many thanks. I will study the code to see if I can get it through my head, but it worked perfectly.

I need to get my head around the two output files at the top of the code. That's where the magic is and I need to step through it in debug to try and follow. Often I make one step too many to see what caused a change and don't think there is a way to back up and see it again.

With appreciation,
John K1ESE

[tsh73]

the two output files at the top of the code. T

That was making testing files for my testing
>>it takes 13 seconds on my old machine for 1000 master and 300 worked.

Now it is skipped over with
goto [skipDataForSpeedTest]
so it does not affect program at all.