Combining String data

[AndyGable]

Hi Everyone

Just wanted to run a quick problem / question past you all

I have a data file that holds data in the following format

barcode|qty (eg 50201600|1)

What I would like to do is is read the file in (as this can be anything from 1 line to 5,000 lines) and I would like to combine the qtys for items that have already been read in

What would be the best way to do that? Can I do it with a Array or does it have to be a database table?

For example

5018374320766|1
50201600|2
99998|1
99999|2
5018374320766|2
50201600|1

so when it is out putted I would get


5018374320766|3
50201600|3
99998|1
99999|2

[thatbruce]

I'd do it using a collection. Read each line and split it into a barcode and a quantity. Then using the barcode as the key and the quantity as the value, if the key is not in the collection already then add the key with the value 0. Then update the (now existing key) adding the quantity to the value. Done.

[cogier]

You can do it with arrays. This is my solution, maybe someone can come up with a simpler version but this does work.

' Gambas class file

'Contents of file BCode.txt
' 5018374320766|1
' 50201600|2
' 99998|1
' 99999|2
' 5018374320766|2
' 50201600|1
' 5018374320766|5
' 99999|2
' 547892|9
' 99999|2
' 547892|5

Public Sub Form_Open()
  
  Dim sList As String[] = Split(File.Load(Application.Path &/ "BCode.txt"), gb.NewLine, "", True).Sort() 'Load the file into the array sList
  Dim bBCode As New String[] 
  Dim iQty As New Integer[]
  Dim iLoop, iFind As Integer
  
  For iLoop = 0 To sList.Max
    iFind = bBCode.Find(Split(sList[iLoop], "|")[0])  
    If iFind > -1 Then                                      'Does the Barcode exists in the new array
      iQty[iFind] += Val(Split(sList[iLoop], "|")[1])       'It does so just alter the quantity
    Else 
      bBCode.Add(Split(sList[iLoop], "|")[0])               'It doesn't so add to BBcode array and..
      iQty.Add(Split(sList[iLoop], "|")[1])                 'Add the quantity to iQty array   
    Endif
  Next
  
  sList.Clear                                               'Clear sList
  
  For iLoop = 0 To bBCode.Max                               'Rebuild sList with the new data
    sList.Add(bBCode[iLoop] & "|" & Str(iQty[iLoop]))
  Next
  
  Print sList.Join(gb.Newline)                              'Print out the array
  
End

' Output
' 5018374320766|8
' 50201600|3
' 547892|14
' 99998|1
' 99999|6

[vuott]

Maybe even with this code:

'Contents of file BCode.txt
' 5018374320766|1
' 50201600|2
' 99998|1
' 99999|2
' 5018374320766|2
' 50201600|1
' 5018374320766|5
' 99999|2
' 547892|9
' 99999|2
' 547892|5

Public Sub Main()

  Dim s As String = File.Load(Application.Path &/ "BCode.txt")
  Dim ss, sc As String[]
  Dim c, n As Short

  ss = Split(s, gb.NewLine)
  ss.Remove(ss.Max, 1)
  While ss.Count > 0
    c = 0
    n = 0
    sc = Scan(ss[0], "*|*")
    Repeat
      If Scan(ss[c], "*|*")[0] = sc[0] Then 
        n += Val(Scan(ss[c], "*|*")[1])
        ss.Remove(c, 1)
        Dec c
      Endif
      Inc c
    Until c > ss.Max
    Print sc[0]; "|"; n
  Wend

End

[thatbruce]

For the sake of it

' Gambas module file

Private $totals As New Collection

Public Sub Main()

  Dim sLines As String[] = Split(File.Load("./data.txt"), "\n")
  
  SumOverBCode(sLines)

  For Each $totals
    Print $totals.Key; "|"; $totals[$totals.Key]
  Next
  
End

Private Sub SumOverBCode(data As String[]) 

  Dim aWorkline As String[]

  For idx As Integer = 0 To data.Max
    aWorkline = Split(data[idx], "|")
    If Not $totals.Exist(aWorkline[0]) Then
      $totals.Add(0, aWorkline[0])
    Endif
    $totals[aWorkline[0]] += aWorkline[1]
  Next
  
End

Output:
5018374320766|8
50201600|3
99998|1
99999|6
547892|14

Total duration = 179ms

[cogier]

Wow! You now have 3 options to pick from.

A couple of points.

vuott: - Your code works perfectly if line 22 ss.Remove(ss.Max, 1) is removed!

thatBruce: - Your code will look better if you use the gb rather than the </> button. (I have edited your post above).

[vuott]

vuott: - Your code works perfectly if line 22 ss.Remove(ss.Max, 1) is removed!

The text file, which I load, is structured exactly like this:
5018374320766|1
50201600|2
99998|1
99999|2
5018374320766|2
50201600|1
5018374320766|5
99999|2
547892|9
99999|2
547892|5

If I do not enter the command line "ss.Remove(ss.Max, 1) ", I get the error "Out of Bound ".

However, I can remove it, but I have to add the RTrim() function to the string assignment in the first declaration of the local variable 's'.
If not, I still get the error "Out of Bound ".

Public Sub Main()
 
  Dim s As String = RTrim(File.Load(Application.Path &/ "BCode.txt"))
  Dim ss, sc As String[]
  Dim c, n As Short
 
  ss = Split(s, gb.NewLine)
 
  While ss.Count > 0
    c = 0
    n = 0
    sc = Scan(ss[0], "*|*")
    Repeat
      If Scan(ss[c], "*|*")[0] = sc[0] Then 
        n += Val(Scan(ss[c], "*|*")[1])
        ss.Remove(c, 1)
        Dec c
      Endif
      Inc c
    Until c > ss.Max
    Print sc[0]; "|"; n
  Wend
 
End

[thatbruce]

At a guess, I'd say that one of you has a blank line at the end of the file and one doesn't. In other words a Cr or whatever on the last data line.
If that is the case then I find the best way to to handle that is in the initial file load when the file string is split by line endings, just by ignoring nulls in the split.

[cogier]

At a guess, I'd say that one of you has a blank line at the end of the file and one doesn't. In other words a Cr or whatever on the last data line.
If that is the case then I find the best way to to handle that is in the initial file load when the file string is split by line endings, just by ignoring nulls in the split.

I agree and that is what is in my code. Below is one change I have made to vuott's code that deals with the 'extra' lines.

'Contents of file BCode.txt
' 5018374320766|1
' 
' 50201600|2
' 99998|1
' 99999|2
' 5018374320766|2
' 50201600|1
' 
' 5018374320766|5
' 99999|2
' 547892|9
' 
' 99999|2
' 
' 547892|5
' 
 
Public Sub Main()
  
  Dim s As String = RTrim(File.Load(Application.Path &/ "BCode.txt")) 
  Dim ss, sc As String[]
  Dim c, n As Short
  
  'ss = Split(s, gb.NewLine)
  ss = Split(s, gb.NewLine, "", True) ''It's the TRUE that does the work.
  
  While ss.Count > 0
    c = 0
    n = 0
    sc = Scan(ss[0], "*|*")
    Repeat
      If Scan(ss[c], "*|*")[0] = sc[0] Then 
        n += Val(Scan(ss[c], "*|*")[1])
        ss.Remove(c, 1)
        Dec c
      Endif
      Inc c
    Until c > ss.Max
    Print sc[0]; "|"; n
  Wend  
  
End