Create Dictionary from Excel Data

I’m working on my first real Xojo project and am struggling some. My current problem is getting Xojo to populate a dictionary correctly from data in Excel.

I’m using the concepts from this previous post: https://forum.xojo.com/25535-speed-up-read-from-excel

One of the sheets in the Excel workbook (named “TASK”) lists schedule activities with their percent complete information. Those are the two columns I want, and are automatically named “Activity ID” and “Activity % Complete(%)” by the project scheduling program I use. There are other columns of information on that sheet, but I’m ignoring all that other stuff for now as I try to build my dictionary.

I’m using some sample data that has only 2 rows of data… I’ve not tried with a production size sheet yet which would have 100 to 10,000 rows.

[code]Activity ID % Complete
A1010 50
A1020 0[/code]

The problem I’m having is that the Key doesn’t get recorded by Xojo for the second row of data… it’s just null (blank) field, and I cannot figure out why.

(By the way, I have other methods that open and do some error checking before this method is called.)

[code]Dim excel As New ExcelApplication

Dim TempDictionary As New Dictionary
Dim c As New Clipboard
Dim Lines() As String
Dim CellValues() As String

Dim ActIDCol As Integer
Dim PcntCompletCol As Integer
Dim LastRow As Integer

excel.ActiveWorkbook.worksheets(“TASK”).activate
ActIDCol = excel.cells.find(“Activity ID”).Column
PcntCompletCol = excel.cells.find(“Activity % Complete(%)”).column
LastRow = excel.activesheet.usedrange.rows.count +1

//copy the whole sheet into the clipboard
excel.range(excel.cells(3,ActIDCol),excel.cells(lastrow,PcntCompletCol)).copy

lines = Split(c.Text,Chr(13)) //splitting into rows of data

Dim i As Integer

For i = 0 To lines.ubound-1
CellValues = Split(lines(i),Chr(9)) //splitting the row of data into columns

'MsgBox(CellValues(actidcol-1)) + Chr(9) + CellValues(PcntCompletCol-1)
TempDictionary.Value(CellValues(actidcol-1)) = CellValues(PcntCompletCol-1)
Next i

Return TempDictionary[/code]

A second problem is how to deal with numbers. Data in the spreadsheet is always going to be viewed by excel as text based on how it’s exported. I have no control over that. But, ultimately, I want the % Complete to be a double. When I try to convert it before populating the dictionary using CDbl(xxxx) , I get a different error saying that Xojo was expecting a string but got an integer.

Thank you for your help!!

Hello Alex,

I made an example Excel spreadsheet with two columns and used most of your code to convert the excel data into a dictionary. The dictionary is the read and data is shown in the TextArea control in Xojo. A link to download both the example excel data and the working Xojo code is below. The main code in the action event of the pushbutton is shown.

[code]Sub Action() Handles Action
Dim excel as New ExcelApplication
Dim TempDictionary as New Dictionary
Dim c as New Clipboard
Dim Lines() as String
Dim CellValues() As String

Dim ActIDCol as Integer
Dim PcntCompletCol as Integer
Dim LastRow as Integer

excel.ActiveWorkbook.Worksheets(“TASK”).Activate
ActIDCol = excel.Cells.Find(“Activity ID”).Column
PcntCompletCol = excel.Cells.Find(“Activity % Complete(%)”).Column
LastRow = excel.ActiveSheet.usedrange.rows.count//+1 is removed
TextArea1.Text = “”
TextArea1.Text = TextArea1.Text + "LastRow: " + LastRow.ToText + EndOfLine

//Copy the whole sheet into the Clipboard
excel.Range(excel.Cells(1, ActIDCol), excel.Cells(LastRow, PcntCompletCol)).Copy //Column #1

Lines = Split(c.Text, Chr(13)) //Splitting into rows of data

Dim i as Integer
For i = 0 to Lines.Ubound -1//removed -1
CellValues = Split(Lines(i), Chr(9)) //splitting the row of data into columns
TempDictionary.Value(CellValues(ActIDCol-1)) = CellValues(PcntCompletCol-1)
Next i

//Show the data
For i = 0 to TempDictionary.Count-1
TextArea1.Text = TextArea1.Text + TempDictionary.Key(i) + " " + TempDictionary.Value(TempDictionary.Key(i))
Next i

//Return TempDictionary
End Sub
[/code]

This seems to work on my computer. Does this work on yours?
ExcelData.zip

Eugene, thanks for helping me with this! One of the ways I’m learning Xojo is through your excellent Excel book… thank you very much for sharing your knowledge!! (BTW, if I have questions or suggestions for your book, what’s the best way to contact you?)

I used your code and the TextArea gets the key & value correctly. But, when test it out to see if the dictionary is working via a MsgBox, the first one works OK, but the second one fails, giving a “KeyNotFoundException”. Do I have an error in how I’m asking for it to repeat back to me the information? I get the same error in both the function where I’m actually loading the dictionary, or after I pass the dictionary back to my “ProcessTheFile” routine.

Screen Shot

You can either contact me through my email address, which is on page 2 of the Excel book, or you can private message me in the Xojo forum.

Hmm, I am not sure if this is a bug, and it might be. I replaced the following code and it works… Maybe someone from Xojo may know the answer:

Removed this code in the example:

//Show the data For i = 0 to TempDictionary.Count-1 TextArea1.Text = TextArea1.Text + TempDictionary.Key(i) + " " + TempDictionary.Value(TempDictionary.Key(i)) Next i

Added this code in the example:

//Show the data TextArea1.Text = TextArea1.Text + TempDictionary.Value(Chr(&H0A)+"A1010") TextArea1.Text = TextArea1.Text + TempDictionary.Value(Chr(&H0A)+"A1020")

For some reason, the character &H0A needs to be added when searching in the dictionary. I am not sure if this is because the information was read from an Excel file, or if it is from Xojo. @Greg O’Lone or someone from the team, do you have any thoughts on this?

Alex, the workaround would be to add Chr(&H0A) for the time being.

I couldn’t get the Chr(&H0A) workaround to work for me. I ended up slowing things down and temporarily saving the key and the value into separate variables first, then referring to those when populating the dictionary. Also, I’m forcing the values to be strings. In prelim testing, this seems to work. But, I’m sure when I deal with production-level volumes of data, this is going to be slow since I also got away from the clipboard object concept.

[code]
BuildDictionaryFromRange(f as FolderItem, sheetName as string, keyColumnName as string, valueColumnName as string) As Dictionary

//This function creates a dictionary from an excel range.
//Process: open the excel file, select the sheet, walk down the rows of data, grabbing what you want.

Dim excel As New ExcelApplication
Dim book As ExcelWorkbook
Dim TempDict As New Dictionary
Dim keyColumn As Integer
Dim valueColumn As Integer
Dim firstRow As Integer

Dim i As Integer
Dim curKey As String
Dim curValue As String

//Open the excel workbook and select the correct sheet
book = Excel.workbooks.open(f.NativePath)
excel.visible = True
excel.ActiveWorkbook.worksheets(sheetName).activate

//Set up the boundaries of the data
keycolumn = excel.cells.find(keyColumnName ).Column
firstRow = excel.cells.find(keyColumnName ).row+1
valueColumn = excel.cells.find(valueColumnName ).Column

//Load the dictionary
For i = firstRow To excel.activesheet.usedrange.rows.count
curkey= excel.cells(i,keycolumn).value '<-- temp saving the data I want in the dictionary
curvalue= excel.cells(i,valuecolumn).value '<-- temp saving the data I want in the dictionary

TempDict.value(curkey) = curvalue

Next i

Return TempDict

//Cleanup
f=Nil
excel = Nil
book = Nil
TempDict = Nil[/code]