adding up same numbers

[Rod]

This is what a csv file should look like and how to get it into an array to work on. I keep it in one array not six because we need to sort the records to find duplicates. I sum the duplicate records and zero out the previous record.


open "server.txt" for output as #1
#1 "104,29-06-2020,07:35:43,23.9,461,1"
#1 "34,29-06-2020,07:53:21,15.6,395,1"
#1 "62,29-06-2020,08:08:13,21.7,455,1"
#1 "141,29-06-2020,08:21:20,19.6,541,1"
#1 "141,29-06-2020,08:21:20,19.6,541,1"
#1 "95,29-06-2020,08:37:04,10.4,290,1"
close #1



'to find duplicates you need to put the file in cow number order
'so you need one array not six
dim cow$(100,6)
cownum=1
date=2
time=3
kg=4
sec=5
place=6

'now open the server file and read all records till we hit eof()
open "server.txt" for input as #server
i=1
while eof(#server)=0
   inputcsv #server, cow$(i,cownum),cow$(i,date),cow$(i,time),cow$(i,kg),cow$(i,sec),cow$(i,place)
   i=i+1
wend

'reduce i because it is one too many
i=i-1

'now sort the file into cow number order
sort cow$(,i,1,cownum

'print it to show the change
'notice the order is different from the input file
for n= 1 to i
   print cow$(n,cownum)
next



'search for duplicates using for next loop
lastcow=0
for n= 1 to i

   'checking for duplicate
   if val(cow$(n,cownum))=lastcow then
       print "Duplicate found ";cow$(n,cownum)
       cow$(n,kg)=str$(val(cow$(n,kg))+val(cow$(n-1,kg)))
       cow$(n,sec)=str$(val(cow$(n,sec))+val(cow$(n-1,sec)))
       cow$(n-1,cownum)=""
       cow$(n-1,date)=""
       cow$(n-1,time)=""
       cow$(n-1,kg)=""
       cow$(n-1,sec)=""
       cow$(n-1,place)=""
   end if

   'remember the last cow for the next check
   lastcow=val(cow$(n,cownum))
next

'now print it all out to see if we got it right
'ignore array elements with ""
for n=1 to i
if cow$(n,cownum)<>"" then print cow$(n,cownum);" ";cow$(n,kg);" ";cow$(n,sec)
next

wait




[konijn]

Thanks Rod,

This is how I think it will work. The out.dat file is the format how it has to be.

The date is not nessecary.

Maybe things can eassier?

I test on!!

'open "server.txt" for output as #1
'#1 "104,29-06-2020,07:35:43,23.9,461,1"
'#1 "34,29-06-2020,07:53:21,15.6,395,1"
'#1 "62,29-06-2020,08:08:13,21.7,455,1"
'#1 "141,29-06-2020,08:21:20,19.6,541,1"
'#1 "141,29-06-2020,08:21:20,19.6,541,1"
'#1 "95,29-06-2020,08:37:04,10.4,290,1"
'close #1



'to find duplicates you need to put the file in cow number order
'so you need one array not six
dim cow$(100,6)
cownum=1
date=2
time=3
kg=4
sec=5
place=6

'now open the server file and read all records till we hit eof()
open "c:\melkteller\server.txt" for input as #server
i=1
while eof(#server)=0
   inputcsv #server, cow$(i,cownum),cow$(i,date),cow$(i,time),cow$(i,kg),cow$(i,sec),cow$(i,place)
   i=i+1
wend
close #server
'reduce i because it is one too many
i=i-1

'now sort the file into cow number order
sort cow$(,i,1,cownum

'print it to show the change
'notice the order is different from the input file
'for n= 1 to i
'   print  cow$(n,cownum)
'next



'search for duplicates using for next loop
open "out.at" for output as #1
lastcow=0
for n= 1 to i

   'checking for duplicate
   if val(cow$(n,cownum))=lastcow then
    '   print "Duplicate found ";cow$(n,cownum)
       cow$(n,kg)=str$(val(cow$(n,kg))+val(cow$(n-1,kg)))
       cow$(n,sec)=str$(val(cow$(n,sec))+val(cow$(n-1,sec)))
       cow$(n-1,cownum)=""
       cow$(n-1,date)=""
       cow$(n-1,time)=""
       cow$(n-1,kg)=""
       cow$(n-1,sec)=""
       cow$(n-1,place)=""
   end if

   'remember the last cow for the next check
   lastcow=val(cow$(n,cownum))
next

'now print it all out to see if we got it right
'ignore array elements with ""
print #1,"DN9900080030000106000300130031003003660600030011906000300358030"
for n=1 to i
kgs= val(cow$(n,kg))*10
kg$ = using(" ###", kgs)
dt$=word$(cow$(n, time),1,":")+word$(cow$(n, time),2,":")+word$(cow$(n, time),3,":")
if cow$(n,cownum)<>"" then print #1,"VN990008"+leadingZerosecond$(val(cow$(n,cownum)), 6);leadingZerosecond$(val(kg$), 3);dt$;hhmmss$(val(cow$(n,sec)));leadingZerosecond$(val(cow$(n,place)), 3)
next
print #1,"ZN"
close #1
wait



function hhmmss$( i)
   '   NB if i is >=86400 ( 60 *60 *24) this needs modifying!
   subject =i
   sec     =i mod 60
   i       =( i -sec) /60
   min     =i mod 60
   i       =( i -min) /60
   hrs     =i
   hhmmss$ =right$( "00" +str$( hrs), 2) +"" +right$( "00" +str$( min), 2) +"" +right$( "00" +str$( sec), 2)
   if ( sec +60 *min +3600 *hrs) <>subject then notice "I failed!"
end function


function leadingZerosecond$( i, L)  '   receives number i and padded length L
   for n =1 to L
       pad$ =pad$ +"0"
   next n
   leadingZerosecond$ =right$( pad$ +str$( i), L)
end function



[tsh73]

I am doing it wrong

LOL
1) it is called "comma separated". How'd you think - why?
so
(global search/replace will do)
104,29-06-2020,07:35:43,23.9,461,1,
34,29-06-2020,07:53:21,15.6,395,1,
62,29-06-2020,08:08:13,21.7,455,1,
141,29-06-2020,08:21:20,19.6,541,1,
95,29-06-2020,08:37:04,10.4,290,1,
I know it's locale-dependent (yes in Russia we have semicolon as a delimiter from Excel too), but LB is not aware of it, I'm afraid.

2) then I add print just after inputcsv
   print i, hCow$(i), hDate$(i), hTime$(i), hKg(i), hSec(i), hPlace(i)
I see that in goes out of sync
My guess it is last comma throws it off
so I added dummy variable
   inputcsv #server, hCow$(i), hDate$(i), hTime$(i), hKg(i), hSec(i), hPlace(i), dummy$
Viola!
n=5
1             104           29-06-2020    07:35:43      23.9          461           1
2             34            29-06-2020    07:53:21      15.6          395           1
3             62            29-06-2020    08:08:13      21.7          455           1
4             141           29-06-2020    08:21:20      19.6          541           1
5             95            29-06-2020    08:37:04      10.4          290           1


---

EDIT sorry answered last message from page 1, not aware it's already taken care of