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
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.