I recently found a new section (new to me at least) of the .Net library very useful–System.Collections.

This is most likely nothing new to veteran programmers, but it was a perfect match, and a simple, quick solution to my problem. My output was  a .csv file with a list of employee names (sorted by last name), id, and work hours separated by payroll weeks.  Because of the structure of the data and tables in the database, I could not pull just the last name for sorting, or directly pull the data I needed in the format I wanted with plain SQL. This left me with parsing database results into something usable via row manipulation. Everything was fine until I got to sorting. This is where the System.Collections library came in handy. Using a Split(string) function and a SortedDictionary from System.Collections, I came up with the following solution:

'Use Hashtables to store unsorted data as it comes in.'
Dim week1 as New Hashtable()
Dim week2 as New Hashtable()

'Use a SortedDictionary to store data I wish to keep sorted.'
Dim employeeNames as New SortedDictionary(Of String, Integer)

'I removed the sql here to protect company info. '
'However, the database returned the following: '
'- Employee_firstlast'
'- Employee_ID'
'- Week_of_hours_worked'
'- Number_of_hours_worked'

'Now, I push database values into '
'the Hashtables and SortedDictionary created above. '
'Not all employees will have hours in both weeks.'
For Each row in dbResultTable
    'Split the name into distinct parts. '
    'They are stored in the database as "First Last" with no exceptions. '
    Dim nameArray as String() = Split(row.Cells("Employee_firstlast")," ")

    'This uses the value of "last first" as the Key instead of employee_id, '
    ' because SortedDictionary on Key by default. This, of course requires '
    '"last first" to be unique, and it was in this case. '
    If Not employeeNames.ContainsKey(nameArray(1) & " " & nameArray(0)) Then
        employeeNames.Add(nameArray(1) & " " & nameArray(0), & _
                          row.Cells("Employee_ID")
    End If

    'Now use ID as Key in Hashtables.'
    Select Case row.Cells("Week_of_hours_worked")
        Case 1
            If week1.ContainsKey(row.Cells("Employee_ID")) Then
                'If Key exists, add hours together...'
                week1.Item(row.Cells("Employee_ID")) +=
                           row.Cells("Number_of_hours_worked")
            Else
                'Otherwise, insert new Key into Hashtable....'
                week1.Add(row.Cells("Employee_ID"), 
                          row.Cells("Number_of_hours_worked"))
            End If
        Case 2
            'This "If" block uses same logic as above.'
            If week2.ContainsKey(row.Cells("Employee_ID")) Then
                week2.Item(row.Cells("Employee_ID")) += 
                           row.Cells("Number_of_hours_worked")
            Else
                week2.Add(row.Cells("Employee_ID"), 
                          row.Cells("Number_of_hours_worked"))
            End If
    End Select
Next

'Now, lets write the .csv file'
Dim myFile as New StreamWriter("filename.csv")

'Write the header row for easier human consumption. (Mmmm, tasty data!)'
myFile.WriteLine("Employee_ID, Employee_Name, Week 1 Hours, Week 2 Hours")

'Now, write each line with appropriate information, with a little help from '
'a small helper function below, "hasElements" which returns a string '
For Each employee in employeeNames

    'Now, reverse the name in the key for format of person '
    'who requested the file. Goofy, I know... '
    Dim name as String = Split(employee.Key, " ")(1) & " " & _ 
                         Split(employee.Key, " ")(0)

    'Add appropriate information for this row to the .csv file. '
    myFile.WriteLine(employee.Value & "," & name & _
                     hasElement(week1, driver.Value) & _
                     hasElement(week2, driver.Value))
Next

myFile.Close()

And the helper function ‘hasElement’:

Function hasElement(ByVal h as HashTable, ByVal s as String)

    'If this HashTable has this Key,... ' 
    If h.ContainsKey(s) Then
        '...return the Value associated with this Key. '
        Return h.Items(s)
    Else
        '...otherwise, return a 0. '
        Return "0"
    End If
End Function

The beauty of using a SortedDictionary is that I never had to issue any ‘sort’ function, and my data was still sorted! I didn’t even need to tell it HOW to sort (i.e. ascending, numerically, etc). Again, this is probably no news to a .Net veteran, and certainly is nothing new in the world of programming at large. However, it saved me some time, and I though that it might save others time, too.

Also, I know it is in Visual Basic instead of C# or Ruby or . I don’t care. This is the language my workplace has designated for most uses, and that is what I work quickly in, so for small things like this, I don’t look to the newest, most groundbreaking way of doing things.

With the caveat above, I would like to add that I am definitely interested in learning new ways to do this sort of operation; just don’t be rude about my current choice. For instance, I would love to see the most awesome Ruby or Python way to do this. Also, I am pretty sure this could have been done more succinctly with something like GNU awk or sed. I am interested in those, too. Feel free to leave you way in the comments below.

---