Forum Discussion

muttu's avatar
muttu
Occasional Contributor
14 years ago

Requirement to read data from the excel tables with multiple rows.

We are using below functions to read data from excel & store it in dictionary objects as key-value pair.

It works for the data in Table1(attached file Table) , we get the resulst as "David"

But it does not work for Table2(attached file Table2 ).For Table2 ,it displays error "Key is already associated with an element in this collection" Can someone help? 






Dim sAutomationPath


 sAutomationPath

sAutomationPath = "d:\TC Sample Projects\Excel_MultipleRows\"



'Fetching values from excel tables



Function ConnectDB


 ConnectDB

Set AConnection = ADO.CreateADOConnection


Dim sConnectionString


 


sConnectionString = _


"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _


"DriverId=790;" & _


"Dbq=" + sAutomationPath + "myFile.xlsx;" & _


"DefaultDir=" + sAutomationPath


 


AConnection.ConnectionString = sConnectionString


AConnection.LoginPrompt = False


AConnection.Open


Set ConnectDB = AConnection



End Function

 




'********************************************************************



Function FetchDB (sTableName, sSheetName)


 FetchDB (sTableName, sSheetName)

Dim iIndex


'Get ADO Connection Object


Set AConnection = ConnectDB ()


Set Driver = DDT.ExcelDriver(sAutomationPath + "myFile.xlsx", sSheetName,true)


Set RecSet = AConnection.Execute_("SELECT * FROM "+ sTableName)


 


Set objDict = CreateObject("Scripting.Dictionary")


RecSet.MoveFirst


While Not RecSet.EOF


for iIndex = 0 to Driver.ColumnCount-1


objDict.Add (Driver.ColumnName(iIndex)), RecSet.Fields(Driver.ColumnName(iIndex)).Value


Next


RecSet.MoveNext


WEnd


Call DDT.CloseDriver(Driver.Name)


Set FetchDB = objDict



End Function

'********************************************************************



Sub Test1


 Test1

 Set dictObj = FetchDB ("Table1","Sheet1")



Log.message (dictObj.item("EmpName"))


.message (dictObj.item("EmpName"))

End Sub



Result1:

David



 

Sub Test2


 Set dictObj = FetchDB ("Table2","Sheet1")



Log.message (dictObj.item("EmpName"))


.message (dictObj.item("EmpName"))

End Sub



Result2:

Error_Test2 file attached







Excel table: screenshot of the excek table data is attached.







2 Replies

Sort By
  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    14 years ago
    Hi Mruttunjay,



    The problem is in the

      While Not RecSet.EOF

        for iIndex = 0 to Driver.ColumnCount-1

          objDict.Add (Driver.ColumnName(iIndex)), RecSet.Fields(Driver.ColumnName(iIndex)).Value

        Next

        RecSet.MoveNext

      WEnd

    code snippet.



    What it does is creation of the 'column name - column value' pairs.

    I.e. for the Table1 the dictionary structure will be like this:

    Key          Value

    ----           -----

    TCID        TC1

    EmpName David



    which is fine.



    Hovewer, for the Table2 the dictionary structure will be like this:

    Key          Value

    ----           -----

    TCID        TC1

    EmpName David

    TCID        TC2

    EmpName Adam



    which is incorrect because duplication of the key values is not allowed for the dictionary.



    Maybe, instead of the mentioned code snippet you need just this one:

      While Not RecSet.EOF

        objDict.Add RecSet.Fields(Driver.ColumnName(0)).Value, RecSet.Fields(Driver.ColumnName(1)).Value

        RecSet.MoveNext

      WEnd



    with the

      Log.message (dictObj.item("EmpName"))

    line in Test2 replaced with

      Log.message (dictObj.item("TC2"))

    ?
  • AlexanderM's avatar
    AlexanderM
    Staff
    14 years ago

    Hi Mruttunjay,





    You get this error due to an attempt to add a duplicate key.





    To prevent it, you can change the line with "Set RecSet..." so that it looks like this:





    Set RecSet = AConnection.Execute("SELECT DISTINCT FROM "+ sTableName)