Create Dictionary from Excel Data

  1. 4 months ago

    Alex B

    Aug 13 St. Louis, MO

    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.

    Activity ID  |  % Complete
    -------------|--------------
    A1010        |   50
    A1020        |    0

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

    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

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

  2. Eugene D

    Aug 14 Pre-Release Testers, Xojo Pro Canada scispec.ca

    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.

    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

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

  3. Alex B

    Aug 14 St. Louis, MO
    Edited 4 months ago

    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.

    -image-

    Screen Shot

  4. Eugene D

    Aug 14 Pre-Release Testers, Xojo Pro Canada scispec.ca

    @Alex B if I have questions or suggestions for your book, what's the best way to contact you?

    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.

    @Alex B 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.

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

  5. Alex B

    Aug 15 St. Louis, MO

    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.

    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

or Sign Up to reply!