Solved

Parse CSV string and perform select & listbox display

Posted on 2011-09-05
11
499 Views
Last Modified: 2012-05-12
Experts,

I have strCardIDs = "1,1,2,2,2,2,3,3,4,1,1,8,8,3,3,3,4,4,4,4,5,6,7,7,7,7,7,7,7,8" (this is retrieved from field in db)
These are the IDs of each of the Cards.

I have table Cards that look like this

ID        Title
1          CardOne
2          CardTwo
3          CardThree
4          CardFour
5          CardFive
6          CardSix
7          CardSeven
8          CardEight
etc        etc

I have a listbox, lstCards

I want to display the following (the card title, and the quantity of each card as a sum of the number of times it's ID appears in the list. Please note, ID of card could appear anywhere in the string. that is, ID 1 could also appear at the end of the string as well as at the beginning, or in the middle):

Title               Quantity
CardOne        4
CardTwo        4
CardThree      5
CardFour        5
CardFive         1
CardSix           1
CardSeven      7
CardEight       3

TIA,

crafuse
0
Comment
Question by:crafuse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 15

Expert Comment

by:x77
ID: 36483208
You can use a Dictionary(of string,Integer) where Id is strin(key), Count is integer

  dim  sep As Char() = New Char() {","c}
  dim cnt as integer
  for each v as string in strCardIDs.split(sep,StringSplitOptions.RemoveEmptyEntries)
      if myDict.TryGetValue(v,cnt) then
         cnt += 1
         myDict(v) = cnt
      else
         MyDict.Add(v,1)
      end if
  next

Next enumerate the dictionary (Values, Count) to list values.
0
 
LVL 15

Expert Comment

by:x77
ID: 36483254
On my code I use specific Hash class for many situations as I use it to much (htCnt, HtSuma, HtAvg).
It is very easy built a class that support Hash if it do´nt need remove method.

on htCnt class, I can use:

    MyHtCnt.Add(Value) - I do´nt need worry about value already exists ...

   At end, I can access the Data Array (note that it is a public) , Sort It ....
Public Class HtCnt(Of T As IComparable(Of T))
    Public Structure Node
        Implements IComparable(Of Node)

        Public Value As T, Cnt As Int32, Nxt As Integer
        Public Overrides Function ToString() As String
            Return Value.ToString & " : " & Cnt.ToString
        End Function

        Public Function CompareTo(ByVal other As Node) As Integer Implements System.IComparable(Of Node).CompareTo
           Return Value.CompareTo(other.value)
        End Function
    End Structure
    Private ReadOnly Indices() As Integer, IndCount As Integer
    Public Data() As Node, Count As Integer
    Public Sub New(ByVal size As Integer)
        If size < 7 Then size = 7
        IndCount = GetPrime(size)
        Indices = New Integer(IndCount - 1) {}
        Data = New Node(size) {}
    End Sub
    '0 - en Array Indices es valor inicial, Entrada vacía
    Public Sub add(ByVal Value As T)
      Dim Index As Int32 = (Value.GetHashCode And &H7FFFFFFF) Mod IndCount
      Dim na As Int32 = Indices(Index) - 1, n = na    'n : Bucket / Node.nxt 
      Do Until n < 0
         With Data(n)
           If Value.Equals(.Value) Then .Cnt += 1 : Return
           n = .Nxt
         End With
      Loop
      If Count >= Data.Length Then Array.Resize(Data, Count << 1)
      Data(Count) = New Node() With {.Value = Value, .Nxt = na, .Cnt = 1} : Count += 1
      Indices(Index) = Count 'Nueva Entrada - Se Encadena al índice
    End Sub

    Public Sub addRange(ByVal Values As IEnumerable(Of T))
      For Each V In Values : add(V) : Next
    End Sub

    Public Function Index(ByVal Value As T) As Int32
      If Value IsNot Nothing Then
          Dim n = Indices((Value.GetHashCode And &H7FFFFFFF) Mod IndCount) - 1
          Do Until n < 0
             With Data(n) : If Value.Equals(.Value) Then Return n Else n = .Nxt
             End With
          Loop
      End If
      Return -1
    End Function
    Public Sub Clear()
        If Count > 1 Then
           Array.Clear(Indices, 0, Indices.Length)
           Array.Clear(Data, 0, Data.Length) : Count = 0
        End If
    End Sub
'  Public Shared Sub TesthtCnt()
'  Dim t = Stopwatch.StartNew
'      Dim h As New HtCnt(Of Integer)(100)
'      For n = 0 To 1000
'          For i = 0 To n
'              h.add(i)
'          Next
'      Next
'Debug.Print(t.Elapsed.ToString)
'      For n = 0 To 1000
'          If h.Index(n) <> n Then Stop
'          If h.Data(n).Cnt <> (1001 - n) Then Stop
'      Next
'  End Sub
End Class


'This method is comon to all my Hash class.
  Private primes() As Integer = {53, 97, 193, 389, 769, 1543, 3079, 6151, 12289, 24593, 49157, 98317, 196613, 393241, 786433, 1572869, 3145739, 6291469, 12582917, 25165843, 50331653, 100663319, 201326611, 402653189, 805306457, 1610612741}

  <DebuggerStepThrough()> _
  Public Function GetPrime(ByVal size As Integer) As Integer
      Dim n As Integer = 0
      If size < 0 Then Throw (New Exception("GetPrime - Size < 0"))
      size >>= 5 : While (size > 0) : size >>= 1 : n += 1 : End While
      Return primes(Math.Min(n, primes.Length - 1))
  End Function

Open in new window

0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 36483621
From your string, it contains only the value. The text is missing. However you can keep them in a dictionary/List as well.

List<int,string> objList = new List<int,string>();

objList.Add(1,"CardOne");
objList.Add(2,"CardTwo");
and so on...

string[]  keys = strCardIDs.split(",".ToCharArray());
List<int,string> objListRest = new List<int,string>();
int val;
for each (string str in  keys )
{
   int key = Convert.Toint32(str);
   
   if(objListRest.ContainsKey(key))
{
    val = objListRest[key];
    val ++;
objListRest[key] = val;
}
else
  objListRest[key] = 1;

}




0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Accepted Solution

by:
crafuse earned 0 total points
ID: 36483715
Experts,

I got it working with the code below. I still have a question, though: I'm sure that opening & closing the db connection through every pass is NOT good. Any suggestions to make this elegant?

Thanks,

crafuse
Dim MyArray As New ArrayList()
            Dim values() As String = strLibraryList.ToString.Split(",")
            Dim int As Integer = 1
            Dim total As Integer

            MyArray.AddRange(values)
            MyArray.Sort()

            Dim b As Integer

            For b = 0 To MyArray.Count - 1
                If MyArray(b) <> int AndAlso total > 0 Then
                    
                    Dim CardSQL As String
                    Dim strCardName As String

                    CardSQL = "select name from merchant where cardno = " & int & ""
                    
                    Dim con1 As String = strMySQLConnectString
                    Dim strConnect1 As String = con1

                    Dim conReader1 As New MySqlConnection(strConnect1)
                    Dim conString1 As New MySqlCommand(CardSQL, conReader1)
                    conReader1.Open()

                    Dim drReader1 As MySqlDataReader
                    drReader1 = conString1.ExecuteReader(CommandBehavior.CloseConnection)

                    drReader1.Read()
                    strCardName = drReader1.Item("name").ToString

                    lstLibraryCards.Items.Add(strCardName.PadRight(32, " ").Substring(0, 16) & Chr(9) & total)

                    conReader1.Close()
                    drReader1.Close()

                    total = 1
                    int = MyArray(b)
                Else
                    total += 1
                End If
            Next b

Open in new window

0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 36483803
Yes. You can form seperate more select queries with ; seperated. This will execute all the statements. NextResultSet() will give successive result set.

Refer http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm
0
 

Author Comment

by:crafuse
ID: 36483846
EaswaranP:

thanks for that. could you demonstrate using my code? otherwise, i will look into this tomorrow.

Thanks,
crafuse
0
 
LVL 19

Expert Comment

by:Shahan Ayyub
ID: 36484315
Hi!

Please try like this: (see comments also)

Dim MyArray As New ArrayList()
        Dim values() As String = strLibraryList.ToString.Split(",")
        Dim int As Integer = 1
        Dim total As Integer

        MyArray.AddRange(values)
        MyArray.Sort()

        Dim b As Integer
        Dim CardSQL As String
        Dim strCardName As String = String.Empty

        Dim temp As String = String.Empty        'Declaration ends

        For b = 0 To MyArray.Count - 1
            If MyArray(b) <> int AndAlso total > 0 Then
                temp &= MyArray(b) & ","                     ' Concatenate IDs in a variable with comma to use in 'IN clause'
                total = 1
                int = MyArray(b)
            Else
                total += 1
            End If
        Next b

        temp = temp.TrimEnd(",")                       ' Remove ',' at the end of the variable.

        CardSQL = "select name from merchant where cardno IN (" & temp & ")"    ' pass IDs in query ONCE.

        Dim conReader1 As New MySqlConnection(strMySQLConnectString)
        Dim conString1 As New MySqlCommand(CardSQL, conReader1)

        conReader1.Open()   ' Open connection once

        Dim drReader1 As MySqlDataReader
        drReader1 = conString1.ExecuteReader(CommandBehavior.CloseConnection)   ' execute command Once

         'here datareader has all the name you are willing to popup.
         'insert a loop here and read 'drReader1' and display results as you want.


        conReader1.Close()   ' Close connection once.

Open in new window

0
 

Author Comment

by:crafuse
ID: 36486396
Shahan_Developer -

I guess this question was not very clear. Your answer neglects a fundamental aspect of the functionality I'm looking for: that each CardName is listed along with that card's quantity, so that the listbox items look like this:

cardone                   10
cardforty                    3
cardtwentynine         6
cardsix                       1
cardonehundred        9
etcetera                     x
0
 

Author Closing Comment

by:crafuse
ID: 36515718
will open a new question to help with passing through my db.
0
 
LVL 19

Expert Comment

by:Shahan Ayyub
ID: 36487649
Did you get the logic what I tried to express ?

I don't have further information so i can't test. That was the only reason to add "comments" in my code.
0
 

Author Comment

by:crafuse
ID: 36487694
i suppose i got your logic, yes. what is important, however, is displaying also a count of each cards quantity. populating the listbox with card names derived from a dynamically concatenated string of values is no problem for me, but it's not what i need in this case.

but thank you for your help.

;-)

crafuse
0

Featured Post

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question